Working through the command line interface, or CLI, grants a great deal of control over the sync, including taking backups and verifying their integrity, but requires that someone with knowledge of the necessary programs and utilities be available to execute it each time.
The commands, however, stay fairly consistent, and are therefore highly scriptable with extra logic to account for certain conditions. Scripting the sync commands both simplifies the process for one-time use and makes it easy to automatically execute via cron, a job scheduler built in to most Linux systems.
The wot-dbsync.sh utility, available from the tools repository on our GitHub, is such a script, and is freely available to anyone who’d like to use it.
To work, the script attempts to connect to two servers running MySQL, either by connecting to MySQL directly through the Internet, or by connecting through SSH and then connecting to MySQL locally.
For example, let’s say we have two servers running MySQL: wideopentech.com and dev.wideopentech.com. Let’s further say that we want to sync a WordPress database, wp_wot, from wideopentech.com to dev.wideopentech.com.
We first SSH in to dev.wideopentech.com, then initialize the script to connect to wideopentech.com:
$ ./wot-dbsync.sh sync -s user@wideopentech.com -h 127.0.0.1 -u wp_user -p secret_wp_password -d wp_wot
Let’s recap: the -s flag specifies that MySQL on wideopentech.com only accepts local connections, so we’ll need to SSH in as user@wideopentech.com first. Once we’re connected through SSH, we can open the MySQL connection by passing 127.0.0.1, or localhost, with the -h flag. Finally, we specify the MySQL username with -u, password with -p and the database we’d like to export with -d.
Now let’s complete the command to connect to MySQL on dev.wideopentech.com and complete the sync:
$ ./wot-dbsync.sh sync -s user@wideopentech.com -h 127.0.0.1 -u wp_user -p secret_wp_password -d wp_wot -H 127.0.0.1 -U wp_dev_user -P secret_wp_dev_password -D wp_wot -o /home/user/db-backups
The flags follow the same format, but are capitalized for the secondary connection. Here the -S flag, for SSH, is not necessary since we’re already connected to the dev.wideopentech.com server. Instead, we can connect directly to MySQL through 127.0.0.1, or localhost. The final parameter, -o, specifies the filesystem location we’d like to store our database exports in.
We can now execute the script. According to the parameters set, the script will:
- Connect to wideopentech.com through SSH as user (use key-based auth to avoid having to enter a password).
- Connect to MySQL on wideopentech.com through 127.0.0.1 as wp_user.
- Export the wp_wot database to /home/user/db-backups on dev.wideopentech.com.
- Connect to MySQL on dev.wideopentech.com through 127.0.0.1 as wp_dev_user.
- Export the wp_wot database to /home/user/db-backups on dev.wideopentech.com.
- Verify that the exports were successful, and that the files are non-zero.
- Overwrite the wp_wot database on dev.wideopentech.com with the export from wideopentech.com.
That’s all! The production database has now been synced to development and testing can proceed with the freshest data.
The script has additional options, for example customizing MySQL ports, which can be reviewed in the FAQ section at: https://github.com/wideopentech/tools/blob/master/wot-dbsync.sh.
