时光错位：一次MySQL主从同步延迟的排查与优化

MySQL 主从同步
MySQL
发布日期:   2025-04-03

时光错位：一次MySQL主从同步延迟的排查与优化

问题出现背景

在生产环境中，执行 ALTER TABLE 语句向某张大表新增字段后，主从同步出现严重延迟。从库 SHOW SLAVE STATUS 显示 Seconds_Behind_Master 迅速增长，从正常的几秒飙升至数小时甚至上万秒，同时从库查询开始频繁超时，业务访问受阻。SHOW PROCESSLIST 发现大量查询处于 Waiting for table metadata lock 或 Waiting for table flush 状态，导致连接数剧增，最终触及 max_connections 限制，影响整个系统的稳定性。

问题解析

1. 查询从库状态

SHOW SLAVE STATUS\G

具体指标查看：关键字段解析

2. 结果发现

Slave_SQL_Running_State 值为： Waiting for table metadata lock

  • 正常值：Reading event from the relay log

Slave_IO_State

  • 正常值：Waiting for master to send event（等待主库发送事件）

Slave_IO_Running:

  • 正常值：Yes（等待主库发送事件）

发现 Slave_SQL_Running_State 值异常，并没有出现Duplicate column、Unknown、Can’t 、Eorr 等能看出是错误的地方。

3. 针对Waiting for table metadata lock 排查分析

可能的原因分析

  1. 主库执行了 DDL 语句
    • ALTER TABLE、CREATE INDEX、DROP TABLE 等操作可能会导致锁等待。
    • 如果从库正在执行 SQL，而主库又修改了表结构，从库可能会等待 metadata lock 释放。
  2. 主库长时间未提交事务
    • 事务未提交，导致从库 SQL 线程等待执行，阻塞后续操作。
  3. 从库上有并发查询占用了表
    • 从库上有查询正在使用表，而 SQL_THREAD 需要修改表结构，导致等待。
  4. 使用了 LOCK TABLES
    • 如果主库或者从库有 LOCK TABLES，可能会阻止复制线程获取 metadata lock。

通过现象分析 主库执行DDL 语句但并未阻塞，主库正常访问，而且从库的DDL并未执行，说明DDL语句已经阻塞了，排除1，2可能性，查询并未锁表现象，排除4，最大可能就是3

4. 查询未提交的事务

通show processlist 查到不少等待锁，但这些锁的时间都比较短 明显是DDL阻塞之后的查询也被阻塞

image

执行以下 SQL 语句，检查 INFORMATION_SCHEMA.INNODB_TRX 表，定位未提交的事务：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    trx_id,
    trx_mysql_thread_id,
    trx_query,
    trx_state,
    trx_wait_started,
    TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) AS wait_time_seconds
FROM
    INFORMATION_SCHEMA.INNODB_TRX;
或
 SELECT *
 FROM
     information_schema.INNODB_TRX
     WHERE STATE='ACTIVE';

查询结果显示存在 5 个未提交的事务，其中部分事务已运行超过 8 天，并且执行时间仍在持续增加。

5. 关联 SHOW PROCESSLIST 进行确认

通过 trx_mysql_thread_id 关联 SHOW PROCESSLIST 进一步确认事务详情，发现一个 ID 为 19138566 的线程，状态如下：

1
2
Id        User        Host                     db                                               Command      Time      State                     Info
19138566  readuser     10.11.11.11:50369        ioscar_customersystem_customerbasic              Query        648905    Creating sort index       SELECT concat(a.id) AS '案件ID', a.customer_name AS '客户',   ac.account_age AS '账龄段',

该查询已运行 648905 秒（约 8 天），且执行时间仍在增长，表明该事务可能已进入死锁或长时间未提交，严重影响 MySQL 性能和主从同步。

6. 终止异常事务

由于该事务占用资源并可能阻塞主从同步，立即执行 KILL 命令终止该事务：

1
KILL 19138566;

7. 观察从库状态变化

