MySQL中主从复制重复键问题修复方法

-------------------quote begin------------------------ 3. If you decide that you can skip the next statement from the master, issue the following statements: mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; mysql> START SLAVE; The value of n should be 1 if the next statement from the master does not use AUTO_INCREMENT or LAST_INSERT_ID(). Otherwise, the value should be 2. The reason for using a value of 2 for statements that use AUTO_INCREMENT or LAST_INSERT_ID() is that they take two events in the binary log of the master.

-------------------quote end------------------------

MySQL文档中的意思是当master传到slave的语句中要用到auto_increment,或者last_insert_id()时,需要skip两个event. 但实际情况并非如此

测试过程如下: 172.16.161.26 为master 172.16.161.15 为slave 同步c2cdb，初始状态ok

1. 在master上创建测试表

mysql> create table tmp_test_0208(id int not null auto_increment,name varchar(30),primary key(id)) engine=innodb; Query OK, 0 rows affected (0.20 sec)

2, 在salve上insert 3条记录

mysql> insert into tmp_test_0208 values(1,'a'),(2,'b'),(3,'c'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tmp_test_0208; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+ 3 rows in set (0.00 sec)