Skip navigation links
5.2.4 Replication with Circular Masters »
Section Navigation      [Toggle]

5.2.3.1. 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.

These two variables affect 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 項4.2.3. 「システム変数」.