mysql slave duplicate entry_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