MySQL Database setup for Stand Alone Dashboard

Post any 3rd party software here.
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Sat Aug 29, 2015 9:30 pm

We have imported the last 8 months of data from SQLITE with some conversion scripts.
Even with all the data in the database, the performance is still WONDERFUL!
CentOS 7 also works fine and probably better than the performance with CentOS 5.5
Thanks to all who helped.
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Mon May 09, 2016 10:20 am

Updated with the fixes mentioned in this thread:
Standalone_Dashboard_MySQL.zip
(4.58 MiB) Downloaded 1855 times
dashboard-create-mysqldb.sql..zip
(1.33 KiB) Downloaded 753 times
Came up with some basic instructions to install this on a Windows PC:

1) Download the zip and sql file from viewtopic.php?p=9556#p9556
2) Install XAMPP with MySQL and phpMyAdmin enabled.
3) Once installed, open the control panel and start Apache and MySQL.
4) Go to http://localhost/phpmyadmin
5) Click the Import button in the top menu.
6) Select database-create-mysqldb.sql.zip.
7) Click Go.
8) Extract Standalone_Dashboard_MySQL.zip to c:\xampp\htdocs\, overwrite any files with the same name.
9) Go to c:\xampp\htdocs\btech\config\database.php and edit with any text editor.
10) Change the file contents to match the below configuration:

$db['default']['hostname'] = 'localhost';
$db['default']['port'] = '';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'dashboard';
$db['default']['dbdriver'] = 'mysqli';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

11) Should be good to go, go to http://localhost/ and configure the database
Ben
Brultech Research Inc.
E: ben(at)brultech.com
shortbg
Posts: 4
Joined: Mon Jan 26, 2015 9:51 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortbg » Sat Jul 16, 2016 12:30 pm

After 18 months of recording data, the database is almost 4 GB. Performance is still great, however I blew past the 1 GB limit that godaddy sets on mysql databases. I guess it's time to delete some of the old minute data. There are over 31 million records in the database.
shortwh
Posts: 25
Joined: Tue Jan 06, 2015 1:18 pm

Re: MySQL Database setup for Stand Alone Dashboard

Post by shortwh » Wed Jul 20, 2016 9:50 am

Instead of removing data which I like to keep for finding previous energy patterns. We chose to build up a mac mini 2.1 running CentOS7.2. Works fine and even better than it did with goDaddy hosting. Now to see how long we can go recording before the HDD dies or hits some MYSQL limit. BTW - the GEM is still the best monitor we have used to date.
Thanks, Ben for all the help and posting the modifications here on the forum.
JoeShmo
Posts: 1
Joined: Sun Mar 13, 2016 2:00 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by JoeShmo » Sat Dec 17, 2016 9:47 pm

note, with the above attached files, if using mysql, you still need to replace PAGE_CHECKSUM with CHECKSUM:
sed -i s/"PAGE_CHECKSUM"/"CHECKSUM"/ dashboard-create-mysqldb.sql
(note: MariaDB supports PAGE_CHECKSUM)

Also, if you are migrating from an existing sqlite dashboard, I did something like this to move the data over:
echo "SET @@session.unique_checks = 0;" > gem.sql
echo "SET @@session.foreign_key_checks = 0;" >> gem.sql
echo "START TRANSACTION;" >> gem.sql
sqlite3 brultech_dash.sqlite .dump | grep INSERT | sed s/\"// | sed s/\"// | sed '0~10000 s/$/\nCOMMIT;\nSTART TRANSACTION;/g' >> gem.sql
echo "COMMIT;" >> gem.sql

fire up mysql, use "source gem.sql". It'll still take quite a while, even though we're importing 10k transactions at a time.
also note, don't have the GEM import to the new database until you've imported everything from old database, as the id fields are auto increment, and those numbers are already populated in the export files. I guess we could have filtered those out.. (shrug) Actually, my IDs dont start at 1.. wonder if they got cleaned out at some point... (shrug)
Ignore the errors at the end of the import about the sqlite tables not existing.

then after all this, I'll take a look at this newfangled btmon and influxdb that seems to the all the rage these days...
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: Help

Post by ben » Wed Feb 20, 2019 3:08 pm

jerryt wrote:Trying to get Standalone v2 working. I was able to install Xampp 1.8.3, load the Sql file (Had to change the font type), and I am getting the Brultech dashboard at "http://localhost:82/index.php/pages/devices/". (Note port 80 was in use. Edited Apache for port 82)

I have entered the Wiznet adapter and set the;
"Data Post" to XXX.XXX.X.106 the computer running Xampp, (Also tried X.106:82)
"URL Extension" /index.php/ecmEngine/greeneye
"site type" Dashbox/Standalone Dash

Problem is that the Dashboard (Standalone) is not receiving any packets, no channel fill in.

Please help

Changed the Wiznet to mixed not server and now the channel are showing up, but still not data or live data ??
If the channels were created:
- Packets are reaching the server
- Database connection is working

Have you checked to see if there's any live data files in the web folder?
Ben
Brultech Research Inc.
E: ben(at)brultech.com
ben
Site Admin
Posts: 4254
Joined: Fri Jun 04, 2010 9:39 am

Re: MySQL Database setup for Stand Alone Dashboard

Post by ben » Thu Feb 21, 2019 11:02 am

jerryt wrote:I would like to get SAD v3.1 working as it is the latest. I was at least able to get the Brultech server pages on SAD v2.0

But on SAD v3.1 with Xampp 7.1.26, 7.3.1, and 7.3.2 importing the mysqldb creates many errors and I was not able to even get to the Brultech pages.

Could someone edit the "dashboard-create-mysqldb.sql" file to work properly with Xampp 7.1.26, 7.3.1, or 7.3.2 ??

Maybe state which version of Xampp a certain "dashboard-create-mysqldb.sql" file is know to import properly with ??

Thanks
Use XAMPP 5.6.40 instead of the 7.X versions, I'm unsure how well the dashboard is going to work with PHP7.
Ben
Brultech Research Inc.
E: ben(at)brultech.com
Post Reply