20-多主复制时的自增长变量冲突问题
| 1 |
在大多数情况下，一般只使用单主复制（一台主库对一台或者多台从库）。但是在某些情况下，可能会需要使用多主复制（多台主库对一台从库）。这时，如果主库的表采用自动增长变量，那么复制到从库的同一张表后很可能会引起主键冲突，因为系统参数 auto_increment_increment和auto_increment_offset的默认值为1，这样多台主库的自增变量列迟早会发生冲突。在单主复制时，可以采用默认设置，不会有主键冲突发生。但是使用多主复制时，就需要定制auto_increment_increment 和 auto_increment_offset 的设置，保证多主之间复制到从数据库不会有重复冲突。比如，两个master的情况可以按照以下设置。
Master1上：auto_increment_increment = 2，auto_increment_offset = 1；（1,3,5,7…序列）。
Master2上：auto_increment_increment = 2，auto_increment_offset = 0；（0,2,4,6…序列）。
下面的例子中创建了测试表 ai，只有一个自增字段 i，我们开始演示修改这两个参数的效果。首先在参数是默认值时，往表ai中插入记录，可以看到自动增长列的值是连续的。
mysql> CREATE TABLE ai (
-> i bigint(20) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (i)
-> ) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.03 sec)
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> insert into ai values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from ai;
+---+
| i |
+---+
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)
然后把参数auto_increment_increment的值修改成10，再插入记录：
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.00 sec)
mysql> insert into ai values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from ai;
+----+
| i |
+----+
| 1 |
| 2 |
| 3 |
| 11 |
| 21 |
| 31 |
+----+
6 rows in set (0.00 sec)
从测试的结果上看，新插入的记录不再连续了，每次增加10。接着再修改auto_increment_offset参数，了解插入记录的效果：
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> insert into ai values(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from ai;
+----+
| i |
+----+
| 1 |
| 2 |
| 3 |
| 11 |
| 21 |
| 31 |
| 35 |
| 45 |
| 55 |
+----+
9 rows in set (0.00 sec)
从插入记录的结果上可以了解，auto_increment_offset参数设置的是每次增加后的偏移量，也就是每次按照10累加之后，还需要增加5个偏移量。
通过这两个参数可以方便地设置不同的主库上的自动增长列的值的范围，这样在这些数据复制到从库上时可以有效地避免主键的重复。