6.13. Auto-Increment in Multiple-Master Replication

When multiple servers are configured as replication masters, special steps must be taken to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows.

The auto_increment_increment and auto_increment_offset system variables help to accommodate multiple-master replication with AUTO_INCREMENT columns. Each of these variables has a default and minimum value of 1, and a maximum value of 65,535. They were introduced in MySQL 5.0.2.

These two variables effect AUTO_INCREMENT column behavior as follows:

By choosing non-conflicting values for these variables on different masters, servers in a multiple-master configuration will not use conflicting AUTO_INCREMENT values when inserting new rows into the same table. To set up N master servers, set the variables like this:

For additional information about auto_increment_increment and auto_increment_offset, see Section 5.2.2, “Server System Variables”.