Section 5.15. Auto-Increment in Multiple-Master Replication

5.15. 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:

• auto_increment_increment controls the increment between successive AUTO_INCREMENT values.

• auto_increment_offset determines the starting point for AUTO_INCREMENT column values.

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:

• Set auto_increment_increment to N on each master.

• Set each of the N masters to have a different auto_increment_offset, using the values 1, 2, ..., N.

For additional information about auto_increment_increment and auto_increment_offset, see Section 4.2.2, "Server System Variables."