MySQL Replication 'Catchup' UPDATES keep newer record

Question

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?

Answer 1

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

• Statement-based Replication (SBR) transmits, via the binary logs/relay logs, the SQL need to be executed on a Slave.
• Row-based Replication transmits the actual row-level change via the binary logs/relay logs. Those logs usually grow much faster that SBR.
• See my Dec 09, 2011 in the DBA StackExchange and Aaron Brown's comment on MySQL's ability to switch modes in the presence of unsafe SQL statements

Answer 2

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.