Home / server / Questions / 142923
Accepted
Kirrus
Asked: 2010-05-19 03:24:54 +0800 CST

Is there a simple way to merge a broken 2-way-MySQL replication

  • 772

I have a two way replication setup, with geographically distinct servers. Replication has broken, but I don't want to just choose one as master. Is there some way to restore replication in such a way that the two databases will be re-merged?

mysql mysql-replication

3 Answers

  1. Before you have to resolve the problems on slave...then you can restart the replication. You can also try maatkit tools: mk-slave-restart and mk-table-checksum.

    • 3

  2. In short, no. At least, if you want to maintain data integrity. I gave lg a +1 for recommending maatkit, as those tools can be helpful to compare the data set once restored.

    Depending on how it is broken, you are probably going to need to read through the binlogs. You can use the mysqlbinlog utility for this. You will want to find the last successful query executed on the slave, verify with select queries, and compare to the master's binlogs to find the position. It's tedious but with practice it can go quickly.

    If you mean dual master by "two way replication," the situation can vary. A typical dual master configuration would have an active/passive. If replication died on the active server, you can point to the last position (or set a global skip counter) in the passive master log without risking data on the active master. If the passive master replication broke, you will need to spend time in the binlogs.

    • 1
  3. Best Answer

    Read my comment on the question. If the problem is just that replication has broken because a statement went bad (are the servers running the same version of MySQL?), it may be possible just to fix the problem and restart replication.

    If the problem is actually that the masters have performed mutually incompatible writes, then that's a trickier fix, and I don't think that can be done automatically. You're probably going to have to look at the binary logs on each server and manually reconcile the conflicts.

    In the future, however, you need to do one of two things - either only have one server accepting writes at any given time or avoid auto_increment keys. I recommend the former, because the effect is that you have a server that can become the active master at any given time. If you really need to have clients always write to the nearest master, you'll need to come up with a way to partition the keys so that one master can create a row with a primary key that the other master is certain not to use.

    • 0