时光错位：一次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 排查分析
可能的原因分析
- 主库执行了 DDL 语句
ALTER TABLE、CREATE INDEX、DROP TABLE等操作可能会导致锁等待。- 如果从库正在执行 SQL，而主库又修改了表结构，从库可能会等待 metadata lock 释放。
- 主库长时间未提交事务
- 事务未提交，导致从库 SQL 线程等待执行，阻塞后续操作。
- 从库上有并发查询占用了表
- 从库上有查询正在使用表，而
SQL_THREAD需要修改表结构，导致等待。
- 从库上有查询正在使用表，而
- 使用了
LOCK TABLES- 如果主库或者从库有
LOCK TABLES，可能会阻止复制线程获取 metadata lock。
- 如果主库或者从库有
通过现象分析 主库执行DDL 语句但并未阻塞，主库正常访问，而且从库的DDL并未执行，说明DDL语句已经阻塞了，排除1，2可能性，查询并未锁表现象，排除4，最大可能就是3
4. 查询未提交的事务
通show processlist 查到不少等待锁，但这些锁的时间都比较短 明显是DDL阻塞之后的查询也被阻塞
执行以下 SQL 语句，检查 INFORMATION_SCHEMA.INNODB_TRX 表，定位未提交的事务：
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 的线程，状态如下：
Id User Host db Command Time State Info
19138566 cbcread 10.60.86.91:60369 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 命令终止该事务：
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 queueReading 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 生成，从而影响从库的同步。通过以下步骤成功修复问题：
- 查询未提交事务，定位长期运行 SQL
- 通过
SHOW PROCESSLIST确认事务状态 KILL关键阻塞事务- 观察
SHOW SLAVE STATUS变化，验证Slave_SQL_Running_State状态 - 监控
Seconds_Behind_Master缩小至 1 秒，确保主从同步恢复
至此，主从同步恢复正常，问题解决。
问题回顾
后续分析事故产生原理
主要从是 MySQL共享锁和独占锁 分析
发生 Waiting for table metadata lock 的原因
当 ALTER TABLE 需要 X 锁，但表上有正在执行的 SELECT 时，ALTER TABLE 会进入等待状态。这时 新的查询（SELECT）也会被阻塞，即使它只是想获取 S 锁！
📌 详细的锁定过程
SELECT语句执行，获取S 锁（共享 metadata lock）。- 你运行
ALTER TABLE，它需要X 锁（独占 metadata lock），所以它必须等待所有S 锁释放。 ALTER TABLE在等待时，新来的SELECT也会被阻塞！- 因为 MySQL 会保证所有等待中的事务按顺序执行。
ALTER TABLE必须先执行完，新的SELECT才能继续。
关键字段解析
SHOW SLAVE STATUS\G
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 线程正在尝试连接主库，但尚未成功 |
常见情况解析
- 正常状态
Slave_IO_Running: Yes- 说明从库成功连接到主库，并在持续接收 binlog。
- 连接失败
Slave_IO_Running: Connecting- 说明从库正在尝试连接主库，但尚未成功，可能是：
- 主库地址 (
Master_Host) 配置错误 - 主库未开启
binlog - 端口 (
Master_Port) 未开放 - 账户或密码错误 (
Master_User/Master_Password)
- 主库地址 (
- 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 来实现：
START TRANSACTION;
SELECT * FROM your_table WHERE id = 1 LOCK IN SHARE MODE;
-- 其他操作
COMMIT;
上述语句会对 your_table 中满足条件的记录加上共享锁，直到事务提交或回滚后释放。
独占锁（X 锁）
独占锁又称为排他锁或写锁，在同一时刻只允许一个事务对数据进行修改。当一个事务对数据加上独占锁后，其他事务既不能加共享锁，也不能加独占锁，必须等待锁的释放。这确保了数据的修改操作是互斥的，防止了并发修改导致的数据不一致问题。
应用场景：
当需要更新或删除某条记录，并希望在操作完成前防止其他事务对其进行读取或修改时，可以使用独占锁。在 MySQL 中，UPDATE、DELETE 等操作会自动对涉及的记录加独占锁。如果需要在 SELECT 查询时手动加独占锁，可以使用 FOR UPDATE：
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时，才会对读取的数据加锁。 - 死锁与性能：过度使用锁可能导致死锁或性能下降，因此应根据具体需求合理使用锁机制，避免不必要的锁争用。
通过正确理解和使用共享锁与独占锁，可以有效控制并发事务对数据的访问，确保数据的正确性和一致性。
