What to do in times of data inconsistency in Master-Master Replication in MySQL?

Question

I am building an architechture consisting MySQL Master-Master replication with HAProxy in its front. For HAProxy i am using round_robin algorithm to distribute loads between two masters. My question is when there is a heavy write situation as Seconds_Behind_Master will be greater than 0, what should I do for the data inconsistency will arise when HAProxy will go divert to an unupdated Master?

Answer 1

First point is that you should only write to one Master at a time, with the other set as the 'backup' option in HAPROXY.

This will prevent you from writing to an out of date master (unless there is a problem with the main one, in which case you probably don't have an option anyway). By writing to both masters simulataneously you are opening yourself up to a whole load of problems. If you need to write to both you should probably look at Group Replication or Galera Cluster.

Second point, I assume you have taken precautions such as setting the auto_increment_increment and auto_increment_offset values on the two databases? So that in the event you do write to them both (during testing failover etc) you won't get duplicate entries on your auto_increment values.

If you want to test for Seconds behind (and you have Linux servers) you can use a simple script adapted from here: http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/

We use it to check various values to determine if a slave database is up to date and available for use.

Answer 2

Master-Master, with both being written to, has so many perils that I regularly recommend against it.

Since all writes need to be performed on both servers, there is no performance benefit from sharing the write load.

Read load is best handled by having multiple readonly Slaves, but...

The "critical read" problem plagues most forms of Replication. This is where a user causes a write (to some Master), then moves to another web page (say) and expects to see the effect of the write -- but due to replication delay and the read going to a different Master or Slave, it is not there yet.

Is "critical read" your real problem? If so, you must abandon round-robin.

Seconds_Behind_Master is rarely truly 0, though in 'normal' times, it will round down to 0.

Galera, with 3 nodes, is a very good HA and scaling solution. There is, however, a coding technique you need to follow to avoid the critical read problem. It is outlined in my Galera blog.

With Galera and wsrep_sync_wait, any write or read can be proxied to any node. (And I vote for round-robin in this situation.)

Recent versions of MariaDB include Galera. Group Replication (MySQL 5.7.x) looks promising.