Wednesday, August 13, 2014

SBR vs RBR when using On Duplicate Key Update for High Availability

The other day, I was discussing high availability (HA) with other people.  The discussion was going in the direction of, as the application was only using INSERT ... ON DUPLICATE KEY UPDATE (IODKU) and traffic could be replayed, promoting a slave in case of the failure of the master was easy: replaying traffic would make all the slaves converge back to a common state.  It looks as this could work but it might not be as simple.  Before going into the details, let's take a step back and look into HA in general.

tl;dr: be careful when using row-based replication when replicating IODKU as slaves will stop replicating if a row is present on the master and missing on slave (or vice-versa).