-
Notifications
You must be signed in to change notification settings - Fork 297
Transfer from sqlite to MySQL
This article is about moving data from sqlite to MySQL. For information on moving the other direction, see its companion article, Transfer from MySQL to sqlite.
There are two general approaches to this problem:
- Use the command line utility
sqlite3
to dump the contents of the sqlite database to a file, then massage the file, then upload its contents to MySQL using the utilitymysql
; or - Use the
weectl database
utility to transfer the sqlite archive to a MySQL database (Version 5 only).
The first is more general and can be done independently of WeeWX. The second is simpler, but requires access to a WeeWX installation. It also tends to be faster because the entire transfer is done as a single transaction.
For approach #1, first dump the sqlite database using the utility sqlite3
. Adjust the path to the sqlite3 database, weewx.sdb
, as necessary. Note: you may have to install sqlite3
first.
$ echo ".dump archive" | sqlite3 weewx.sdb > weewx.sql
Now recreate in MySQL. Start by creating the database:
$ echo "CREATE DATABASE weewx" | mysql -u root -p
Now restore using the contents of the file weewx.sql
, massaged a bit:
$ sed -e '/PRAGMA/d' -e 's/BEGIN/START/' < weewx.sql | mysql -u root -p --database=weewx
This will result in a database weewx
in MySQL, with a single table, archive
, holding the contents of your sqlite archive file.
If the new database is to be used as the WeeWX archive, WeeWX will need to be configured to use MySQL as per the section Configuring MySQL / MariaDB in the User's Guide.
There is one more step before the new database can be used with WeeWX: creating the daily summaries inside the new database. If WeeWX is configured to use the new database the daily summaries will be built automatically at the next startup. Alternatively, the daily summaries can be built manually:
$ weectl database rebuild-daily
Using the utility weectl database
is often faster. My modest Intel NUC, using
a solid-state disk, can process about 1,500 records per second.
To transfer a database using weectl database
, data bindings for both the
source and destination databases must exist in weewx.conf
. Because the source
database most likely will already exist, in most cases, only a binding for the
destination database will need to be added to weewx.conf
. To do this, edit
weewx.conf
, locate the [DataBinding]
section, and add a binding for the
destination database similar to the following:
[[dest_binding]]
database = archive_mysql
table_name = archive
manager = weewx.manager.DaySummaryManager
schema = schemas.wview_extended.schema
The above settings adds a new database binding dest_binding
. It will use the database archive_mysql
in the [Databases]
section of weewx.conf
, and the wview_extended
database schema. If this is not the case (e.g., you use a modified schema) you will need to alter one of more of the above settings accordingly.
Before doing the transfer, it may worthwhile to run weectl database transfer
with the --help
option:
$ weectl database transfer --help
Note the --dry-run
option. When used with the transfer
action, weectl database
prints out what would happen but does not actually do the transfer.
$ weectl database transfer --dest-binding=dest_binding --dry-run
This will result in output something like this:
Using configuration file /Users/user-name/weewx-data/weewx.conf
This is a dry run. Nothing will actually be done.
Transfer 3654 records from source database 'weewx.sdb' to destination database 'weewx' (y/n)? y
Transferring, this may take a while....
Completed.
0 records transferred from source database 'weewx.sdb' to destination database 'weewx' in 0.09 seconds.
This was a dry run. Nothing was actually done.
If the dry run was successful the transfer can be completed using the following command:
$ weectl database transfer --dest-binding=dest_binding
This will result in output something like this:
Using configuration file /Users/user-name/weewx-data/weewx.conf
Transfer 3654 records from source database 'weewx.sdb' to destination database 'weewx' (y/n)? y
Transferring, this may take a while....
Records processed: 3000; time: 2022-07-31 00:31:00 PDT (1659252660)
Completed.
3654 records transferred from source database 'weewx.sdb' to destination database 'weewx' in 0.93 seconds.
Once the transfer is complete and before the new database can be used with WeeWX
the daily summaries need to be created inside the new database. If WeeWX is
configured to use the new database, the daily summaries will be built
automatically by WeeWX at the next startup. Alternatively, the daily summaries
can be built manually using the weectl database
utility:
$ weectl database rebuild-daily --binding=dest_binding
This rebuilds the daily summaries of the database referred to by the binding
dest_binding
in weewx.conf
.
If the new database is to be used as the WeeWX archive then WeeWX needs to be configured to use MySQL as per the Configuring MySQL / MariaDB in the User's Guide.