Syncing a Production Database to Development with

Aug 19, 2016 | John Cornelius, Web Development

To ensure reliable testing, periodically syncing a production database to development becomes a necessary step after taking an application live. Though complex tools for managing databases may make sense for some use cases, more often than not the sync can simply be executed from the command line.

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 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: and Let’s further say that we want to sync a WordPress database, wp_wot, from to

We first SSH in to, then initialize the script to connect to
$ ./ sync -s -h -u wp_user -p secret_wp_password -d wp_wot

Let’s recap: the -s flag specifies that MySQL on only accepts local connections, so we’ll need to SSH in as first. Once we’re connected through SSH, we can open the MySQL connection by passing, 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 and complete the sync:
$ ./ sync -s -h -u wp_user -p secret_wp_password -d wp_wot -H -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 server. Instead, we can connect directly to MySQL through, 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 through SSH as user (use key-based auth to avoid having to enter a password).
  • Connect to MySQL on through as wp_user.
  • Export the wp_wot database to /home/user/db-backups on
  • Connect to MySQL on through as wp_dev_user.
  • Export the wp_wot database to /home/user/db-backups on
  • Verify that the exports were successful, and that the files are non-zero.
  • Overwrite the wp_wot database on with the export from

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:

Share This