How to make gtid copy skip errors in mysql

Check the gtid of the current execution error on the slave machine

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.77
                  Master_User: lixiaomeng
                  Master_Port: 6000
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 334
               Relay_Log_File: db2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'dba.test'' on query. Default database: 'dba'. Query: 'DROP TABLE `test` /* generated by server */'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 752
              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: 1051
               Last_SQL_Error: Error 'Unknown table 'dba.test'' on query. Default database: 'dba'. Query: 'DROP TABLE `test` /* generated by server */'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1077
                  Master_UUID: 3ad791d8-93a4-11e7-bc8f-00163e088e93
             Master_Info_File: /data0/mysql/6000/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: 170907 17:38:15
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3ad791d8-93a4-11e7-bc8f-00163e088e93:1  ------------------------这一行为执行出错的gtid
            Executed_Gtid_Set: 6b344a45-93a8-11e7-9c7c-00163e08914e:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

##Solution one:

(1) Stop the slave process

mysql> STOP SLAVE;
(2)设置事务号，事务号从Retrieved_Gtid_Set获取
在session里设置gtid_next，即跳过这个GTID
mysql> SET @@SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810a-0050568833c8:4'
（3）设置空事物
mysql> BEGIN; COMMIT;
（4）恢复事物号
mysql> SET SESSION GTID_NEXT = AUTOMATIC;
(5)启动slave进程
mysql> START SLAVE;

Solution 2: Reset the master method to skip the error

mysql>  STOP SLAVE;
mysql> RESET MASTER;
mysql>   SET @@GLOBAL.GTID_PURGED ='8f9e146f-0a18-11e7-810a-0050568833c8:1-4'
mysql>  START SLAVE;

The purpose of these commands is to ignore 8f9e146f-0a18-11e7-810a-0050568833c8: For the GTID transaction 1-4, the next transaction will start from the GTID 5, and the above error can be skipped.

Solution three: Use pt-slave-restart tool

pt-slave-restart工具的作用是监视某些特定的复制错误，然后忽略，并且再次启动SLAVE进程（Watch and restart MySQL replication after errors）。
忽略所有1062错误，并再次启动SLAVE进程
[root@dgt mysql]# pt-slave-resetart -S /var/lib/mysql/mysql.sock —error-numbers=1062
检查到错误信息只要包含 test.t1，就一概忽略，并再次启动 SLAVE 进程
[root@dgt mysql]# pt-slave-resetart -S /var/lib/mysql/mysql.sock  —error-text=”test.t1”
下面举例解决错误问题号
            Last_SQL_Error: Could not execute Delete_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000028, end_log_pos 1862
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 8f9e146f-0a18-11e7-810a-0050568833c8
             Master_Info_File: /var/lib/mysql/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: 170421 17:45:11
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8f9e146f-0a18-11e7-810a-0050568833c8:1-7
            Executed_Gtid_Set: 8f9e146f-0a18-11e7-810a-0050568833c8:1-6,
f7c86e19-24fe-11e7-a66c-005056884f03:1
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
[root@dgt mysql]# pt-slave-restart  -S  /var/lib/mysql/mysql.sock   --error-numbers=1032   --user=root --password='bc.123456'
2017-04-21T17:53:27 S=/var/lib/mysql/mysql.sock,p=...,u=root mysql-bin.000015         620 1032 
2017-04-21T17:54:31 S=/var/lib/mysql/mysql.sock,p=...,u=root mysql-bin.000015        1140 1032

Parameter explanation:

--slave-password=s         Sets the password to be used to connect to the slaves
--slave-user=s         Sets the user to be used to connect to the slaves
--sleep=i            Initial sleep seconds between checking the slave ( default 1)
--socket=s             -S  Socket file to use for connection=
--password=s             -p  Password to use when connecting
pt-slave-resetart             -S./mysql.sock —error-numbers=1032
--error-numbers=h         Only restart this comma-separated list of errors
--host=s             -h  Connect to host
--user=s             -u  User for login if not current user

The above is the detailed content of How to make gtid copy skip errors in mysql. For more information, please follow other related articles on the PHP Chinese website!