Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Is the problem that replication has broken on one, or is the problem that each master has received mutually incompatible writes? –  David M May 18 '10 at 15:48

3 Answers 3

up vote 0 down vote accepted

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.

share|improve this answer
    
Your answer seems to be the closest to what I need, thanks! MySQL rep broke both ways, and I think writes at some point a failover has dropped writes on both servers. We are using auto_increment offsets to ensure that the records on each server do not conflict. My problem with just restarting it, is that replication has been broken for a sufficient time before I was put onto this that bin-logs have been rotated out, which complicates things nicely, hence wanting a way to merge the two databases, which should contain distinct, non-conflicting data.. –  Kirrus May 20 '10 at 11:03

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.

share|improve this answer

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.

share|improve this answer
    
Check mysql server error logs to find why replication stopped. –  lg. May 18 '10 at 13:52
    
Be careful. From the documentation: mk-slave-restart watches one or more MySQL replication slaves and tries to skip statements that cause errors. It polls slaves intelligently with an exponentially varying sleep time. You can specify errors to skip and run the slaves until a certain binlog position. –  Warner May 18 '10 at 14:19
    
Note: it has come to my attention that Yahoo! had or has an internal tool called fix_repl, described to me by a past Yahoo! employee and mentioned in the first edition of High Performance MySQL. Apparently this tool does the same thing. Make no mistake, though: this is not a way to "fix replication." In fact I would not even encourage its use on a regular basis; I use it only when I have an error I know I just need to skip past. –  Warner May 18 '10 at 14:20
    
The question specifies that it's 2-way replication. I believe that he's saying he has two servers which replicate from each other. If one just puked on a replicated statement, that might be an easy fix. If the two masters have received mutually incompatible writes, that's a bit tougher. –  David M May 18 '10 at 15:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.