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 status\G;
*************************** 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;
