分支的博客通过本文主要向大家介绍了mysql gtid,mysql 5.6 gtid 主从,mysql5.6 gtid,mysql 5.7 gtid,mysql gtid复制等相关知识,希望本文的分享对您有所帮助
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: NULL
Master_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: 1
1 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:13‘; mysql> START SLAVE;
三：使用pt-slave-restart工具(暂未测试)
# pt-slave-restart -S /var/lib/mysql/mysql.sock --error-numbers=test.hello --user=root --password='xxx'