PHP

How to Synchronize WordPress Live and Development Databases

Craig Buckler
Share

database synchronization

This article is part of a series created in partnership with SiteGround. Thank you for supporting the partners who make SitePoint possible.

Developing WordPress themes is considerably easier with real content. Unexpected situations begin to arise when people add pages, posts, media and comments. Your beautiful template can break when:

  • editors use assets which are too big or small for your breakpoints to handle
  • managers introduce a new menu item which doesn’t fit at lower screen sizes
  • your theme implements a two-level menu hierarchy which fails to cater for the inevitable third-level page
  • long or deeply-nested comments become unreadable.

Ideally, your development server should have a snapshot of your live production server’s database. Your workflow can be improved further if content is automatically synchronized when changes occur.

Synchronization Snags

One-way WordPress database replication can be more challenging than you expect. There are good reasons why few coders live in this development dreamland…

Data Differences

WordPress stores various configuration settings in its database. This includes the domain name and paths which are used throughout posts and asset references. Importing this information into a local database (perhaps hosted at http://localhost/) will lead to undesired consequences. It may look as though it worked but image and link URLs could be taking you back to the live server.

To add to the complications, WordPress serializes some data. It can be difficult to parse and change those values.

Multiple Installations

Synchronization is less necessary and complicated when you’re a sole developer working on a local installation and uploading to a single live server. Life is more complicated for teams with multiple developers, staging servers, and possibly more than on production server.

Databases can be Large

SitePoint runs on WordPress and contains many thousand articles, drafts and revisions. Even if fast incremental synchronization were implemented, you’re unlikely to want or need numerious megabytes of post data.

It’s Not Just Databases

WordPress stores uploaded images and other media in the local file system. Those need to be downloaded and the database references must be updated accordingly.

You must also ensure theme and plugin files are synchronized, enabled and configured appropriately. That said, you possibly do not want certain plugins enabled, e.g. multi-site, caching, etc.

It’s Dangerous!

Could your synchronization process fail? Is it possible for synchronization to be mis-configured so development data goes live? Is there a risk the live database can be corrupted or wiped?

Absolutely.

Whatever solution you choose, ensure you have regular file and database back-ups from your live and development servers. Imagine losing everything. A few minutes spent running mysqldump is less troublesome than many days spent re-writing lost content!

Is Synchronization Possible?

Yes, but there’s no one-size-fits-all option. You may need two-way database synchronization. You may want one-way replication from live to development servers. Or you may want to push from development to live. You may require just pages. Or do you want posts, comments, users and other data? Is database data enough or do you want all media assets? Would you prefer to synchronize manually, at predefined intervals, or automatically?

One or more of the following options may be appropriate depending on your circumstances…

WP Migrate DB Pro

Perhaps the best-known and easiest choice is the WP Migrate DB Pro WordPress plugin from Delicious Brains. The commercial product has a Media Files add-on and multi-site capabilities but a free edition is also available.

Note data is pulled using in-browser Ajax methods so large databases can take time to synchronize.

WP Sync DB

WP Sync DB is an open-source fork of WP Migrate DB.

VersionPress

VersionPress is a Git-based version control plugin for WordPress which can manage database merging. The product is in development but seems promising.

WPSiteSync for Content

Offering free and commercial editions, WPSiteSync permits real-time synchronization of pages, posts and other data.

Database Sync

Database Sync is a free database-only synchronization WordPress plugin. It has not been updated for some time and is not compatible with multi-site installations but may be enough for some developers.

WordPress Importer

If you prefer a safer, more manual process, WordPress Importer exports posts, pages, comments, categories, tags, authors and other data to an XML file which can be imported into another installation. This is also a good option if you are building a live site from posts in a development system.

SyncDB

SyncDB is a bash script which synchronizes local and remote WordPress databases.

WP Staging

The WordPress WP Staging plugin is able to clone data and files from live to development or staging servers.

WordPress GitHub Sync

The WordPress GitHub Sync plugin allows you to synchronize content allows you synchronize content from a GitHub repository or Jekyll-created website. This could be an interesting option for teams requiring content editing collaboration and pull request approval workflows.

PushLive

The PushLive plugin duplicates staging to live sites with a single click. Multi-site WordPress installations are supported.

WP Stagecoach

A commercial online service which allows staging data to be merged into your live database.

MySQL Synchronization Tools

Most good MySQL database management tools offer database import, export and/or synchronization facilities. The free MySQL Workbench or is a good place to start. More advanced tools such as SymmetricDS provide features such as data transformation and filtering. Finally, you could consider options such as MySQL replication to automatically replicate data from the live master database to development slaves.

Build Your Own Solution

The only perfect solution is the one you build for your own requirements. The following tools may help:

  • mysqldump — the original MySQL schema and data back-up utility
  • mysqlpump — a new MySQL back-up utility introduced in v5.7.8 which should offer faster parallel processing and compression. Note that it is not backward-compatible with earlier versions of the database.
  • rsync — incremental file transfer
  • Git deployment options such as WP Pusher, Beanstalk and DeployBot
  • task runners such as Gulp.js or PHP alternatives such as Robo, Task and Bldr.

Do you have a perfect WordPress development and deployment workflow? Please leave your suggestions below!

For awesome WordPress hosting, we recommend SiteGround. SiteGround offers automated WordPress core and plugin updates, daily backups, and SSL certificates for free in every plan.