I've got two MySQL clusters that have Master-Master replication setup between them.

Most tables are log based, with only inserts and selects, so they don't have any problems with the replication.

However, I have a few tables that hold current 'state' information for a real time system. Entries in these tables are updated, and have a timestamp field showing their last update.

When replication breaks, the two clusters could be writing to the same rows.

Is it possible to have the replication keep the records that have the most recent updated column?

If not (and my research shows its not possible), what solutions could I use instead?

  • Two questions: 1) What version of MySQL are you using? 2) Is all your data InnoDB? – RolandoMySQLDBA Jul 26 '12 at 16:47
  • @RolandoMySQLDBA Version 5.1 (might be moving to 5.5 soon, if this is possible only on 5.5, that'll help push to upgrade). All tables are ndbcluster. – giggsey Jul 26 '12 at 19:54

If you are looking for that kind of transactional granularity, you may have to go with row-based replication instead of statement-based replication.

  • The replication is already setup to use row based binary logs. – giggsey Jul 25 '12 at 21:11

The situation you describe is called a split-brain problem for a Master-Master environment. This is when two Masters do not know of each other's existence, and work alone while assuming the other Master is dead. This is actually the most challenging issue for this kind of high availability setup. To handle this situation correctly, the admin should set up a policy that specifies which Master should be the Master when the two Masters cannot communicate with each other, and then direct all DB connections to the Master. This is normally implemented by a script that monitors the communication and re-direct DB connections when split-brain occurs. Afterwards the failed transactions will need to be synchronized back to the Master DB that was down. I've seen that Xeround claims their database service offers automatic mater-master deployment that takes care of the split brain issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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