How to Sync Databases Across Multiple WordPress Installs

Previously, we had a look at how to setup staging environment for WordPress development. If you have followed it through, you might find that the process sometimes involves migrating the SQL database to synchronize the discrepancies between the stages. This particular process of migrating database could be tedious.

You will have to export the database from the first site then import it to another one, which could take a couple of minutes or more for a huge database. This process will need to be repeated each time we migrate the database.

If this is something that you have been struggling with, you certainly would find this post useful. In this post, we are going to see how streamline the database synchronization across multiple WordPress installs. This will help us save a lot of time.

Getting Started

To begin with, I assume that you have had the three development stages – Local, Testing and Live – all set up.

Here, I’ve setup a scenario where I have 5 posts published on a live site, and 2 more posts are in a local site (one that is still being developed). In reality though you will be dealing with a lot of posts, than just 2 or 5.

Now I would like to update the local site with the content on the Live site. Having an identical database content as the one found on Live, while developing, is encouraged. This is so we could catch any issues regarding the styles, layout, or any thing that may affect the content early on.

Syncing the Database

To sync the database, follow these steps:

  1. Install the WordPress plugin, Database Sync. Just install the plugin in the websites where you would perform the database migration, let’s say if you were to pull the database from a Live stage into the Testing stage, then you should install the plugin in the site for these two stages.

  2. In the Live site, go to Tools > Database Sync. Then, generate the secret token key.

    You must not share this secret key to anyone as it will allow them to access your database as well.

  3. Add the token in the Tools > Database Sync of the Local stage site.

    As the Token is added, you will find the Sync next to the linked site, as shown in the above screenshot.

  4. Click the Sync button to start synchronizing these two sites.

  5. Click the Pull button to pull the remote site database down and replace the local site.

Keep in mind though that the process of pulling and pushing may take minutes, or longer, depending on the size of the database. Once the pulling process is complete, you should see a success message on the screen. And the local site should now have the exact same posts, pages, and settings as the Live site.

Alternatively, click the Push button if you would like copy the current site to remote websites. Install this plugin in multiple web sites as needed and add the tokens to link to their database.

This plugin makes database synchronization seamless and sound. I hope that the plugin developer would improve it further with some additional features, such as:

  • Selective Table Sync, which basically enables sync for particular tables in the database. For example, as we only need to copy the post content, it would be better to Pull the wp_posts rather than the whole database. This will tremendously speed up the process for most cases.
  • Media Sync, to synchronize WordPress media file uploads like for images and videos.