mysql gitd 跳过错误_gtid跳过错误的方法

gtid跳过错误的方法

在show slave status \G中

Retrieved_Gtid_Set: 59222b34-04f6-11ea-8e77-02000aba408a:209

Executed_Gtid_Set: 2e8ed7a5-050f-11ea-84a0-02000aba4088:1-33,

4b5cabd9-04f6-11ea-8c8e-02000aba4089:36-37:41:49,

59222b34-04f6-11ea-8e77-02000aba408a:200-201

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: mastester-02

Master_TLS_Version:

2 rows in set (0.00 sec)

可见从201到209这九个事务出错，如何跳过？一次跳一个

解决办法一

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set session gtid_next= '59222b34-04f6-11ea-8e77-02000aba408a:201';

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> set session gtid_next=automatic;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G

或者

解决办法二

mysql>  STOP SLAVE;

mysql>RESET MASTER;

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

mysql>  START SLAVE;

上面这些命令的用意是，忽略8f9e146f-0a18-11e7-810a-0050568833c8:1-4 这个GTID事务，下一次事务接着从5这个GTID开始，即可跳过上述错误。

解决办法三：使用pt-slave-restart工具

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

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