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_incrementcontrols the increment between successiveAUTO_INCREMENTvalues. -
auto_increment_offsetdetermines the starting point forAUTO_INCREMENTcolumn 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_incrementtoNon each master. -
Set each of the
Nmasters to have a differentauto_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”.