Master-Master MySQL Replication…that hurts less

If you have ever touched a MySQL slave, you know that they can and do frequently halt. While sync problems can be caused by many things—network outages, schema changes, etc—one of the most common problems in a dual-master setup is primary key collision.

Primary Key Collision

…happens when records are added on two different servers to the same table and get the same AUTO_INCREMENT value. Fortunately, there is a trivially easy way to prevent this from happening.

auto-increment-increment=N

Adding this to your my.cnf or my.ini file will make AUTO_INCREMENT increment by N rather than by 1. N is the number of replicated servers that are masters.

Combine this setting with…

auto-increment-offset=N

…to ensure that each replicated master uses unique AUTO_INCREMENT values. N should match the server-id setting.

With these two settings in place, your primary keys will never collide.

Example

Let’s suppose you have three replicated master MySQL servers, each with a `comments` table with a last id of 1000. Your servers are configured as follows:

Server 1:

server-id = 1
auto-increment-increment = 3
auto-increment-offset = 1

Server 1 will generate AUTO_INCREMENT values of the form 1 + 3N (4, 7, 10, 13…), where N is a sequential positive integer.

Server 2:

server-id = 2
auto-increment-increment = 3
auto-increment-offset = 2

Server 2 will generate AUTO_INCREMENT values of the form 2 + 3N (5, 8, 11, 14…), where N is a sequential positive integer.

Server 3:

server-id = 3
auto-increment-increment = 3
auto-increment-offset = 3

Server 3 will generate AUTO_INCREMENT values of the form 3 + 3N (6, 9, 12, 15…), where N is a sequential positive integer.

At the same instant, three different visitors to your web application comment. Here’s what happens to the `comments` table:

  • Server 1: AUTO_INCREMENT = 1003 (1 + 334 * 3)
  • Server 2: AUTO_INCREMENT = 1004 (2 + 334 * 3)
  • Server 3: AUTO_INCREMENT = 1005 (3 + 334 * 3)

Let’s suppose that replication stopped before these three new rows could be replicated on the other two servers, and three more visitors leave comments. What IDs will be assigned?

  • Server 1: AUTO_INCREMENT = 1006 (1 + 335 * 3)
  • Server 2: AUTO_INCREMENT = 1007 (2 + 335 * 3)
  • Server 3: AUTO_INCREMENT = 1008 (2 + 335 * 3)

Beautiful!

4 thoughts on “Master-Master MySQL Replication…that hurts less”

  1. Hey Jonathon,
    it is not clear where you’ve got value 334 for the first insert.
    Can you explain, pls ?

  2. Sure! Note that with these settings enabled, MySQL’s AUTO_INCREMENT function uses the algebraic formula to determine the next ID:

    ID = A + (B x N)

    where A is the value of auto-increment-offset, B is the value of auto-increment-increment, and N is the current position with in the number series generated by the formula above.

    In my example, I assumed that the last ID in the table was 1000, and B = 3. So I derived N by dividing 1000 by 3 and rounded up to get 334, which is the value of N required to get the next number in the AUTO_INCREMENT series.

    N will increment by 1 as you add rows, unless you insert an ID that skips ahead in the sequence.

  3. So if we have 2 master replication server.
    Then auto increment value will be 2 in both the servers
    and offset will be 1 and 2.

    please confirm.

Leave a Reply

Your email address will not be published. Required fields are marked *

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>