How to merge local and live databases?

Question

We've been developing for Wordpress for several years and whilst our workflow has been upgraded at several points there's one thing that we've never solved... merging a local Wordpress database with a live database.

So I'm talking about having a local version of the site where files and data are changed, whilst the data on the live site is also changing at the same time.

All I can find is the perfect world scenario of pulling the site down, nobody (even customers) touching the live site, then pushing the local site back up. I.e copying one thing over the other.

How can this be done without running a tonne of mysql commands? (it feels like they could fall over if they're not properly checked!) Can this be done via Gulp's (I've seen it mentioned) or a plugin?

Just to be clear, I'm not talking about pushing/pulling data back and forth via something like WP Migrate DB Pro, BackupBuddy or anything similar - this is a merge, not replacing one database with another.

I would love to know how other developers get around this!

File changes are fairly simple to get around, it's when there's data changes that it causes the nightmare.

---

WP Stagecoach does do a merge but you can't work locally, it creates a staging site from the live site that you're supposed to work on. The merge works great but it's a killer blow not to be able to work locally.

I've also been told by the developers that datahawk.io will do what I want but there's no release date on that.

Answer 1

It sounds like VersionPress might do what you need:

VersionPress staging

A couple of caveats: I haven't used it, so can't vouch for its effectiveness; and it's currently in early access.

Answer 2

Important : Take a backup of Live database before merging Local data to it.

Follow these steps might help in migrating the large percentage of data and merging it to live

1. Go to wp back-end of Local site Tools->Export.
2. Select All content radio button (if not selected by default).
3. This will bring an Xml file containing all the local data comprised of all default post types and custom post types.
4. Open this XML file in notepad++ or any editor and find and replace the Local URL with the Live URL.
5. Now visit the Live site and Import the XML under Tools->Import.
6. Upload the files (images) manually.

This will bring a large percentage of data from Local to Live .

Rest of the data you will have to write custom scripts.

Risk factors are :

1. When uploading the images from Local to Live , images of same name will be overriden.
2. Wordpress saves the images in post_meta generating a serialized data for the images , than should be taken care of when uploading the database.
3. Serialized data in post_meta for post_type="attachment" saves serialized data for 3 or 4 dimensions of the images.
4. Usernames or email ids of users when importing the data , can be same (Or wp performs the function of checking unique usernames and emails) then those users will not be imported (might be possible).