mysql主从1062主键冲突跳过错误
1062错误——主键冲突,出现这种情况就是从库出现插入操作,主库又插入相同的数据,iothread没问题,sqlthread出错
处理此种错误一般有两种思路:
1、直接跳过错误执行语句
2、找到错误执行语句,修复主库2数据https://www.cndba.cn/leo1990/article/2957https://www.cndba.cn/leo1990/article/2957
https://www.cndba.cn/leo1990/article/2957
https://www.cndba.cn/leo1990/article/2957https://www.cndba.cn/leo1990/article/2957
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.test; Duplicate entry ‘2’ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql2-bin.000001, end_log_pos 2799
Skip_Counter: 0
—传统模式
mysql> stop slave;
#表示跳过一步错误,后面的数字可变,(或者N条event,一条一条跳过)
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;
之后再用mysql> show slave status/G 查看:
mysql> show slave status/G;/
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.21
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql2-bin.000001
Read_Master_Log_Pos: 3087
Relay_Log_File: cndba-relay-bin.000006
Relay_Log_Pos: 587
Relay_Master_Log_File: mysql2-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
#还有一种方法跳过所有1062错误
更改my.cnf文件,在Replication settings下添加:
slave-skip-errors = 1062
并重启数据库,然后start salve。
注意:因为要重启数据库,不推荐,除非错误事件太多。