Mysql开启GTID后遇到错误跳过方法

来源：互联网发布：java线程池是什么编辑：程序博客网时间：2024/05/21 17:53

1、数据库版本

root@localhost:mysql.sock  08:06:29 [(none)]>select version();+------------+| version()  |+------------+| 5.6.37-log |+------------+1 row in set (0.00 sec)

2、问题产生

root@localhost:mysql.sock  08:09:04 [test]>show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.18.50                  Master_User: copy                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mybinlog.000001          Read_Master_Log_Pos: 3292               Relay_Log_File: mysql-relay-bin.000006                Relay_Log_Pos: 694        Relay_Master_Log_File: mybinlog.000001             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: 1146                   Last_Error: Error executing row event: 'Table 'test.hello' doesn't exist'                 Skip_Counter: 0          Exec_Master_Log_Pos: 3046              Relay_Log_Space: 2929              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1146               Last_SQL_Error: Error executing row event: 'Table 'test.hello' doesn't exist'  Replicate_Ignore_Server_Ids:              Master_Server_Id: 203306                  Master_UUID: 5385fd59-884a-11e7-90eb-080027eb4c97             Master_Info_File: /mydata/mysql/mysql_3306/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: 170828 07:58:47               Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-13            Executed_Gtid_Set: 5385fd59-884a-11e7-90eb-080027eb4c97:1-12                Auto_Position: 11 row in set (0.00 sec)

3、问题处理

如图可以看到当前从Master已经取到了日志13这个GTID的位置处，并且已经执行到了12这个事务处，但是在执行13的时候发生了错误。

在未开启GTID之前的处理方法：

mysql> stop slave；mysql> set global sql_slave_skip_counter=1;mysql> start slave;

但是现在不能这么处理了

处理方法如下：

一：跳过错误

mysql> STOP SLAVE;mysql> SET @@SESSION.GTID_NEXT= '5385fd59-884a-11e7-90eb-080027eb4c97:13‘;mysql> BEGIN; COMMIT;mysql> SET SESSION GTID_NEXT = AUTOMATIC;mysql> START SLAVE;

二：重置master跳过错误

手工调整SLAVE已清除的GTID列表GTID_PURGED,人为通知SLAVE哪些事务已经被清除了，后续可以忽略：

mysql> STOP SLAVE;mysql> RESET MASTER;mysql> SET @@GLOBAL.GTID_PURGED = '5385fd59-884a-11e7-90eb-080027eb4c97:1-13‘;mysql> START SLAVE;

三：使用pt-slave-restart工具(暂未测试)

# pt-slave-restart -S /var/lib/mysql/mysql.sock --error-numbers=test.hello --user=root --password='xxx'

阅读全文

0 0