How to keep two MySQL databases in sync?

问题:

Is there a way to keep two databases in sync? I have a client who's running WordPress with MySQL. Is there a way to take a copy of the database the current state, and use it for a development server, and then when the dev changes are done push it back to the live site?

The client might make changes to the live site while I'll be working on the dev version, and wondering if there will any merge conflicts.

If I import the updated database via phpmyadmin, will it only update with only the newest changes or overwrite everything?

回答1:

Here's a quick reference of MySQL Replication by @Mark Baker or you can use MySQL Workbench Synchronization.

回答2:

So I finally found a solution to my problem. Since this was an issue for WordPress I found two plugins that worked really well.

Free one: Database Sync
Very simple and has an easy push/pull interface.

Paid Plugin $40-200: WP Migrate DB Pro
Much more polished and has an option to select specific tables you want to sync.

回答3:

There's an answer to the duplication problem here. However, that's only the start of your difficulties. If two people are making changes independently to two copies of one database, merging the two will inevitably cause nightmares. In short, yes there will be merge conflicts. Exactly what, and what you do about it, will depend on the nature of the changes each of you have made. Good luck!

回答4:

Other modern (this post is quite old) paid solutions to the problem would be deevop and mergebot.

Mergebot is a plugin saas, that helps with complicated merges between the different development and production databases, specifically for WordPress.

deevop is a more comprehensive solution providing the development environment but also having many options for complex data syncronisation between phases (excluding tables, etc) not only for WordPress but for other platforms, too.

You can even combine both and use deevop as deployment manager (one click deploy to/from production) and then use mergebot for the complex database merges.