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.

little theoretical question. I'm done setting up a bunch of 2-node MySQL 5.1 clusters user the control of several MMM instances.

We started some testing, joyously kill -9 the writer nodes and all went fine with the app chugging along, oblivious of the DBMS turmoils.

Then I thought, what if in production first server A goes down, server B takes over and more work is done, and finally B goes down as well.

If the sysadmin restarts the cluster first from A and later joins B while in the meantime work is done on the outdated data of A?

Does MySQL have a quorum mechanism that keeps A (or even B) in Recovery mode until it has decided what is the most recent transaction to continue from?

Thanks and apologies if it's an FAQ...

share|improve this question

1 Answer 1

This is probably more of a starter answer, to get the discussion rolling. I expect some MySQL guru to come along and give you the right answer... ;-)

but for this scenario I have configured my clusters to use offset auto-update sequences in order that when you bring the primary master back online, that the data that was written to the slave will not conflict like so;

So on the primary master

 auto_increment_increment=10
 auto-increment-offset=1

on the slave machines (each slave with a different offset)

 auto_increment_increment=10  
 auto-increment-offset=2

this means that you can replicate the binary log off the slave, back into the master to recover the Data that was written there during the master outage.

If you are using MySQL 5.1+ (without the server-id bug version) then you can configure master-master, and have the master replicate the "lost" queries back off the slave automatically.

(unfortunately I think this is beyond the capabilities of MMM, but then again I've not looked at MMM for a few years)

share|improve this answer
1  
Hi, thanks for your answer I appreciate your time. Let me explain better: I'm not worried about conflicting keys too much, I'm actually worried of the timeline conflict. If A goes down and the cluster fails over to B, updates are only persisted to B. My worry is: if B also goes down and the sysadmin restarts the cluster by bringing A back ONLINE, the new updates will flow into A, unaware of all the changes that happened on B while A was offline. Unless A, being slave of B remains locked on AWAITING_RECOVERY until B is also brought online and all transactions are replicated back. –  Eddy May 24 '12 at 9:45
    
yes, the point is that after fail-over - B is the new master. The sys-admin brings A online as a slave, otherwise you get the conflict you described, you need to switch the write-master and read-only roles around until the nodes are re-synced –  Tom H May 24 '12 at 10:12
    
If you performed the process that you described, (A fails at time t1, B promoted to master at t1, B-fails at t2, A restored as master at some t3), you would be effectively restoring A from a backup at time t1. You would have to manually process the B bin-log back onto A. Theoretically if these transitions had been discrete if you apply the B bin-log representing the time period t1-t2, both servers would be in sync –  Tom H May 24 '12 at 10:15
    
I should make it clear that you would have to manually process the B bin-log back onto A, while making both A and B read-only to prevent conflicts from new data –  Tom H May 24 '12 at 10:21
    
Sorry, reading back your comment. You were exactly right the first time. "Unless A, being slave of B remains locked on AWAITING_RECOVERY until B is also brought online and all transactions are replicated back. " –  Tom H May 24 '12 at 10:22

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.