108.mysql8.0.34-GTID模式下主库取消长事务&从库遇到长事务夯死

戒掉贪嗔痴

已于 2024-01-17 21:30:51 修改

阅读量915

点赞数 7

CC 4.0 BY-SA版权

分类专栏：数据库运维-MySQL 文章标签：数据库 mysql

于 2024-01-17 11:21:26 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_43346403/article/details/135645425

数据库运维-MySQL 专栏收录该内容

207 篇文章

订阅专栏

文章讲述了在GTID模式下，当主库执行长时间未结束的DELETE事务并强制终止后，如何通过查看进程列表、停止从库并设置GTID_next来恢复从库同步。解决方法包括跳过异常事务的GTID、调整数据库结构以避免大事务，并在必要时人工干预。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.有GTID模式下从库不同步修复。

(1).主库执行了一个长事务DELETE,执行很久没有结束，主库手动终止了该SQL;
sql语句记录在binlog,并在从库执行。从库夯死。

(2)处理方法
show processlist; 
kill xxx; --杀掉DELETE大事务对应的SQL;

show processlist; 
| 442 | system user || echannel | Query| 84698 | Applying batch of row changes (delete) | delete from tbl_base_operate_log where create_time > '2023-12-07 00:00:00' and create_time < '2023-1...' |
kill 442;

show slave status\G; 
--找到:
Executed_Gtid_Set:215363766-217838840

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.1.1.1
                  Master_User: repl
                  Master_Port: 3300
                Connect_Retry: 60
              Master_Log_File: binlog.001178
          Read_Master_Log_Pos: 159849119
               Relay_Log_File: newb2b-prod-database-b2b-slave-relay-bin.000010
                Relay_Log_Pos: 24370701
        Relay_Master_Log_File: binlog.001164
             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: 1756
                   Last_Error: ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 24370531
              Relay_Log_Space: 23483303240
              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: 1756
               Last_SQL_Error: ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 23306
                  Master_UUID: fee84d96-076b-11ed-8228-005056bf9190
             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: 240116 15:59:17
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: fee84d96-076b-11ed-8228-005056bf9190:217765115-218611985
            Executed_Gtid_Set: 3f0f0583-58a0-11ec-beec-005056a3dd2d:1-6,
7a697ee5-86fd-11eb-b5e8-fa163e9bc0e8:1-77260748,
fee84d96-076b-11ed-8228-005056bf9190:1-185572825:185572827-185572837:185572839-189097549:189097551-189097557:189097559-189102963:189102965-215363764:215363766-217838840
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

在这个GTID的基础上加1;GTID_NEXT=217838840+1=217838841;

2.跳过该DELETE大事务对应的GTID

stop slave; 
SET GTID_NEXT='fee84d96-076b-11ed-8228-005056bf9190:217838841';
begin;commit;
SET GTID_NEXT="AUTOMATIC";
start slave;
show slave status\G

重新同步后正常。

3.总结

主库需要给表建立主键或者索引，或者如果表比较大时可以对其分区，防止主库执行大事务，并未结束，而SQL传输到从库，导致从库夯死的情况发送，如果有这类情况发送，需要人工处理该夯死问题。