mysql中使 gtid 复制跳过错误的方法

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;

mysql>  STOP SLAVE;

mysql> RESET MASTER;

mysql>   SET @@GLOBAL.GTID_PURGED ='8f9e146f-0a18-11e7-810a-0050568833c8:1-4'

mysql>  START SLAVE;

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

--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