You are reading help file online using chmlib.com
|
When multiple servers are configured as replication masters, special steps must be taken to prevent key collisions when using auto_increment, otherwise multiple masters may attempt to use the same auto_increment value when inserting rows.
To accommodate multi-master replication with auto_increment, two
new server variables were introduced with MySQL 5.0.2:
auto_increment_increment
and
auto_increment_offset
. Each of these variables
has a default (and mimimum) value of 1, and a maximum value of
65,535.
By setting non-conflicting values for these variables, servers in
a multi-master configuration will not use conflicting
AUTO_INCREMENT
values when inserting new rows
into the same table.
These two variables effect AUTO_INCREMENT
column behavior as follows:
auto_increment_increment
controls the
interval by which the column value is incremented. For
example:
mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql>CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec) mysql>SET @auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec) mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec) mysql>INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>SELECT col FROM autoinc1;
+-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | +-----+ 4 rows in set (0.00 sec)
(Note how SHOW VARIABLES
is used here to
obtain the current values for these variables.)
auto_increment_offset
determines the
starting point for the AUTO_INCREMENT
column value. This affects how many masters you can have in
your replication setup (i.e. setting this value to 10 means
your setup can support up to ten servers).
Consider the following, assuming that these commands are executed during the same session as the previous example:
mysql>SET @auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec) mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 5 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql>CREATE TABLE autoinc2 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec) mysql>INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>SELECT col FROM autoinc2;
+-----+ | col | +-----+ | 5 | | 15 | | 25 | | 35 | +-----+ 4 rows in set (0.02 sec)
For additional information see Section 5.3.3, “Server System Variables”.
You are reading help file online using chmlib.com
|