slave 报Duplicate entry for key

今天slave机器报如下错误

 [ERROR] Slave SQL: Error 'Duplicate entry '7301' for key 'PRIMARY'' on query. Default database: 'rt_roledb0301'. Query: 'insert into gm_proc_run_info (run_name, des, err_code) values ('pc_rank_of_day', 'begin', 0)', Error_code: 1062
111027  4:00:12 [Warning] Slave: Duplicate entry '7301' for key 'PRIMARY' Error_code: 1062
111027  4:00:12 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-binlog.000009' position 31542061

定位分析:在主从切换以后，由于没有关闭从上的计划任务

(root@localhost:)[(none)]> show global variables like 'event_sch%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)

导致 从服务器复制了一次主服务器计划任务的结果，又执行了一次相同的计划任务，导致了Duplicate entry报警。

解决方法:关闭从服务器上的计划任务，对从服务器进行数据恢复。

-------------------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. 但实际情况并非如此
测试过程如下:
192.168.1.1 为master
192.168.1.2 为slave
同步test，初始状态ok
1. 在master上创建测试表

mysql> create table tmp_test_0208(id int not nullauto_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如下：
130416 13:20:57 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into tmp_test_0208(name) values('a'),('b'),('c')', Error_code: 1062
130416 13:20:57 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062
130416 13:20:57 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.004707' position 39082343

 
master binlog中相应的记录如下：

SET INSERT_ID=1/*!*/;
# at 39082439
#130416 13:16:07 server id 1  end_log_pos 39082557      Query   thread_id=12589 exec_time=0     error_code=0
SET TIMESTAMP=1366089367/*!*/;
insert into tmp_test_0208(name) values('a'),('b'),('c')
/*!*/;
# at 39082557
#130416 13:16:07 server id 1  end_log_pos 39082584      Xid = 409512836
COMMIT/*!*/;

总结：使用SET GLOBAL SQL_SLAVE_SKIP_COUNTER 命令跳过失败的SQL