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 5.2.2, “Server System Variables”.