DRBD and MySQL – Excellent Low-cost HA Solution

“HA with MySQL Replication

“MySQL high availability is often implemented with its in-built
replication technology. The standard master-slave configuration
provides one database as the primary, receiving all write traffic
that is all changes to data. Read traffic, i.e. SELECT queries, can
be sent to the primary or to the replicated slaves. Transactions
flow on the primary database into its binary log. The slave keeps a
watchful eye on the primary, copying new transactions into its own
relay log. Keep in mind until 5.1 this involved copying the
*actual* SQL statements albeit in binary form. Once they make it to
the slave database, another thread then applies those SQL
statements in a serial fashion thus theoretically keeping that
slave in the same state as the primary.

“The trouble comes when your replication stops, and the error
log shows some funny error about duplicate keys or failed primary
key constraint. How’s that possible? If all the same transactions
are being applied in serial, the two databases should never have a
case like this. Strange indeed. It turns out that as we all know, a
lot of things can happen when a query executes to interrupt it or
cause otherwise anomalous behavior.”

Complete Story