执行 SHOW SLAVE STATUS; 查看主从同步状态，发现 Slave_SQL_Running_State 由 Waiting for Master to send event 变更为 altering table，说明从库正在执行 ALTER TABLE 语句，即之前被阻塞的 DDL 语句开始恢复执行。

随着 ALTER TABLE 事件完成，Slave_SQL_Running_State 逐步变更为：

  • Waiting for Slave Worker queue
  • Reading event from the relay log

此时，说明从库已恢复正常同步，并正在继续处理 relay log。

8. 监控主从延迟恢复情况

查询 SHOW SLAVE STATUS;，关注 Seconds_Behind_Master（主从延迟时间）：

  • 事务终止前，Seconds_Behind_Master 超过 9W 秒（约 25 小时）
  • 终止事务后，Seconds_Behind_Master 开始逐步缩小
  • 经过数小时观察，主从延迟最终缩小至 1 秒以内
  • 验证主从数据，确保数据一致性

9. 结论

本次 MySQL 主从延迟的根因是 长期未提交的事务阻塞了主库的 binlog 生成，从而影响从库的同步。通过以下步骤成功修复问题：

  1. 查询未提交事务，定位长期运行 SQL
  2. 通过 SHOW PROCESSLIST 确认事务状态
  3. KILL 关键阻塞事务
  4. 观察 SHOW SLAVE STATUS 变化，验证 Slave_SQL_Running_State 状态
  5. 监控 Seconds_Behind_Master 缩小至 1 秒，确保主从同步恢复

至此，主从同步恢复正常，问题解决。

问题回顾

后续分析事故产生原理

主要从是 MySQL共享锁和独占锁 分析

发生 Waiting for table metadata lock 的原因

当 ALTER TABLE 需要 X 锁，但表上有正在执行的 SELECT 时，ALTER TABLE 会进入等待状态。这时 新的查询（SELECT）也会被阻塞，即使它只是想获取 S 锁！

📌 详细的锁定过程

  1. SELECT 语句执行，获取 **S 锁**（共享 metadata lock）。
  2. 你运行 ALTER TABLE，它需要 X 锁（独占 metadata lock），所以它必须等待所有 S 锁 释放。
  3. ALTER TABLE 在等待时，新来的 SELECT 也会被阻塞！
    • 因为 MySQL 会保证所有等待中的事务按顺序执行。
    • ALTER TABLE 必须先执行完，新的 SELECT 才能继续。

关键字段解析

SHOW SLAVE STATUS\G

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event --IO thread的状态
                  Master_Host: 10.10.10.10         -- 主库的地址     
                  Master_User: repl                -- 用于连接主库复制账号（这个账号是在主库上创建）
                  Master_Port: 3300                -- 主库的端口
                Connect_Retry: 10                  -- 连接重试之间的秒数（默认 60）
              Master_Log_File: mysql-bin.005395    -- I/O 线程当前正在读取的主库的二进制日志文件名称。
          Read_Master_Log_Pos: 684976832           -- I/O 线程已读取的当前主库二进制日志文件中的位点
               Relay_Log_File: dd-relay.000063     -- SQL线程正在读取和执行的中继日志名称
                Relay_Log_Pos: 684953253           -- SQL线程正在读取和执行的当前中继日志的位点
        Relay_Master_Log_File: mysql-bin.005395    -- SQL 线程执行的最新事件 对应在主库上的二进制日志文件名称。
             Slave_IO_Running: Yes                 -- IO线程是否已启动并已成功连接到主库
            Slave_SQL_Running: Yes                 -- SQL线程是否启动。
              Replicate_Do_DB:                     -- 需要复制的DB
          Replicate_Ignore_DB:                     -- 复制忽略的DB
           Replicate_Do_Table:                     -- 需要复制的表
       Replicate_Ignore_Table:                     -- 复制忽略的表
      Replicate_Wild_Do_Table:                     -- 用于指定需要复制的数据库表，支持通配符（wildcard）的形式
  Replicate_Wild_Ignore_Table:                     -- 用于指定需要忽略（不复制）的数据库表，同样支持通配符的形式。
                   Last_Errno: 0                   -- Last_SQL_Errno的别名
                   Last_Error:                     -- Last_SQL_Error的别名
                 Skip_Counter: 0                   -- 系统变sql_slave_skip_counter 的当前值  （从库跳过的SQL数量）
          Exec_Master_Log_Pos: 684953080           -- SQL线程已经读取和执行过的中继日志 对应在主库二进制日志文件的位点
              Relay_Log_Space: 684977292           -- 所有现有中继日志文件的总大小。
              Until_Condition: None                -- start slave 中制定 until 语句
               Until_Log_File:                     -- start slave 中制定 until 语句
                Until_Log_Pos: 0                   -- start slave 中制定 until 语句
           Master_SSL_Allowed: No                  -- 是否允许与源的 SSL 连接
           Master_SSL_CA_File:                     -- 指定用于验证主服务器证书的证书颁发机构（CA）文件的路径
           Master_SSL_CA_Path:                     -- 指定用于验证主服务器证书的证书颁发机构（CA）路径的路径
              Master_SSL_Cert:                     -- 指定从服务器的 SSL 证书文件的路径
            Master_SSL_Cipher:                     -- 指定在 SSL 通信中使用的密码套件
               Master_SSL_Key:                     -- 指定从服务器的 SSL 私钥文件的路径
        Seconds_Behind_Master: 0                   -- 主从延迟
