MySQLNotes

第 31 章：故障排查手册

zjc 于 2026-01-31 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 故障排查的原则是：先止血，再定位，后复盘。不要在事故中追求完美根因，而让业务持续不可用。

31.1 通用排查流程

1. 确认影响面：接口、用户、机房、错误率；
2. 判断是否数据库：应用日志、连接错误、SQL 延迟；
3. 保存现场：SHOW PROCESSLIST、错误日志、监控截图；
4. 寻找变更：发布、DDL、参数、活动、流量；
5. 定位资源：CPU、IO、锁、连接、复制、磁盘；
6. 止血：限流、杀会话、切读、扩容、回滚；
7. 恢复验证：核心接口、数据校验、监控回落；
8. 根因分析和改进项；

31.2 常用命令集

会话与状态：

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS\G

事务：

SELECT
  trx_id,
  trx_state,
  trx_started,
  trx_rows_locked,
  trx_rows_modified,
  trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

锁：

SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM performance_schema.data_locks;

复制：

SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_applier_status_by_worker;

容量：

SELECT TABLE_NAME, TABLE_ROWS,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop';

31.3 场景一：应用连不上

典型报错：

Communications link failure
Too many connections
Access denied
Connection refused

排查：

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Aborted_connects';
SHOW PROCESSLIST;

可能原因：

  1. MySQL 停止；
  2. 端口不可达；
  3. 安全组或防火墙；
  4. 超过 max_connections；
  5. 账号来源限制；
  6. 密码或认证插件；
  7. 连接池耗尽；
  8. 主机被 blocked。

止血：

  1. 恢复进程；
  2. 放行网络；
  3. 杀掉空闲异常连接；
  4. 应用限流；
  5. 临时提高连接数；
  6. 回滚连接池配置。

31.4 场景二：CPU 高

常见原因：

  1. 慢 SQL 扫描；
  2. 并发查询暴涨；
  3. 排序聚合；
  4. 锁等待后重试风暴；
  5. 复制应用压力；
  6. SQL 解析过多；
  7. 备份和监控任务；
  8. 机器规格不足。

查看：

SELECT *
FROM sys.session
ORDER BY statement_latency DESC
LIMIT 10;

处理：

  1. 终止明显异常查询；
  2. 应用限流；
  3. 优化索引；
  4. 报表下线或切走；
  5. 停止非核心任务；
  6. 扩容或切换；
  7. 回滚发布。

只终止查询：

KILL QUERY <processlist_id>;

终止连接：

KILL <processlist_id>;

杀之前必须确认连接身份和事务状态。

31.5 场景三：IO 高或磁盘延迟高

常见原因：

  1. Buffer Pool 命中率低；
  2. 大查询读大量页；
  3. 脏页刷盘；
  4. Redo 空间不足；
  5. Binlog 洪峰；
  6. 备份任务；
  7. 从库追复制；
  8. 磁盘故障或云盘限流；
  9. 临时表落盘。

查看：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending%';

处理：

  1. 停大查询；
  2. 延迟备份；
  3. 限制导入速率；
  4. 拆分大事务；
  5. 增大 Buffer Pool 需评估内存；
  6. 云盘升配；
  7. 只读流量切到其他从库。

31.6 场景四：锁等待

查看：

SELECT * FROM sys.innodb_lock_waits;

判断：

  1. 阻塞源头事务；
  2. 等待 SQL；
  3. 锁住的表和索引；
  4. 事务年龄；
  5. 是否长事务或外部调用。

处理：

  1. 请求业务确认；
  2. 终止阻塞事务；
  3. 回滚异常会话；
  4. 限流热点接口；
  5. 修复索引；
  6. 优化锁顺序。

不要盲目杀最大事务，可能是正在正常执行的批任务。杀错会触发回滚并进一步占用资源。

31.7 场景五：死锁激增

查看：

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';
SHOW ENGINE INNODB STATUS\G

排查：

  1. 死锁事务 SQL；
  2. 加锁顺序；
  3. 是否 RR 间隙锁；
  4. 唯一键冲突；
  5. 大事务；
  6. 应用是否重试。

止血：

  1. 限流冲突接口；
  2. 暂停批任务；
  3. 回滚近期发布；
  4. 临时调整隔离级别需评审；
  5. 增加重试；
  6. 统一更新顺序。

31.8 场景六：主从延迟

查看：

SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_applier_status_by_worker;

常见原因：

  1. 主库大事务；
  2. 从库单线程应用；
  3. 从库硬件差；
  4. 从库重查询；
  5. 表结构缺索引；
  6. 网络瓶颈。

处理：

  1. 停止主库大批任务；
  2. 从库下线报表查询；
  3. 增加并行 worker；
  4. 暂停非关键 DDL；
  5. 临时读主库；
  6. 无法收敛时重建或切到其他从库。

31.9 场景七：磁盘满

检查：

SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'log_bin_basename';
SHOW VARIABLES LIKE 'tmpdir';
SHOW BINARY LOGS;

常见来源：

  1. Binlog 增长；
  2. 大表空间；
  3. 临时表落盘；
  4. Relay Log 堆积；
  5. 备份残留；
  6. 错误日志暴涨；
  7. Undo 膨胀；
  8. 快照恢复残留。

安全处理：

PURGE BINARY LOGS BEFORE '2026-08-18 00:00:00';

不要直接 rm MySQL 正在管理的文件。清理前必须确认从库已消费和备份链路可恢复。

31.10 场景八：重启后变慢

典型现象：

  1. Buffer Pool 冷；
  2. 命中率低；
  3. 磁盘读高；
  4. P99 尖刺。

处理：

  1. 降低流量；
  2. 预热核心索引；
  3. 逐步放量；
  4. 观察命中率；
  5. 保留更长监控窗口；
  6. 滚动重启避免同时冷启动。

31.11 场景九：误更新或误删除

立即动作：

1. 停止写入或下线相关功能；
2. 保留当前 Binlog；
3. 记录误操作时间和 SQL；
4. 不要在主库继续尝试修复；
5. 恢复备份到临时实例；
6. 用 Binlog 找到目标数据；
7. 校验后导出修复 SQL；
8. 双人确认后执行；

修复期间要防止新数据覆盖，并为修复脚本准备回滚语句。

31.12 事故复盘模板

标题：
时间：
影响：
处理时间线：
监控表现：
根因：
触发条件：
为什么没有提前发现：
为什么没有被测试覆盖：
止血动作：
长期改进：
负责人：
截止时间：

改进项必须可执行，例如“上线前必须用影子库执行 EXPLAIN”，而不是“大家以后小心”。

本章小结

故障排查先确认影响面和变更历史，再用会话、事务、锁、复制、资源和日志定位。常见故障包括连接耗尽、CPU 高、IO 高、锁等待、死锁、主从延迟、磁盘满和冷启动。事故中优先止血和保存现场，事后用可执行改进项闭环。

思考题

  1. 为什么事故中要先保存现场？
  2. CPU 高和 IO 高的处理顺序有什么不同？
  3. 杀阻塞事务前需要确认什么？
  4. 磁盘满时为什么不能直接删除 Binlog 文件？
  5. 为误更新场景写一份止血和恢复流程。