Duplicate entry '1000094731-2' for key 'PRIMARY', Error_code: 1062;

1.版本

1)操作系统

 cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m

 cat /proc/version
Linux version 2.6.32-504.el6.x86_64 ([e-mail]) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2)mysql数据库版本

mysql --version
mysql  Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using  EditLine wrapper

2.问题描述

  问题是这样的，我发在2015-12-11 17:57:11 我的从库因为主键冲突，导致sql_thread停止运行了，报错如下：

2015-12-11 17:57:11 28231 [ERROR] Slave SQL: Could not execute Write_rows event on table dbname.tbname; Duplicate entry 'xxxxx' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 3307-bin.000015, end_log_pos 119060542, Error_code: 10622015-12-11 17:57:11 28231 [Warning] Slave: Duplicate entry 'xxxxxx' for key 'PRIMARY' Error_code: 10622015-12-11 17:57:11 28231 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log '3307-bin.000015' position 119059631

##注意，日志中的 数据库名我用 dbname替换了，tablename用tbname替换了，主键用xxxxxx替换了

##到上面为止，都只是一个简单因为从库主键冲突导致从库的sql_thread停止运行。下面看看我的疑问，在2015-12-11 17:57:11之后 我在errorlog中还看到了如下的报错：

2015-12-11 17:57:11 28231 [ERROR] Slave SQL: Could not execute Write_rows event on table dbname.tbname; Duplicate entry 'xxxxxx' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 3307-bin.000015, end_log_pos 119060542, Error_code: 10622015-12-11 17:57:11 28231 [Warning] Slave: Duplicate entry 'xxxxxx' for key 'PRIMARY' Error_code: 10622015-12-11 17:57:11 28231 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log '3307-bin.000015' position 1190596312015-12-12 04:03:13 28231 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 02015-12-12 04:03:13 28231 [Note] Slave SQL thread initialized, starting replication in log '3307-bin.000015' at position 119059631, relay log './3307-relay-bin.000041' position: 1190597932015-12-12 04:03:13 28231 [ERROR] Slave SQL: Could not execute Write_rows event on table dbname.tbname; Duplicate entry 'xxxxxx' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 3307-bin.000015, end_log_pos 119060542, Error_code: 10622015-12-12 04:03:13 28231 [Warning] Slave: Duplicate entry 'xxxxxx' for key 'PRIMARY' Error_code: 10622015-12-12 04:03:13 28231 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log '3307-bin.000015' position 1190596312015-12-13 04:03:21 28231 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 02015-12-13 04:03:21 28231 [Note] Slave SQL thread initialized, starting replication in log '3307-bin.000015' at position 119059631, relay log './3307-relay-bin.000041' position: 1190597932015-12-13 04:03:21 28231 [ERROR] Slave SQL: Could not execute Write_rows event on table dbname.tbname; Duplicate entry 'xxxxxx' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log 3307-bin.000015, end_log_pos 119060542, Error_code: 10622015-12-13 04:03:21 28231 [Warning] Slave: Duplicate entry 'xxxxxx' for key 'PRIMARY' Error_code: 10622015-12-13 04:03:21 28231 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log '3307-bin.000015' position 119059631

##我们看到在2015-12-11 17:57:11 (sql_thread线程已经停止了，并且也没有人工进行干预恢复)，每天的凌晨4点都又报了同样的主键冲突，这是为什么呢？

3.问题解决

  这个问题是因为我在从库部署了定时备份任务(备份脚本中加了--dump-slave参数)，因为备份脚本中有--dump-slave参数，所以在备份前会先stop slave sql_thread;备份完成后再执行 start slave; 就是因为再次执行start slave;所以我们看到 在每天的凌晨4点都报了 2015-12-11 17:57:11那个时候的主键冲突(因为这个主键冲突一直没有解决，所以每次start slave;都会报这个错误)

##关于--dump-slave 可以参考我的另一篇博客

http://blog.csdn.net/shaochenshuo/article/details/50312777