How to keep two MySQL databases in sync?

Question

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?

MySQL Replication; but what major structural changes are you making to the dev database? — Mark Baker, Jun 17 '13 at 01:06
You shouldn't ever have to have two databases running with the same content. — Adam McArthur, Jun 17 '13 at 01:14
@MarkBaker It's a small site, the changes I'm adding are new features to the admin side. I'm mostly worried about over writing changes that are newer. In my tests so far it seems to only update the tables that have changed. I'm just be cautious before I start working with the client's site. — Alex, Jun 17 '13 at 01:17
Sorry I thought you wanted to permanently run them side by side. I've done what you're asking before - just export the database content FROM WORDPRESS and then import it into a fresh copy of wordpress on your local server. Any changes the client makes will have to be re-added manually either as they happen or at the end. — Adam McArthur, Jun 17 '13 at 02:15
@AdamMcArthur Yeah, that's why I'm looking for a way to compare, once I'm done I want to see what is different from live site. Looks like MySQL Workbench is going to help. — Alex, Jun 17 '13 at 02:46

Mark · Answer 1 · 2013-06-17T01:27:51.630

9

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

edited Jun 17 '13 at 01:27

answered Jun 17 '13 at 01:11

Mark

8,046
15
48
78

1

Ah, this looks like it might be a great solution. Investigating now. – Alex Jun 17 '13 at 01:19
Though you have to install MySQL Workbench on your computer. – Mark Jun 17 '13 at 01:24
The MySQL Workbench Sync looks like it's going to do the ticket. Thanks. – Alex Jun 17 '13 at 02:54
@ChristianMark will that solution work in real time aswell, I mean keep it sync and whenever I update any value it do sync again automatically. – Wasim A. Dec 15 '15 at 16:26
@Wasim, yes with proper configuration. – Mark Jan 04 '16 at 01:06
@ChristianMark could you please send me some url that can help me to config mysql that way. – Wasim A. Jan 04 '16 at 09:41

score 6 · Accepted Answer · answered Mar 17 '14 at 19:02

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.

score 1 · Answer 3 · edited May 23 '17 at 10:30

1

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!

edited May 23 '17 at 10:30

Community

1
1

answered Jun 17 '13 at 01:09

I don't think our changes will be to the same tables, and everything will be very cautiously done and backed up, but I'd like it to be a smooth process. – Alex Jun 17 '13 at 01:20

score 1 · Answer 4 · answered Mar 26 '17 at 18:28

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.

deevop looks interesting. Mergebot is from the same people who made WP Migrate Pro (Delicious Brains) — Alex, Mar 27 '17 at 19:36

How to keep two MySQL databases in sync?

4 Answers4

Linked

Related