主库删除了ttt表，从库上没有ttt表，出现了报错。
[root@mysqlstu2:demo]10:49:52>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.55.97
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_log_bin.000005
Read_Master_Log_Pos: 683
Relay_Log_File: mysqlstu2-relay-bin.000002
Relay_Log_Pos: 410
Relay_Master_Log_File: mysql_log_bin.000005
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: 1051
Last_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE `ttt` /* generated by server */'
Skip_Counter: 0
Exec_Master_Log_Pos: 338
Relay_Log_Space: 958
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: 1051
Last_SQL_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE `ttt` /* generated by server */'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 97
Master_UUID: 9f7152cc-913d-11ea-b983-000c298beb69
Master_Info_File: mysql.slave_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: 200605 10:49:39
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9f7152cc-913d-11ea-b983-000c298beb69:15-16
Executed_Gtid_Set: 9f7152cc-913d-11ea-b983-000c298beb69:1-14
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
[root@mysqlstu2:demo]10:49:58>show tables; 从库没有ttt表
+----------------+
| Tables_in_demo |
+----------------+
| dd |
| tt |
+----------------+
2 rows in set (0.00 sec)
[root@mysqlstu2:demo]10:53:31>stop slave;
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:56:24>set gtid_next ='9f7152cc-913d-11ea-b983-000c298beb69:14';
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:56:57>begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:57:05>set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:57:24>start slave;
Query OK, 0 rows affected (0.01 sec)
这次操作失败了，gtid_next 指定错误，与Executed_Gtid_Set一样了，理解错了，这个应该是要跳过的事务。
[root@mysqlstu2:demo]10:57:29>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.55.97
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_log_bin.000005
Read_Master_Log_Pos: 683
Relay_Log_File: mysqlstu2-relay-bin.000002
Relay_Log_Pos: 410
Relay_Master_Log_File: mysql_log_bin.000005
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: 1051
Last_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE `ttt` /* generated by server */'
Skip_Counter: 0
Exec_Master_Log_Pos: 338
Relay_Log_Space: 1461
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: 1051
Last_SQL_Error: Error 'Unknown table 'demo.ttt'' on query. Default database: 'demo'. Query: 'DROP TABLE `ttt` /* generated by server */'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 97
Master_UUID: 9f7152cc-913d-11ea-b983-000c298beb69
Master_Info_File: mysql.slave_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: 200605 10:57:29
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9f7152cc-913d-11ea-b983-000c298beb69:15-16
Executed_Gtid_Set: 9f7152cc-913d-11ea-b983-000c298beb69:1-14
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
再次执行， gtid_next='9f7152cc-913d-11ea-b983-000c298beb69:15'，成功跳过指定事务。
[root@mysqlstu2:demo]10:57:36>stop slave;
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:57:58>set gtid_next='9f7152cc-913d-11ea-b983-000c298beb69:15';
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:58:44>begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:58:55>set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
[root@mysqlstu2:demo]10:59:17>start slave;
Query OK, 0 rows affected (0.01 sec)
[root@mysqlstu2:demo]10:59:25>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.55.97
Master_User: bak
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_log_bin.000005
Read_Master_Log_Pos: 683
Relay_Log_File: mysqlstu2-relay-bin.000004
Relay_Log_Pos: 450
Relay_Master_Log_File: mysql_log_bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 683
Relay_Log_Space: 953
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 97
Master_UUID: 9f7152cc-913d-11ea-b983-000c298beb69
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 9f7152cc-913d-11ea-b983-000c298beb69:15-16
Executed_Gtid_Set: 9f7152cc-913d-11ea-b983-000c298beb69:1-16
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：MYSQL GTID跳过指定事务 - Python技术站
微信扫一扫 
支付宝扫一扫 