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. 「システム変数」.