How to keep two MySQL databases in sync?

Question

Is there a way to synchronize two databases? I have a client that runs WordPress with MySQL. Is there a way to make a copy of the database in its current state and use it for the development server, and then, when the developer changes are completed, return it to the site in real time?

The client can make changes to the live site while I am working on the dev version and am wondering if merge conflicts will occur.

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

Answer 1

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

Free: Database Sync
Very simple and has a simple push / pull interface.

Paid plugin $ 40-200: WP Migrate DB Pro
Much more polished and has the ability to select specific tables that you want to synchronize.

Answer 2

Here is a quick reference on MySQL Replication on @Mark Baker or you can use MySQL Workbench Synchronization .

Answer 3

Here is the answer to the duplication problem here . However, this is only the beginning of your difficulties. If two people make changes to two copies of one database on their own, combining the two will inevitably lead to nightmares. In short, yes, there will be merge conflicts. What and what you do with it will depend on the nature of the changes you made each. Good luck

Answer 4

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

Mergebot are Sami plugins that help with complex merges between different development and production databases, especially for WordPress.

deevop is a more comprehensive solution that provides a development environment, but has many options for complex data synchronization between phases (excluding tables, etc.) not only for WordPress, but also for other platforms.

You can combine both and use deevop as a deployment manager (one click to deploy to / from production), and then use mergebot to merge a complex database.