Master_SSL_Verify_Server_Cert: No                  -- 表示是否验证主服务器的 SSL 证书。
                Last_IO_Errno: 0                   -- 导致IO线程停止的最近一次的错误码，Errno :0 表示表示没有错误
                Last_IO_Error:                     -- 导致IO线程停止的最近的错误信息 。Erro为空表示没有错误
               Last_SQL_Errno: 0                   -- 导致SQL线程停止的最近的错误码。Errno :0 表示没有错误
               Last_SQL_Error:                     -- 导致SQL线程停止的错误信息,Erro为空表示没有错误
  Replicate_Ignore_Server_Ids:                     -- 忽略复制的主库的server_id
             Master_Server_Id: 181323300           -- 主库的参数server_id的值
                  Master_UUID: 127ef593-1826-11eb-8a97-6c92bf7d39de           -- 主库参数server_uuid的值
             Master_Info_File: mysql.slave_master_info                        -- 在从库上存储主库信息的文件或表
                    SQL_Delay: 0                                              -- 从库延迟主库多少秒
          SQL_Remaining_Delay: NULL                                           -- 当Slave_SQL_Running_State为 时 Waiting until MASTER_DELAY seconds after master executed event，该字段包含剩余延迟秒数。其他时候，该字段为 NULL。
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates -- SQL线程的运行状态
           Master_Retry_Count: 86400  -- 在连接丢失的情况下，从库可以尝试重新连接到主库的次数。
                  Master_Bind:       --
      Last_IO_Error_Timestamp:       -- 最近的I/O 线程发生错误的时间 格式YYMMDD hh:mm:ss
     Last_SQL_Error_Timestamp:       -- 最近的SQL 线程发生错误的时间 格式YYMMDD hh:mm:ss
               Master_SSL_Crl:       -- 指定撤销列表 (CRL) 文件的路径，该文件包含已被撤销的 SSL 证书列表
           Master_SSL_Crlpath:       -- 指定撤销列表 (CRL) 文件的路径，该文件包含已被撤销的 SSL 证书列表
           Retrieved_Gtid_Set: 127ef593-1826-11eb-8a97-6c92bf7d39de:330411-2764671 -- 从库已经接收到的GTID的集合（I/O线程），如果GTID模式没有开启则为空。这个值是现在存在或者已经存在在relay log中的GTID集合
            Executed_Gtid_Set: 127ef593-1826-11eb-8a97-6c92bf7d39de:1-2764671,
