gtid多源复制1032故障处理
# 此时，从库异常如下 mysql> show replica status for channel 'testdb214'G; *************************** 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.000001 Read_Source_Log_Pos: 4011 Relay_Log_File: relay-log-testdb214.000002 Relay_Log_Pos: 3782 Relay_Source_Log_File: mysql-binlog.000001 # 非常重要 Replica_IO_Running: Yes Replica_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: 1032 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:8' at master log mysql-binlog.000001, end_log_pos 3980. 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_Source_Log_Pos: 3561 # 非常重要 Relay_Log_Space: 4445 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: NULL Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1032 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:8' at master log mysql-binlog.000001, end_log_pos 3980. 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: 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: Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 210203 11:22:48 Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-8 Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-7, 8d94b44b-645e-11eb-b232-fa163ee8385c:1-7 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb214 Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified mysql>
# 解决办法： Relay_Source_Log_File: mysql-binlog.000001 # 非常重要 Exec_Source_Log_Pos: 3561 # 非常重要 根据这个，解析主库的binlog日志 mysqlbinlog -vvv --base64-output=decode-rows --start-position=3561 mysql-binlog.000001 >01.binlog # at 3561 #210203 11:22:48 server id 10402 end_log_pos 3640 CRC32 0xdd0e2087 GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=1612322568475699 immediate_commit_timestamp=1612322568475699 transaction_length=450 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1612322568475699 (2021-02-03 11:22:48.475699 CST) # immediate_commit_timestamp=1612322568475699 (2021-02-03 11:22:48.475699 CST) /*!80001 SET @@session.original_commit_timestamp=1612322568475699*//*!*/; /*!80014 SET @@session.original_server_version=80023*//*!*/; /*!80014 SET @@session.immediate_server_version=80023*//*!*/; SET @@SESSION.GTID_NEXT= '7b68143a-645e-11eb-b60f-fa163ea9cdf0:8'/*!*/; # at 3640 #210203 11:22:48 server id 10402 end_log_pos 3720 CRC32 0x78dad65a Query thread_id=59 exec_time=0 error_code=0 SET TIMESTAMP=1612322568/*!*/; 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*//*!*/; BEGIN /*!*/; # at 3720 #210203 11:22:48 server id 10402 end_log_pos 3779 CRC32 0xa93fce99 Rows_query # delete from info_area where level=3 # at 3779 #210203 11:22:48 server id 10402 end_log_pos 3852 CRC32 0xd2e13c57 Table_map: `testdb214`.`info_area` mapped to number 311 # at 3852 #210203 11:22:48 server id 10402 end_log_pos 3980 CRC32 0xb41b0afc Delete_rows: table id 311 flags: STMT_END_F ### DELETE FROM `testdb214`.`info_area` ### WHERE ### @1=10001 /* INT meta=0 nullable=0 is_null=0 */ ### @2='三环以内' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */ ### @3='100001000110001' /* VARSTRING(200) meta=200 nullable=0 is_null=0 */ ### @4=10001 /* INT meta=0 nullable=0 is_null=0 */ ### @5=3 /* INT meta=0 nullable=0 is_null=0 */ ### DELETE FROM `testdb214`.`info_area` ### WHERE ### @1=10003 /* INT meta=0 nullable=0 is_null=0 */ ### @2='三环到四环之间' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */ ### @3='100001000110003' /* VARSTRING(200) meta=200 nullable=0 is_null=0 */ ### @4=10003 /* INT meta=0 nullable=0 is_null=0 */ ### @5=3 /* INT meta=0 nullable=0 is_null=0 */ # at 3980 #210203 11:22:48 server id 10402 end_log_pos 4011 CRC32 0xf049e13f Xid = 592 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; root@k8s-02-19-214-bigdata-test /data/mysql/mysql_3306/logs eth0 10.100.19.214 # 根据以上查询到是删除两条记录，而从库已经没有这些数据，所以可以注入空事务，跳过即可，或者插入数据也可以。也可以补齐数据
方法一：在从库中补齐数据： set sql_log_bin=0; insert into `testdb214`.`info_area` values (10001,'三环以内','100001000110001',10001,3); insert into `testdb214`.`info_area` values (10003,'三环到四环之间','100001000110003',10003,3); set sql_log_bin=1; start replica for channel 'testdb214'; show replica status for channel 'testdb214'G;
方法2二：跳过错误，注入空事务 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.000001 Read_Master_Log_Pos: 5389 Relay_Log_File: relay-log-testdb214.000002 Relay_Log_Pos: 5160 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: 1032 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:11' at master log mysql-binlog.000001, end_log_pos 5358. 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: 4939 Relay_Log_Space: 5823 ...... ...... mysqlbinlog -vvv --base64-output=decode-rows --start-position=4939 mysql-binlog.000001 >01.binlog # at 4939 #210203 14:03:05 server id 10402 end_log_pos 5018 CRC32 0xc396cee9 GTID last_committed=10 sequence_number=11 rbr_only=yes original_committed_timestamp=1612332185740111 immediate_commit_timestamp=1612332185740111 transaction_length=450 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1612332185740111 (2021-02-03 14:03:05.740111 CST) # immediate_commit_timestamp=1612332185740111 (2021-02-03 14:03:05.740111 CST) /*!80001 SET @@session.original_commit_timestamp=1612332185740111*//*!*/; /*!80014 SET @@session.original_server_version=80023*//*!*/; /*!80014 SET @@session.immediate_server_version=80023*//*!*/; SET @@SESSION.GTID_NEXT= '7b68143a-645e-11eb-b60f-fa163ea9cdf0:11'/*!*/; # at 5018 #210203 14:03:05 server id 10402 end_log_pos 5098 CRC32 0x0800772d Query thread_id=74 exec_time=0 error_code=0 SET TIMESTAMP=1612332185/*!*/; SET @@session.pseudo_thread_id=74/*!*/; 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*//*!*/; BEGIN /*!*/; # at 5098 #210203 14:03:05 server id 10402 end_log_pos 5157 CRC32 0x0296bade Rows_query # delete from info_area where level=3 # at 5157 #210203 14:03:05 server id 10402 end_log_pos 5230 CRC32 0xfe8de135 Table_map: `testdb214`.`info_area` mapped to number 311 # at 5230 #210203 14:03:05 server id 10402 end_log_pos 5358 CRC32 0x2a2133a9 Delete_rows: table id 311 flags: STMT_END_F ### DELETE FROM `testdb214`.`info_area` ### WHERE ### @1=10001 /* INT meta=0 nullable=0 is_null=0 */ ### @2='三环以内' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */ ### @3='100001000110001' /* VARSTRING(200) meta=200 nullable=0 is_null=0 */ ### @4=10001 /* INT meta=0 nullable=0 is_null=0 */ ### @5=3 /* INT meta=0 nullable=0 is_null=0 */ ### DELETE FROM `testdb214`.`info_area` ### WHERE ### @1=10003 /* INT meta=0 nullable=0 is_null=0 */ ### @2='三环到四环之间' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */ ### @3='100001000110003' /* VARSTRING(200) meta=200 nullable=0 is_null=0 */ ### @4=10003 /* INT meta=0 nullable=0 is_null=0 */ ### @5=3 /* INT meta=0 nullable=0 is_null=0 */ # at 5358 #210203 14:03:05 server id 10402 end_log_pos 5389 CRC32 0x9d447670 Xid = 600 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
########## 单同步进程处理方式 #(1)停止slave进程 mysql> STOP replica; #(2)设置事务号，事务号从Retrieved_Gtid_Set获取 #在session里设置gtid_next，即跳过这个GTID SET @@SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810a-0050568833c8:4'; #(3)注入空事物 BEGIN; COMMIT; #(4)恢复事物号 SET SESSION GTID_NEXT = AUTOMATIC; #(5)启动slave进程 START replica;
########## 多源同步进程处理方式 stop replica for channel 'testdb214'; SET @@SESSION.GTID_NEXT= '7b68143a-645e-11eb-b60f-fa163ea9cdf0:11'/*!*/; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC; start replica for channel 'testdb214'; show replica status for channel 'testdb214'G;