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

原创 已于 2024-01-17 21:30:51 修改 · 982 阅读 · 11 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #mysql

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

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传输到从库，导致从库夯死的情况发送，如果有这类情况发送，需要人工处理该夯死问题。

Mysql8.4参考手册走读（六）
庄的博客
07-17 1145
若要使用复制作为备份解决方案，请从源到副本进行操作，然后备份副本。副本可以暂停和关闭，不影响源，因此您可以生成“实时”数据的有效快照，否则将需要源被关闭。备份数据库的方式取决于数据库的大小以及是否仅备份数据，或在发生故障时可以重建副本的备份数据和副本状态。因此，有两种选择：如果使用复制作为解决方案，使您能够备份源上的数据以及数据库的大小不是太大，mysqldump工具可能合适。请参见第19.4.1.1节“使用mysqldump备份副本”。
MySQL 8.0 高可用之基于GTID的主从复制
个人公众号：Alen的数据库学习笔记
12-26 1726
作者公众号，欢迎一起交流。 GTID，全局事务唯一识别符，当启用GTID时，每个事务在原始数据库服务器提交时都会生成一个唯一的识别符，用于识别、追踪该事务。基于GTID的主从复制在配置时，不需要引用bilog的位点和文件名称，将会极大的简化复制环境的配置。下面将配置一个全新的基于GTID的主从复制环境，主、从库数据库安装初始化参照MySQL 8.0数据库软件安装部署。 1 启用GTID 配置基于GTID的复制环境时，需启动GTID模式，主、从库在配置文件/etc/my.cnf增加以下选项即可启用.
主从复制中定位回放慢涉及的表
GreatSQL2021的博客
02-17 446
主从架构下的性能问题，突发延迟且持续增长，经排查是 DELETE 事务回放缓慢所致。利用SHOW PROCESSLIST等命令，可定位到回放慢涉及的表，多为无索引且数据量庞大的表，这使得从库回放效率低下
一则 MySQL 从节点 hung 死问题分析
ActionTech的博客
04-07 1126
综合以上分析过程，导致此次故障的根本原因还是在于数据库的 Redo 配置参数过小，在问题时段从节点的压力下，Redo 的使用率过高，导致 InnoDB 无法完成检查点。并进一步导致从节点的 worker 线程在执行事务时，检查 Redo Log 是否存在有剩余 Log 文件时，而发生等待。当前一个 worker 线程执行事务挂起后，由于从节点采用 MTS，且，因此其它 worker 线程需要等待之前的事务提交，最终导致所有 worker 线程挂起。
MySQL8.0真正的并行复制writeset
不积跬步，无以至千里；不积小流，无以成江海。
03-09 1858
MySQL的主从架构依赖于 MySQL Binlog 功能， Master节点上产生Binlog并将Binlog写入到Binlog文件中。Slave节点上启动两个线程：一个IO线程，从MySQL上捞取Binlog日志并写入到本地的RelayLog日志；另一个SQL线程，不断的从RelayLog日志中读取日志，并解析执行。这样通过在主机和从机上增加几个文件的顺序读写操作，就可以保证所有在主机上执行过的SQL语句都在从机上一摸一样的执行过一遍。
MySQL-5.6版本GTID的主从复制
weixin_30634661的博客
01-23 576
mysql GTID Replication 一、GTID的概述： 1、全局事物标识：global transaction identifieds。 2、GTID事物是全局唯一性的，且一个事务对应一个GTID。 3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。 4、GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用m...
MySQL主从复制中断，跳过指定事务恢复
qq_34595089的博客
03-03 1871
MySQL主从复制中断，绝大多数的情况是SQL线程应用错误。有时我们会选择跳过这个事务来恢复主从复制，事后再分析主从复制中断的原因。
MySQL GTID模式主从同步配置全指南：从配置到故障转移
洛笙的博客
04-22 900
MySQL主从复制是企业级数据库架构的基础，而GTID(Global Transaction Identifier)模式则是MySQL 5.6版本后推出的革命性复制技术。本文将详细介绍如何配置基于GTID的主从同步，并包含实用的故障转移操作指南。GTID模式极大简化了MySQL主从复制的管理和维护工作。通过本文的配置指南，您可以快速搭建高可用的MySQL复制环境。实际生产环境中，建议结合MHA或Orchestrator等工具实现自动故障转移。最佳实践提示变更前务必备份数据生产环境建议先在测试环境验证。
记录一下，MySQL8.0 多主一从配置且多主互通
qq_36638086的博客
10-15 1226
记录一下，MySQL8.0 多主一从配置且多主互通的详细过程
mysql8.0 docker 搭建基于GTID主从复制并设置 从库只读模块
seowen的开发 小本子
12-21 737
1、创建 docker 容器 ######主库##### docker run -dit \ --net hadoop-master-net \ --ip 192.168.0.202 \ --restart always \ --privileged=true \ --hostname hadoop-master-mysql_13311 \ --name hadoop-master-mysql_13311 \ -v /home/docker/software/mysql-13311/conf/:/etc
MySQL8.0利用XtraBackup 搭建从库详细过程
老苏畅谈运维
05-26 1406
一、背景如何基于 XtraBackup 搭建从库？整个过程其实比较简单，无非是备份还原。唯一需要注意的是建立复制时位置点的选择，包括：在基于位置点的复制中，CHANGE MASTER TO 语句中 MASTER_LOG_FILE 和 MASTER_LOG_POS 的选择。在GTID 复制中，在执行CHANGE MASTER TO 命令之前，必须首先设置 GTID_PURGED。
MySQL8.0基于GTID主从复制
Hannah_zh的博客
05-26 1363
一、MySQL8.0基于GTID主从复制 注意： MySQL8.0之前版本的mysql密码加密方式为mysql_native_password，而MySQL8.0默认新添加的用户密码默认使用的caching_sha2_password，因此进行使用主从复制时可能会遇到错误：“error :Authentication plugin ’ caching_sha2_password’ reported error: Authentication requires secure connection.”。 解决
MySQL深入学习（三八）：主从复制
学习记录和总结
05-03 1447
主从复制：主从复制概述、主从复制的原理、准备工作、同步数据一致性问题、知识延伸
mysql 复制 错误,FAQ系列 | 从MySQL 5.6到5.7复制错误解决
weixin_33891841的博客
03-17 422
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event…Last_Errno: 1756Last_Error: … The slave coordinator and worker threads are stopped, possi...
从库mysqldump会导致复制中断
qq_40907977的博客
08-24 682
背景 环境 数据库版本：5.6.24-log 主从架构：一主两从搭配mha 操作步骤 开发要求truncate一张表，在删除之前需要备份下数据； 在从库上使用mysqldump命令做逻辑备份，备份sql： mysqldump -uXXX -p osdc osdc_XXX &gt; /tmp/osdc_info.sql； 这台从库与主库的同步出现中断，报错为： Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1756 Last_Er
mysql 复制 错误提示_FAQ系列 | 从MySQL 5.6到5.7复制错误解决
weixin_35824356的博客
01-27 551
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event...Last_Errno: 1756Last_Error: ... The slave coordinator and worker threads are stopped, p...
MySQL 5.7和MySQL 8.0的4个细节差异
杨建荣的学习笔记
12-30 2235
这是学习笔记的第2302篇文章 在这些年的MySQL升级需求中，让我大跌眼镜的一个现象是：驱动业务从MySQL 5.5升级到MySQL 5.7的很大一个因素是因为JSON这个特性...
MySQL主从复制异常.md
weixin_33998125的博客
09-04 514
一、MySQL 5.6 &gt;&gt; MySQL 5.7错误信息： Last_Error: ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatical...
当一个mysql主从复制失败很久，比如从库gtid_executed:1-50,而主库的gtid_purged:1-200,gtid_executed:1-500,我从主库导出带有事务id1-500的全量数据后，因为从库已经有1-50的事务id而无法导入，我能够把从库的gtid_purged清除，然后临时禁用binlog以防止从库自身的插入记录，然后导入主库的全量数据，恢复binlog，重做主从同步吗？在我导入全量数据后，从库的gtid_purged和gtid_executed是不是会自动记录为导出全量数据的事务id集合？
最新发布
06-21
&lt;think&gt;我们正在解决MySQL主从复制中因GTID不一致导致的冲突问题，特别是gtid_purged和gtid_executed冲突的情况。用户希望了解如何通过导入全量数据并重置GTID来重新建立主从同步。根据引用内容，我们知道：-gtid_purged表示已从binlog中清除的GTID集合（引用[1]）-gtid_executed表示已执行的GTID集合（引用[2]）-在备份恢复中设置gtid_purged需要注意行为变化（引用[3]）-物理备份（如xtrabackup）恢复后需要设置gtid_purged（引用[4]）步骤概述：1.停止从库复制，并重置从库状态（清除现有数据）。2.从主库获取全量备份（注意GTID设置）。3.在从库上导入备份，并设置gtid_purged为主库备份时的GTID集合（即主库的gtid_executed）。4.确保从库的gtid_purged是主库gtid_purged的子集（或等于），这样主库上未清除的binlog包含从库缺失的事务。5.重新配置主从复制，启用GTID自动定位。详细步骤：步骤1：停止并重置从库```sqlSTOPSLAVE;RESETSLAVEALL;--清除所有复制信息```步骤2：获取主库全量备份使用mysqldump或xtrabackup。这里以mysqldump为例（注意：生产环境大库建议用xtrabackup物理备份）：```bashmysqldump-h主库IP-u用户-p密码--single-transaction--master-data=2--set-gtid-purged=ON--all-databases&gt;full_backup.sql```参数说明：--single-transaction:保证备份一致性--master-data=2:记录主库的binlog位置（以注释形式存在），但GTID模式下我们主要关注GTID--set-gtid-purged=ON:在备份文件中包含SET@@GLOBAL.GTID_PURGED语句，该语句设置从库的gtid_purged为主库备份时的gtid_executed（引用[4]）步骤3：在从库上导入备份在导入前，我们需要确保从库没有执行过任何事务（即gtid_executed为空），否则设置gtid_purged会报错。因此，在导入前建议重置从库的GTID状态（注意：重置GTID会清空现有GTID集合，只能在无数据或可重置的情况下使用）。方法一：如果从库是全新的，或者可以清空所有数据，则直接导入：```bashmysql-h从库IP-u用户-p密码&lt;full_backup.sql```导入后，从库的gtid_purged会被设置为备份文件中的GTID集合（即主库备份时的gtid_executed）。方法二：如果从库已有数据（需要重置GTID），则必须先清空数据并重置GTID：```sqlRESETMASTER;--清空本地的gtid_executed和gtid_purged，注意：这会清除所有GTID信息，只能在从库无数据或数据可丢弃时使用```然后再导入备份。步骤4：检查GTID状态导入后，查看从库的GTID状态：```sqlSHOWGLOBALVARIABLESLIKE&#39;gtid_purged&#39;;--应该等于主库备份时的gtid_executedSHOWGLOBALVARIABLESLIKE&#39;gtid_executed&#39;;--应该和gtid_purged相同，因为导入备份后还没有新事务```步骤5：重新配置主从复制```sqlCHANGEMASTERTOMASTER_HOST=&#39;主库IP&#39;,MASTER_USER=&#39;复制用户&#39;,MASTER_PASSWORD=&#39;密码&#39;,MASTER_AUTO_POSITION=1;STARTSLAVE;```步骤6：验证复制状态```sqlSHOWSLAVESTATUS\G```检查：Slave_IO_Running:YesSlave_SQL_Running:YesRetrieved_Gtid_Set:正在从主库获取的GTID集合Executed_Gtid_Set:从库已执行的GTID集合（应该包含主库的gtid_executed和后续新事务）确保没有错误。注意事项：1.如果主库的gtid_purged（即已清除的GTID集合）不是从库gtid_executed（即导入后从库的gtid_purged）的子集，那么复制会失败（因为主库缺少从库需要的事务）。因此，在备份后，主库不能清除包含备份GTID集合的binlog，直到从库追上复制。2.使用物理备份（如xtrabackup）时，备份中会包含一个xtrabackup_binlog_info文件，记录备份时的GTID集合。在恢复后，我们需要手动设置gtid_purged：```sqlSETGLOBALgtid_purged=&#39;备份的GTID集合&#39;;```同样，在设置前必须确保从库的gtid_executed为空（可通过RESETMASTER实现，但注意：RESETMASTER会删除所有二进制日志和GTID信息，仅适用于全新从库或数据已清空的情况）。3.在MySQL5.7和8.0中，如果从库的gtid_executed不为空，则设置gtid_purged会报错。因此，我们必须在导入前重置GTID（使用RESETMASTER）或者确保从库是干净的。4.如果无法重置从库（例如从库还有其他数据），则不能使用RESETMASTER。这时可以考虑先导出需要保留的数据，然后重置从库，再导入备份和保留的数据。但这种情况比较复杂，一般建议重建从库时清空所有数据。5.禁用binlog导入：如果从库在导入备份时不想产生binlog（避免影响其他从库），可以在导入时临时禁用binlog：```sqlSETsql_log_bin=0;sourcefull_backup.sql;SETsql_log_bin=1;```或者在配置文件中设置skip-log-bin（需要重启），但通常临时禁用会话级的binlog即可。6.在备份主库时，确保备份期间主库的GTID集合不会变化（使用--single-transaction）并且备份后主库的binlog要保留足够长时间，直到从库追上。通过以上步骤，我们可以解决GTID冲突问题，并重新建立主从复制。&lt;/think&gt;### MySQL主从复制GTID冲突解决与数据同步重置指南 当主从库的`gtid_purged`和`gtid_executed`发生冲突时，会导致复制中断（错误1236）。以下是完整解决方案，分为7个关键步骤： --- ### 1. **问题诊断与准备** ```sql -- 主库检查 SHOW GLOBAL VARIABLES LIKE &#39;gtid_purged&#39;; SHOW MASTER STATUS\G -- 查看Executed_Gtid_Set -- 从库检查 SHOW GLOBAL VARIABLES LIKE &#39;gtid_executed&#39;; SHOW SLAVE STATUS\G -- 查看Executed_Gtid_Set和错误信息 ``` **关键判断**：当主库`gtid_purged`不是从库`gtid_executed`的子集时，复制必然中断[^1][^2] --- ### 2. **停止并重置从库服务** ```sql STOP SLAVE; RESET SLAVE ALL; -- 清除所有复制信息 RESET MASTER; -- 清空本地GTID信息[^3] ``` **作用**：清空从库的`gtid_executed`和`gtid_purged`，为导入新数据做准备 --- ### 3. **主库全量数据导出（关键步骤）** ```bash mysqldump -h主库IP -u用户 -p密码 \ --single-transaction \ --set-gtid-purged=ON \ # 包含GTID信息[^4] --all-databases \ --triggers --routines \ &gt; full_backup.sql ``` **参数说明**： - `--single-transaction`：确保一致性快照 - `--set-gtid-purged=ON`：在备份中嵌入`SET @@GLOBAL.gtid_purged`语句[^1][^4] --- ### 4. **从库导入数据（禁用binlog）** ```sql -- 在从库执行 SET sql_log_bin=0; -- 禁用binlog记录[^4] SOURCE full_backup.sql; SET sql_log_bin=1; -- 恢复binlog ``` **效果**： - 导入主库数据及GTID信息 - 从库`gtid_executed`和`gtid_purged`被重置为主库状态 --- ### 5. **验证GTID一致性** ```sql -- 检查从库GTID状态 SELECT @@GLOBAL.gtid_executed; SELECT @@GLOBAL.gtid_purged; -- 应与主库一致： SHOW MASTER STATUS\G -- 主库Executed_Gtid_Set ``` **成功标志**：主库`gtid_purged` &sube; 从库`gtid_executed` --- ### 6. **重新配置主从复制** ```sql CHANGE MASTER TO MASTER_HOST=&#39;主库IP&#39;, MASTER_USER=&#39;repl_user&#39;, MASTER_PASSWORD=&#39;密码&#39;, MASTER_AUTO_POSITION=1; -- 启用GTID自动定位[^2] START SLAVE; ``` --- ### 7. **监控复制状态** ```sql SHOW SLAVE STATUS\G ``` **关键指标**： - `Slave_IO_Running: Yes` - `Slave_SQL_Running: Yes` - `Retrieved_Gtid_Set`与`Executed_Gtid_Set`正常增长 - `Seconds_Behind_Master`趋于0 --- ### ⚠️ 重要注意事项 1. **物理备份场景**：使用xtrabackup时，需手动设置GTID ```sql SET GLOBAL gtid_purged=&#39;主库GTID集合&#39;; -- 恢复后立即执行[^4] ``` 2. **MySQL版本差异**： - MySQL 5.7：`RESET MASTER`会清空`gtid_executed`[^3] - MySQL 8.0：`gtid_executed`表持久化，需额外清理mysql.gtid_executed表 3. **锁表风险**：大库备份使用`--single-transaction`避免锁表 4. **网络中断处理**：备份过程中主库binlog需保留，建议设置： ```ini expire_logs_days = 7 # 至少保留7天binlog ``` --- ### 故障排除流程图 ```mermaid graph TD A[复制中断] --&gt; B{检查gtid_purged &sube; gtid_executed?} B --&gt;|是| C[检查网络/权限] B --&gt;|否| D[停止从库] D --&gt; E[重置从库GTID] E --&gt; F[主库全量备份] F --&gt; G[从库禁用binlog导入] G --&gt; H[配置主从] H --&gt; I[启动复制] I --&gt; J[监控状态] ``` 通过以上步骤，可彻底解决GTID冲突问题，重建健康的主从复制环境[^1][^4]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值