gtid多源复制1032故障处理

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;

 

posted @   davie2020  阅读(448)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理 了，记录一下
点击右上角即可分享
微信分享提示