主从数据不一致导出同步错误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;