How to fix master-slave duplicate keys in MySQL _ MySQL

This article describes how to fix the master-slave duplicate key issue in MySQL. For more information, see --------------------- 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 shoshould be 1 if the next statement from the master does not use AUTO_INCREMENT or LAST_INSERT_ID (). otherwise, the value shocould 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 ------------------------

In the MySQL document, auto_increment or last_insert_id () is used when the master uploads data to slave. However, this is not the case in the actual situation.

The test process is as follows: 172.16.161.26 is master 172.16.161.15 is slave, c2cdb is synchronized, and the initial status is OK.

1. create a test table on the 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. insert 3 records on salve

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: 0mysql> select * from tmp_test_0208;+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c |+----+------+3 rows in set (0.00 sec)

3. insert 3 records on the master

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

4. the slave SQL thread is suspended.

/usr/local/mysql/bin/mysql -uroot -pxxx c2cdb -s -e "show slave status\G" |egrep "Slave_IO_Running|Slave_SQL_Running"Slave_IO_Running: YesSlave_SQL_Running: No

5. start slave normally after skip next statemate

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|Slave_SQL_Running"Slave_IO_Running: YesSlave_SQL_Running: Yes

The slave errlog is as follows: 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

The corresponding records in the master slave binlog are as follows:

# 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 ');

Conclusion: run the set global SQL _SLAVE_SKIP_COUNTER command to skip the failed SQL statement.