What is the best way to merge development database with production?

Question

We develop WordPress websites and we have returning customers who request revisions to their website that require us to develop in a development environment before publishing to production (live). The challenge has always been how to merge two WordPress databases that are out of sync but have mutually new content that must be retained.

This content ranges from Pages, Posts and Comments to Plugin settings and other subtle information. So we export both databases and use FileMerge, for Mac, to display the differences and manually merge them. This would be fine if the WordPress databases didn't contain so much meta information that is, otherwise, human unreadable (examples below).

My question is whether or not someone out there has a recommendation on how to solve this problem? Essentially if we could just omit the items below (assuming that's safe to do?) then we would be perfectly happy continuing to use FileMerge. But with those items it makes it feel like we're decrypting the worlds longest password.

FYI WP Migrate DB (including Pro) is not capable of this. We spoke with the authors.

Thanks.

The following is a list of database records that I think could be omitted without causing any conflicts in the database. - _transient_random_seed - active_plugins "a" value (which I believe represents the number of active Plugins) - _site_transienty_timeout_browser - recently_edited - db_version - cron - _transient_timeout_feed_mod - _transient_feed_mode - rewrite_rules - Plugin Versions - _edit_locks

Answer 1

I've been looking at two potential solutions to this problem, however both have the limitation of requiring php exec() on the development/staging server. So hosts like wpengine (which we're using) won't work with them.

If this isn't a limitation for you then check out https://revisr.io/ or http://versionpress.net/

Revisr is open source and VersionPress is currently using a paid Early Access Program to support development. Both use git to manage all aspects of Wordpress syncing from the WP-Admin.

Answer 2

Try MySQL Data Compare from Red Gate. I use the MSSQL version myself and its one of my favorite development tools. You can select exactly which tables and even the individual records you want to sync. Now for the bad news it only runs on Windows and it is not cheap. If I wouldn't be developing on a Windows machine myself I would buy one just for this program.

http://www.red-gate.com/products/mysql/mysql-data-compare/