主从数据不一致导出同步错误pt-slave-restart(主库删除记录,从库不存在)
场景:
主库执行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)
跳过多个错误:
/opt/percona-toolkit-3.1.0/bin/pt-slave-restart --error-numbers=1062,1032 -h localhost -uroot -pyeemiao3040 -P3306 -S /home/mysql56/mysql.sock
需要安装相应的包
yum install perl-DBI
yum install perl-DBD-MySQL
yum -y install perl-Digest-MD5
同时可以指定端口和socket
/opt/percona-toolkit-3.1.0/bin/pt-slave-restart --error-numbers=1032 -h localhost -uroot -ptesttest -P13306 -S /tmp/mysql.sock
方法3:
--GTID模式
mysql> stop slave;
通过show slave status\G;
Retrieved_Gtid_Set: 9ecf61e2-eed1-11ed-a4b0-080027e81195:1-26 ##从库接收到主库的gtid,9ecf61e2-eed1-11ed-a4b0-080027e81195为主库的uuid
Executed_Gtid_Set: 636915ca-ef98-11ed-a854-080027962264:1-836,##从库自己的gitd,mysql_home/data/auto.cnf可以获取到数据库的uuid,该gtid在从库上有事务操作才会变化,正常同步下该值不变化
9ecf61e2-eed1-11ed-a4b0-080027e81195:1-21 ##从库已经执行的gtid,这个时候停在了gtid=21,已经接收到gtid=26的,但是因为错误的原因无法继续执行，跳过22(21+1)
mysql> set GTID_NEXT='9ecf61e2-eed1-11ed-a4b0-080027e81195:22'
mysql> begin;commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;
