gtid跳过错误的方法
gtid跳过错误的方法
作者:@Mr_Shrimp
转载本文请注明出处:https://www.cnblogs.com/5945yang/p/12365037.html
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