5,838
edits
MX on Linux: Difference between revisions
m
→Transferring database tables to your Pi
| Line 620: | Line 620: | ||
You can view any index.php or PHPMyAdmin web page in your browser by prefixing the address with your Pi IPv4 address e.g. a URL like '''http://192.168.1.xyz/phpmyadmin''' where ''xyz'' is 2 or 3 digits you look up as mentioned before where FTP was described. If your Pi has both Ethernet and wireless connection, there will be two different values of ''xyz'' for you to choose one from. On first run of PhpMyAdmin, as already mentioned, you will see a '''PHP MyAdmin log-on page''' where you type username and password we have just set up. | You can view any index.php or PHPMyAdmin web page in your browser by prefixing the address with your Pi IPv4 address e.g. a URL like '''http://192.168.1.xyz/phpmyadmin''' where ''xyz'' is 2 or 3 digits you look up as mentioned before where FTP was described. If your Pi has both Ethernet and wireless connection, there will be two different values of ''xyz'' for you to choose one from. On first run of PhpMyAdmin, as already mentioned, you will see a '''PHP MyAdmin log-on page''' where you type username and password we have just set up. | ||
===Populating your database tables on your Pi=== | |||
Cumulus MX has functionality to update database tables at one of 3 intervals: | |||
#real-time | |||
#standard interval | |||
#end of day | |||
The database tables can use the column names in the schemas pre-defined by Cumulus MX or in a custom schema (where you specify the column names). The settings are all in [[MX_Administrative_Interface#MySQL_settings]], so read that section to find out more. | |||
You might have started using MX before you set up your database. There is a option in that part of the admin interface to create database tables (as required) for each of the 3 updating intervals. For example, the default name for the table updated at the standard interval is "monthly", but you could give it a name of "standard" or whatever you like. | |||
The MX release distribution includes another executable. Type <tt>cd CumulusMX && ExportMySql.exe monthly</tt> to run the executable. The first parameter is "monthly" even if the table has a different name. | |||
#if the table name is defined in the admin interface, but the actual table does not exist, this executable will create a table with that name and the columns for the schema that applies at the release version. | |||
#*if the table already exists, then the executable will use it. | |||
#There is an optional second parameter that specifies the log file name to read. | |||
#*if the second parameter is not specified, this executable will look at every log file (in "data" folder and with file name that starts with month, then has "log.txt", | |||
# for each line in the log file the executable will try to insert a row in the database table | |||
#*the SQL syntax used is "insert ignore", so if the row for that log file line already exists in the table, it will skip onto next line of log file. | |||
Similar instructions apply for the end of day table, although as there is only one daily summary log file, there is no optional second parameter, just type <tt>cd CumulusMX && ExportMySql.exe dayfile</tt>. | |||
There is no way to use this executable for insert of past rows into the real-time table. | |||
If you have been running Cumulus on another device previously and already have database tables, the next section explains how you can create SQL to export your old database and use that SQL to populate the new table. | |||
===Transferring database tables to your Pi=== | ===Transferring database tables to your Pi=== | ||
| Line 625: | Line 650: | ||
If you have been using Cumulus before (and already have a database) then you can use PhpMyAdmin on your old device to '''export''' out all the '''Cumulus tables as SQL''' in a zip file, FTP that zip file across to your Pi, then use PhpMyAdmin on your Pi to '''import''' that zip file. | If you have been using Cumulus before (and already have a database) then you can use PhpMyAdmin on your old device to '''export''' out all the '''Cumulus tables as SQL''' in a zip file, FTP that zip file across to your Pi, then use PhpMyAdmin on your Pi to '''import''' that zip file. | ||
Providing you selected the right options for what SQL you created in your export, the import will contain SQL to create the tables and to insert all the rows into each Cumulus table on your Pi. Please note that there is a limit of around 1000 rows that can be imported in one action, so for your bigger tables, you will only create the table once, but you will do several '''Replace''' row actions (export from old device, then import to Pi) each transferring just one thousand rows, until the whole table is on the Pi. | Providing you selected the right options for what SQL you created in your export, the import will contain SQL to create the tables and to insert all the rows into each Cumulus table on your Pi. Please note that there is a limit of around 1000 rows that can be imported in one action, so for your bigger tables, you will only create the table once, but you will do several '''Replace''' row actions (export from old device, then import to Pi) each transferring just one thousand rows, until the whole table is on the Pi. You may prefer to use "ExportMySQL.exe" as described in previous section to recreate your bigger tables from the Cumulus log files. | ||
You might want to also '''export/import the tables in the PhpMyAdmin database''' | You might want to also '''export/import the tables in the PhpMyAdmin database''' (as they contain your preferences for each of your tables) in a separate zip, although these might need some extra transformations, as they are specific to a particular version of the database server (and the old database server version may not match the MariaDB version on your Pi). Some PhpMyAdmin tables do change for different versions of the tool, so that too may make export/import of its tables more complicated. | ||
=== Restarting Web Server === | === Restarting Web Server === | ||