MySQL Multiple Master Replication Problems

MySQL replication is an asynchronous process that has a limitation common to most databases – the multi-master writes problem.

Simultaneous in-flight updates to the same keys overwrite each other, and inserts can collide, causing a duplicate key error.

UPDATE Problem Example:

Master A at 15:00:01: UPDATE EMPLOYEE SET ZIP=90210 WHERE EMP_ID=1;
Master B at 15:00:01: UPDATE EMPLOYEE SET ZIP=12345 WHERE EMP_ID=1;

Results: The rows in both masters have a different zip code now.

INSERT Problem Example:

Master A at 15:00:02: INSERT INTO EMPLOYEE (ID, EMP_ID) VALUES (1,1);
Master B at 15:00:02: INSERT INTO EMPLOYEE (ID, EMP_ID) VALUES (1,1);

Because the collision occurs in the replication layer and not the application error, replication is broken and there’s no automatic way to resolve the conflict. (aside from ignoring that replication error.) Traditional application layer workarounds like adding a version or timestamp column to each row don’t help at the replication layer.

A possible workaround are to assume there is a collision, and use INSERT … ON DUPLICATE KEY UPDATE, which converts the INSERT duplicate key problem into an UPDATE problem as before.

For the above reasons, multi-master replication is not generally recommended.

MySQL replication has other problems:

  • autoincrement does not work as expected after a MySQL restart. See the manual for details for your version and storage engine.
  • reads from a lagging server can have stale results (in MySQL 5.6 there is a semi-sync replication feature that can help)
  • no binlog GUIDs (uses server-id+binlog+offset), complicating multi-level slaves management
  • ring replication is fairly difficult to manage in the case of errors
  • single threaded replication, prone to lagging.

Having said all that, an application developer may be able to work around most of the limitations if write scalability is needed:

  • have a “software switch” to route half the updates to one master, and half to the other. The source of truth is well-defined in that case.
  • analyze the data update patterns. Perhaps there is a way of using MySQL as a key-value store in a way that minimized the multi master writes problem.
  • include an autoinc or timestamp column to help partition rows for comparison with maatkit.

High Performance MySQL, 2nd Edition
“Writing to Both Masters in Master-Master Replication”, pp. 398 – 399

Matsunobu: Automated Master Failover
mongodb: Replica Set Design Concepts
wikipedia: Version vector
wikipedia: Vector clock
Why Vector Clocks Are Hard
CASSANDRA-580: vector clock support
Robert Hodges’ Notes

One thought on “MySQL Multiple Master Replication Problems

  1. James,
    >>> autoincrement does not work as expected after a MySQL restart. See the manual for details for your version and storage engine.

    I believe this is only true for InnoDB. Isn’t it?

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>