3133d0b5-8d65-11e7-9f2e-c88d83a9846a:1-12697883,
657b7d6b-8d60-11e7-b85f-6c92bf4e09e6:1-1661102840    -- 已经被写进binlog的GTID的集合（SQL线程），这个值和 系统参数 gtid_executed 相同。也和在该实例上执行 show master status 中的Executed_Gtid_Set 值相同
                Auto_Position: 1  -- 如果正在使用自动定位1；否则为 0。
         Replicate_Rewrite_DB:    -- 用于指定需要在主从复制过程中进行数据库名重写的规则。
                 Channel_Name:    -- 正在显示的复制通道
           Master_TLS_Version:    -- 源上使用的 TLS 版本

执行后，可能会看到如下重要字段：

  • SHOW SLAVE STATUS\G 主要用于监控从库同步状态。
  • Slave_IO_Running 和 Slave_SQL_Running 应该都是 Yes。
  • Seconds_Behind_Master 应该尽量接近 0。
  • 如果有错误，可以 STOP SLAVE，检查 Last_Error，必要时重新设置主从关系

Slave_IO_Running 字段用于指示 MySQL 复制中 I/O 线程的运行状态，常见的值包括：

值 含义
Yes I/O 线程正在运行，正常从主库读取 binlog
No I/O 线程未运行，可能由于错误或手动停止
Connecting I/O 线程正在尝试连接主库，但尚未成功

常见情况解析

  1. 正常状态
    • Slave_IO_Running: Yes
    • 说明从库成功连接到主库，并在持续接收 binlog。
  2. 连接失败
    • Slave_IO_Running: Connecting
    • 说明从库正在尝试连接主库，但尚未成功，可能是：
      • 主库地址 (Master_Host) 配置错误
      • 主库未开启 binlog
      • 端口 (Master_Port) 未开放
      • 账户或密码错误 (Master_User / Master_Password)
  3. I/O 线程停止
    • Slave_IO_Running: No
    • 可能原因：
      • 手动执行了 STOP SLAVE
      • 网络问题导致连接断开
      • 认证失败（账号或权限问题）
      • Last_IO_Error 字段中可能会有详细错误信息

Slave_IO_State 是 SHOW SLAVE STATUS\G 输出中的一个字段，它描述了 MySQL 复制中 I/O 线程当前的状态，表示它正在执行的操作。

常见 Slave_IO_State 值及含义

Slave_IO_State 值 说明
Waiting for master to send event 从库已经成功连接主库，并在等待主库发送 binlog（正常状态）。
Connecting to master 正在尝试连接主库，但连接尚未建立，可能是主库未启动或网络问题。
Waiting for the slave SQL thread to free relay log SQL 线程执行过慢，I/O 线程等待 SQL 线程处理 relay log。
Waiting for master update 说明主库上没有新的 binlog 事件，I/O 线程在等待更新（通常无问题）。
Reconnecting after a failed binlog dump request 从库尝试重新连接主库，可能是由于网络问题或主库重启导致连接断开。
Waiting for master connection 复制未正常启动，可能 START SLAVE 尚未执行，或者连接参数错误。
Queueing master event to the relay log I/O 线程正在将主库 binlog 事件写入 relay log（正常状态）。
Waiting to reconnect after a failed master event read 从库读取主库 binlog 失败，正在等待重连。可能是主库关闭、网络异常等原因。
Waiting for master to send event (after aborting replication due to an error) 发生复制错误，导致 I/O 线程终止，可能需要手动修复并重启 START SLAVE。

title: 时光错位：一次MySQL主从同步延迟的排查与优化

title: 时光错位：一次MySQL主从同步延迟的排查与优化

Slave_SQL_Running_State 解析

Slave_SQL_Running_State 主要描述 从库 SQL 线程的当前状态，可以帮助判断从库是否正常执行 binlog 事件。

📌 常见状态解析

执行 SHOW SLAVE STATUS\G 可能会看到以下 Slave_SQL_Running_State：

