The repair method of master-Slave duplicate key problem in Mysql _mysql

-------------------Quote begin------------------------3. If you decide so you can skip the next statement from the master, issue the following statements:mysql> SET GLOBAL S Ql_slave_skip_counter = n; Mysql> START SLAVE; The value of n should be 1 if the next statement from the master does not with Auto_increment or last_insert_id (). Otherwise, the value should be 2. The reason for using a value of of 2 for statements this use auto_increment or last_insert_id () are that they take events In the binary log of the master.

-------------------Quote End------------------------

The MySQL document means skip two event when master reaches slave's statement to use Auto_increment, or last_insert_id (). But that's not the case.

The test process is as follows: 172.16.161.26 for master 172.16.161.15 for slave sync c2cdb, initial state OK

1. Create a test table on master

Mysql> CREATE TABLE tmp_test_0208 (ID int NOT NULL auto_increment,name varchar (), 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:0 mysql>

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 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: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 abort

/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 after start slave normal

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

The Slave end ErrLog is as follows: 070208 16:07:59[error] slave:error ' Duplicate entry ' 1 ' for key 1 ' in 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 into log ' db_auction1-bin.000203 ' at position 1421 5101, rela y log './db_auction1_b-relay-bin.000457 ' position:200682931

The corresponding records in master carbonyl 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 ');

Summary: Using the Set GLOBAL sql_slave_skip_counter command skipping failed SQL