gtid多源复制Last_Errno: 1007故障处理
在测试环境中，无意中遇到了Last_Errno: 1007。
这里直接上错误提示，以及处理方法。
mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.100.19.214 Master_User: mysqlsync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000002 Read_Master_Log_Pos: 196 Relay_Log_File: relay-log-testdb214.000005 Relay_Log_Pos: 377 Relay_Master_Log_File: mysql-binlog.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: test.% Last_Errno: 1007 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7b68143a-645e-11eb-b60f-fa163ea9cdf0:1' at master log mysql-binlog.000001, end_log_pos 356. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 156 Relay_Log_Space: 6721 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: 1007 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7b68143a-645e-11eb-b60f-fa163ea9cdf0:1' at master log mysql-binlog.000001, end_log_pos 356. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 10402 Master_UUID: 7b68143a-645e-11eb-b60f-fa163ea9cdf0 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: 210204 11:15:24 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-11 Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:12 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb214 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.100.19.215 Master_User: mysqlsync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000002 Read_Master_Log_Pos: 196 Relay_Log_File: relay-log-testdb215.000004 Relay_Log_Pos: 377 Relay_Master_Log_File: mysql-binlog.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: test.% Last_Errno: 1007 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '8d94b44b-645e-11eb-b232-fa163ee8385c:1' at master log mysql-binlog.000001, end_log_pos 356. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 156 Relay_Log_Space: 4876 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: 1007 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '8d94b44b-645e-11eb-b232-fa163ee8385c:1' at master log mysql-binlog.000001, end_log_pos 356. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 13502 Master_UUID: 8d94b44b-645e-11eb-b232-fa163ee8385c 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: 210204 11:15:24 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 8d94b44b-645e-11eb-b232-fa163ee8385c:1-7 Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:12 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb215 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 2 rows in set, 1 warning (0.01 sec) ERROR: No query specified mysql>
排查方法：
Relay_Master_Log_File: mysql-binlog.000001 # 非常重要
Exec_Master_Log_Pos: 156 # 非常重要
错误提示，不再像以前那么清晰明了，迫使我们用正确的方式来解决问题（即以主库为主，解析binlog日志，看看究竟是什么操作引起的异常，不再单单靠跳过错误来解决）
mysqlbinlog -vvv --base64-output=decode-rows --start-position=156 mysql-binlog.000001 >01.binlog
# at 156 #210203 11:15:57 server id 10402 end_log_pos 233 CRC32 0x5a0ed0f0 GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1612322157027088 immediate_commit_timestamp=1612322157027088 transaction_length=200 # original_commit_timestamp=1612322157027088 (2021-02-03 11:15:57.027088 CST) # immediate_commit_timestamp=1612322157027088 (2021-02-03 11:15:57.027088 CST) /*!80001 SET @@session.original_commit_timestamp=1612322157027088*//*!*/; /*!80014 SET @@session.original_server_version=80023*//*!*/; /*!80014 SET @@session.immediate_server_version=80023*//*!*/; SET @@SESSION.GTID_NEXT= '7b68143a-645e-11eb-b60f-fa163ea9cdf0:1'/*!*/; # at 233 #210203 11:15:57 server id 10402 end_log_pos 356 CRC32 0x2a437069 Query thread_id=59 exec_time=0 error_code=0 Xid = 580 SET TIMESTAMP=1612322157/*!*/; SET @@session.pseudo_thread_id=59/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113664/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80016 SET @@session.default_table_encryption=0*//*!*/; create database testdb214 ...... ......
# 解决办法 在从库中执行 set sql_log_bin=0; drop database testdb214; drop database testdb215; set sql_log_bin=1; stop replica; start replicat; show replica statusG; mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.08 sec) mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql> show replica statusG; *************************** 1. row *************************** Replica_IO_State: Waiting for master to send event Source_Host: 10.100.19.214 Source_User: mysqlsync Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-binlog.000002 Read_Source_Log_Pos: 196 Relay_Log_File: relay-log-testdb214.000008 Relay_Log_Pos: 417 Relay_Source_Log_File: mysql-binlog.000002 Replica_IO_Running: Yes Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 196 Relay_Log_Space: 791 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 10402 Source_UUID: 7b68143a-645e-11eb-b60f-fa163ea9cdf0 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-11 Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb214 Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: *************************** 2. row *************************** Replica_IO_State: Waiting for master to send event Source_Host: 10.100.19.215 Source_User: mysqlsync Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-binlog.000002 Read_Source_Log_Pos: 196 Relay_Log_File: relay-log-testdb215.000007 Relay_Log_Pos: 417 Relay_Source_Log_File: mysql-binlog.000002 Replica_IO_Running: Yes Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 196 Relay_Log_Space: 791 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 13502 Source_UUID: 8d94b44b-645e-11eb-b232-fa163ee8385c Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: 8d94b44b-645e-11eb-b232-fa163ee8385c:1-7 Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb215 Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 2 rows in set (0.00 sec) ERROR: No query specified mysql>