状态 说明 解决方案（如果有问题）
Reading event from the relay log SQL 线程正在从 relay log 读取 binlog 事件，并准备执行 正常状态，无需处理
Waiting for dependent transaction to commit 等待前一个事务提交（事务串行化导致等待） 正常状态，但如果卡住太久，检查 SHOW PROCESSLIST;
Waiting for master to send event 从库 I/O 线程在等待主库发送 binlog 事件 正常状态，但如果长时间无进展，检查 Slave_IO_Running
Slave has read all relay log; waiting for more updates 从库 SQL 线程已经执行完 relay log，等待新的数据 正常状态
Waiting for table metadata lock 被 metadata lock 阻塞，导致 SQL 线程无法继续 参见 本文 解决方案
Waiting for an event from Coordinator 适用于多线程复制（MTS），SQL 线程在等待事件分配 正常状态，但如果卡住，检查 SHOW PROCESSLIST;
Error ‘…’ on query. Default database: ‘…’. Query: ‘…’ 复制 SQL 线程遇到错误，可能导致复制停止 检查 Last_SQL_Error 并修复错误
NULL（空值） SQL 线程未运行（可能已停止） START SLAVE SQL_THREAD; 重新启动

MySQL共享锁和独占锁

在 MySQL 数据库中，锁机制是确保数据一致性和完整性的重要手段，主要分为共享锁（Shared Lock，简称 S 锁）和独占锁（Exclusive Lock，简称 X 锁）。

共享锁（S 锁）

共享锁允许多个事务同时读取同一数据，而不会相互阻塞。当一个事务对数据加上共享锁后，其他事务也可以对该数据加共享锁，但不能加独占锁。这意味着在持有共享锁的情况下，数据只能被读取，不能被修改。

应用场景：

当需要读取某条记录并希望防止其他事务对其进行修改时，可以使用共享锁。在 MySQL 中，可以通过在 SELECT 语句后添加 LOCK IN SHARE MODE 来实现：

1
2
3
4
START TRANSACTION;
SELECT * FROM your_table WHERE id = 1 LOCK IN SHARE MODE;
-- 其他操作
COMMIT;

上述语句会对 your_table 中满足条件的记录加上共享锁，直到事务提交或回滚后释放。

独占锁（X 锁）

独占锁又称为排他锁或写锁，在同一时刻只允许一个事务对数据进行修改。当一个事务对数据加上独占锁后，其他事务既不能加共享锁，也不能加独占锁，必须等待锁的释放。这确保了数据的修改操作是互斥的，防止了并发修改导致的数据不一致问题。

应用场景：

当需要更新或删除某条记录，并希望在操作完成前防止其他事务对其进行读取或修改时，可以使用独占锁。在 MySQL 中，UPDATE、DELETE 等操作会自动对涉及的记录加独占锁。如果需要在 SELECT 查询时手动加独占锁，可以使用 FOR UPDATE：

1
2
3
START TRANSACTION;
SELECT * FROM your_table WHERE id = 1 FOR UPDATE;
COMMIT;

上述语句会对 your_table 中满足条件的记录加上独占锁，直到事务提交或回滚后释放。

共享锁与独占锁的兼容性

共享锁和独占锁之间的兼容性如下：

  • 共享锁 vs. 共享锁：兼容，多个事务可以同时持有共享锁。
  • 共享锁 vs. 独占锁：不兼容，若一个事务持有共享锁，其他事务不能获取独占锁，反之亦然。
  • 独占锁 vs. 独占锁：不兼容，一个事务持有独占锁时，其他事务不能获取独占锁。

注意事项

  • 默认读取行为：在 MySQL 的 InnoDB 存储引擎中，普通的 SELECT 语句默认使用快照读，不会加任何锁，而是通过多版本并发控制（MVCC）机制读取数据的快照。只有在显式使用 LOCK IN SHARE MODE 或 FOR UPDATE 时，才会对读取的数据加锁。
  • 死锁与性能：过度使用锁可能导致死锁或性能下降，因此应根据具体需求合理使用锁机制，避免不必要的锁争用。

通过正确理解和使用共享锁与独占锁，可以有效控制并发事务对数据的访问，确保数据的正确性和一致性。

文章作者: victor.smile
文章链接: https://blog.restlessnight.cn/2025/04/03/shi-guang-cuo-wei-yi-ci-mysql-zhu-cong-tong-bu-yan-chi-de-pai-cha-yu-you-hua/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 victor.smile !
MySQL 主从同步
赏
  目录