How to sync tables in master-master MySQL replication

How to sync tables in master-master MySQL replication

with 6 comments

Suppose you have a master-master replication setup, and you know one of the tables has the wrong data. How do you re-sync it with the other server?

Warning: don’t just use any tool for this job! You may destroy your good copy of the data.

If your table is large, you’ll probably want to use a tool that can smartly find the differences in a very large dataset, and fix only the rows that need to be fixed. There are several tools that are either able to do this, or claim to be able to do this. However, most of them are not replication-aware, and are likely to either break replication or destroy data.

To see why this is, let’s look at a typical scenario. You have server1 and server2 set up as co-masters. On server1, your copy of sakila.film has correct data. On server2, somehow you are missing a row in that table. A hypothetical sync tool will compare the two copies of the data and find the missing row, then insert it on server2. This INSERT statement will flow through replication to server1, where it will cause a duplicate key error and stop replication.

You can probably think of many other scenarios with lots of bad side effects, so I won’t list any more. I’ll leave it at this: when you are synchronizing data on a slave (even if it is also a master), you must not change data on the slave. Changing data on the slave can cause so much trouble in so many different ways! The correct way to do this is to make the changes on the master, and let them flow through replication to the slave.

As far as I know, there is only one tool that is capable of doing this. It is mk-table-sync, which is part of Maatkit. However, even this tool will let you point the gun at your foot and pull the trigger, if you don’t use it correctly.

The correct way to sync a master-master setup with mk-table-sync is with the --synctomaster option, which tells it to make changes on the master:

mk-table-sync --synctomaster h=server2,D=sakila,t=film

Notice that I’m connecting to the slave, but instructing it to make changes on the master. (Yes, it is able to find the master by inspecting the slave).

If you do the following, you’ll probably cause problems:

mk-table-sync h=server1,D=sakila,t=film h=server2

I’ve just updated the documentation to point out the subtleties with master-master replication. However, you should always keep in mind: it’s not just master-master replication. Any replication configuration is best synchronized by making the changes on the master, and you should always avoid changing data on a slave — even to “fix” the slave. I might also add a feature to mk-table-sync to warn you when it detects that you are trying to change data on a slave.

Written by Xaprb

February 29th, 2008 at 10:29 am

Posted in Maatkit, SQL

Tagged with master-master-replication, mysql, replication, SQL
« Remember to sign up for MySQL Conference and Expo!
Send your employees to the MySQL Conference »
6 Responses to 'How to sync tables in master-master MySQL replication'

Subscribe to comments with RSS or TrackBack to 'How to sync tables in master-master MySQL replication'.

1.

Hey Baron,

Tried this out and it works like a charm.

Have Fun

Paul

PaulM

4 Mar 08 at 6:51 am
2.

Thanx your article

Ödev Ders Öss SBS

16 May 08 at 10:04 am
3.

I do see a problem though, unless I missed anything in the docs. I have a large table, 500,000 rows, and when data is out of sync, it seems to want to delete almost everything, and then issue a replace statement … well if this happens on the master, then I just lost all my data, until the replace statements are run .. What happens if this script is cancelled mid-way? im assuming all changes done will not roll back, etc?

kosta

2 Jun 08 at 5:30 pm
4.

Hi,
I think I found a problem with master-master replication through an ssh reverse port forward, in which the master of both server are 127.0.0.1 on port 3307

I´ve filled an issues explaining this in here


http://code.google.com/p/maatkit/issues/detail?id=110&colspec=ID%20Type%20Tool%20Status%20Milestone%20Owner%20Summary

thanks

Joe

24 Oct 08 at 10:42 am
5.

let’s say, both masters could not reach each other for a while, and meanwhile one different record with same unique column was inserted to each of them.

is there a way to sync these tables?

Aurimas

2 Nov 09 at 1:04 pm
6.

I think that is best to do manually :-(