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)

3, 在master上insert 3条记录

mysql> insert into tmp_test_0208(name) values('a'),('b'),('c');
query ok, 3 rows affected (0.02 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)

4,  slave 的sql thread 中止

/usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e "show slave status\g" |egrep "slave_io_running|sl
ave_sql_running"
slave_io_running: yes
slave_sql_running: no

5,  skip next statemate后start slave正常

mysql> set global sql_slave_skip_counter = 1 ;
query ok, 0 rows affected (0.00 sec)

mysql> slave start;
query ok, 0 rows affected (0.00 sec)

/usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e "show slave status\g" |egrep "slave_io_running|sl
ave_sql_running"
slave_io_running: yes
slave_sql_running: yes

slave端errlog如下： 070208 16:07:59[error] slave: error 'duplicate entry '1' for key 1' on query. default database: 'c2cdb'. query: 'insert into tmp_te st_0208(name) values('a'),('b'),('c')', error_code: 1062

070208 16:07:59 [error] error running query, slave sql thread aborted. fix the problem, and restart the slave sql thread with "slave  start". we stopped at log 'db_auction1-bin.000203' position 14215101

070208 16:09:59 [note] slave sql thread initialized, starting replication in log 'db_auction1-bin.000203' at position 14215101, rela y log './db_auction1_b-relay-bin.000457' position: 200682931

 

master羰binlog中相应的记录如下：

# at 14215101 #070208 16:08:00 server id 1  log_pos 14215101  intvar set insert_id=1; # at 14215129 #070208 16:08:00 server id 1  log_pos 14215129  query   thread_id=2744782       exec_time=0     error_code=0 set timestamp=1170922080; insert into tmp_test_0208(name) values('a'),('b'),('c');

总结：使用set global sql_slave_skip_counter 命令跳过失败的sql