场景:
主库执行delete语句，但是发现从库对应的记录不存在.
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.22
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000087
Read_Master_Log_Pos: 17094375
Relay_Log_File: relay-bin.000016
Relay_Log_Pos: 484882469
Relay_Master_Log_File: binlog.000086
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table db_yeemiao.user_login_log; Can't find record in 'user_login_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000086, end_log_pos 1434043809
Skip_Counter: 0
Exec_Master_Log_Pos: 1052073531
Relay_Log_Space: 1018361496
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table db_test.user_login_log; Can't find record in 'user_login_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000086, end_log_pos 1434043809
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c95fcd3a-98ae-11e5-b124-5820b106a87c
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200829 15:39:32
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: c95fcd3a-98ae-11e5-b124-5820b106a87c:26487093-28041213
Executed_Gtid_Set: 68a1789e-1f86-11e9-b06d-9cb654b262cc:1,
c95fcd3a-98ae-11e5-b124-5820b106a87c:1-26487091:26487093-28014911
Auto_Position: 0
1 row in set (0.00 sec)
主库上查看binlog日志
mysqlbinlog --base64-output=decode-rows -v --start-position=1052073531 /home/mysql/binlog/binlog.000086>/tmp/aa.txt
针对从库记录不存在的处理办法,可以直接跳过:
方法1(从库操作):
stop slave;
set global sql_slave_skip_counter=1;
start slave;
方法2(从库操作,使用工具):
[root@dev-env23 bin]# /opt/percona-toolkit-3.1.0/bin/pt-slave-restart --error-numbers=1032 -h localhost -uroot -ptest3040
2020-08-31T09:54:38 h=localhost,p=...,u=root relay-bin.000016 484882469 1032
Not checking slave because relay log file or position has not changed (file relay-bin.000016 pos 484882469)
方法3:
--GTID模式
mysql> stop slave;
通过show slave statusG;
找到Retrieved_Gtid_Set:7800a22c-95ae-11e4-983d-080027de205a:10
mysql> set GTID_NEXT='7800a22c-95ae-11e4-983d-080027de205a:10'
mysql> begin;commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;