MySQL教程 / 第 280 节

第28章：故障排查实战

实战是检验技术的唯一标准

28.1 故障排查概述

28.1.1 常见故障类型

性能故障：

查询慢
CPU使用率高
磁盘I/O高
内存不足

可用性故障：

MySQL服务停止
无法连接
主从复制中断
数据损坏

数据故障：

数据丢失
数据不一致
误删除数据

28.2 查询慢故障排查 ⭐⭐⭐⭐⭐

28.2.1 问题现象

用户反馈：查询很慢，页面加载超过10秒

28.2.2 排查步骤

步骤1：查看当前查询

SHOW FULL PROCESSLIST;

-- 发现：
-- Id: 123, Time: 15, State: Sending data
-- Info: SELECT * FROM orders WHERE user_id = 12345

步骤2：分析执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

-- 结果：
-- type: ALL
-- rows: 1000000
-- Extra: Using where
-- 问题：全表扫描，没有索引

步骤3：检查索引

SHOW INDEX FROM orders;

-- 发现：user_id字段没有索引

步骤4：添加索引

CREATE INDEX idx_user_id ON orders(user_id);

步骤5：验证

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

-- 结果：
-- type: ref
-- key: idx_user_id
-- rows: 100
-- 性能提升1000倍

28.3 CPU使用率高故障排查 ⭐⭐⭐⭐⭐

28.3.1 问题现象

监控告警：MySQL服务器CPU使用率100%

28.3.2 排查步骤

步骤1：查看系统负载

top

# 发现：mysqld进程CPU使用率100%

步骤2：查看当前查询

SHOW FULL PROCESSLIST;

-- 发现大量查询：
-- SELECT * FROM users WHERE name LIKE '%张%'

步骤3：分析问题

EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';

-- 结果：
-- type: ALL
-- rows: 10000000
-- 问题：LIKE '%张%'无法使用索引，全表扫描

步骤4：优化方案

-- 方案1：使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张');

-- 方案2：使用Elasticsearch
-- 将搜索功能迁移到ES

-- 方案3：限制查询
-- 要求用户输入至少2个字符，使用'张%'而不是'%张%'

28.4 连接数耗尽故障排查 ⭐⭐⭐⭐⭐

28.4.1 问题现象

应用报错：Too many connections

28.4.2 排查步骤

步骤1：查看连接数

SHOW STATUS LIKE 'Threads_connected';
-- Threads_connected: 600

SHOW VARIABLES LIKE 'max_connections';
-- max_connections: 600

-- 问题：连接数已满

步骤2：查看连接来源

SELECT 
    USER,
    HOST,
    COUNT(*) AS conn_count
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY conn_count DESC;

-- 结果：
-- app_user  192.168.1.100  500
-- 问题：某个应用服务器占用大量连接

步骤3：查看连接状态

SELECT 
    COMMAND,
    COUNT(*) AS count
FROM information_schema.PROCESSLIST
GROUP BY COMMAND;

-- 结果：
-- Sleep  480
-- Query   20
-- 问题：大量Sleep连接（连接未释放）

步骤4：解决方案

-- 临时方案：增加最大连接数
SET GLOBAL max_connections = 1000;

-- 长期方案：
-- 1. 检查应用代码，确保连接正确释放
-- 2. 使用连接池
-- 3. 设置wait_timeout
SET GLOBAL wait_timeout = 600;  -- 10分钟
SET GLOBAL interactive_timeout = 600;

-- 4. 杀死长时间Sleep的连接
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Sleep' AND TIME > 600;

28.5 主从复制中断故障排查 ⭐⭐⭐⭐⭐

28.5.1 问题现象

监控告警：主从复制中断

28.5.2 排查步骤

步骤1：查看复制状态

SHOW SLAVE STATUS\G

-- 关注字段：
-- Slave_IO_Running: No
-- Slave_SQL_Running: Yes
-- Last_IO_Error: Got fatal error 1236 from master...

步骤2：分析错误

错误类型1：binlog文件不存在
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因：主库binlog被删除
解决：重新搭建主从复制

错误类型2：SQL线程错误
Slave_SQL_Running: No
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query

原因：主从数据不一致
解决：跳过错误或重新同步

步骤3：解决方案

方案1：跳过错误（谨慎使用）

-- 跳过1个错误
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

方案2：重新搭建主从

# 1. 主库备份
mysqldump -u root -p --single-transaction --master-data=2 --all-databases > backup.sql

# 2. 从库导入
mysql -u root -p < backup.sql

# 3. 配置复制
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000010',
  MASTER_LOG_POS=154;

START SLAVE;

28.6 磁盘空间满故障排查 ⭐⭐⭐⭐⭐

28.6.1 问题现象

MySQL无法写入数据
错误：ERROR 3 (HY000): Error writing file '/var/lib/mysql/...' (Errcode: 28 - No space left on device)

28.6.2 排查步骤

步骤1：查看磁盘空间

df -h

# 结果：
# /dev/sda1  100G  100G  0  100%  /var/lib/mysql
# 问题：磁盘已满

步骤2：查找大文件

# 查看MySQL数据目录大小
du -sh /var/lib/mysql/*

# 结果：
# 50G  /var/lib/mysql/mydb
# 30G  /var/lib/mysql/mysql-bin.*
# 20G  /var/lib/mysql/ibdata1

步骤3：清理空间

清理binlog：

-- 查看binlog
SHOW BINARY LOGS;

-- 删除7天前的binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 或删除指定binlog之前的所有binlog
PURGE BINARY LOGS TO 'mysql-bin.000100';

-- 配置自动清理
SET GLOBAL expire_logs_days = 7;

清理慢查询日志：

# 备份后清空
cp /var/lib/mysql/slow.log /backup/slow.log.bak
> /var/lib/mysql/slow.log

清理临时文件：

# 清理/tmp目录
rm -f /tmp/mysql*

28.7 死锁故障排查 ⭐⭐⭐⭐⭐

28.7.1 问题现象

应用报错：Deadlock found when trying to get lock

28.7.2 排查步骤

步骤1：查看死锁信息

SHOW ENGINE INNODB STATUS\G

-- 查找LATEST DETECTED DEADLOCK部分

步骤2：分析死锁

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 140123456789, query id 100 localhost root updating
UPDATE users SET age = 20 WHERE id = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3 n bits 72 index PRIMARY of table `mydb`.`users` trx id 12345 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 140123456790, query id 101 localhost root updating
UPDATE users SET age = 21 WHERE id = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3 n bits 72 index PRIMARY of table `mydb`.`users` trx id 12346 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3 n bits 72 index PRIMARY of table `mydb`.`users` trx id 12346 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (1)

步骤3：解决方案

-- 1. 优化事务逻辑，减少锁持有时间
-- 2. 按相同顺序访问资源
-- 3. 使用乐观锁代替悲观锁
-- 4. 降低事务隔离级别（谨慎）

28.8 数据恢复实战

28.8.1 误删除数据

场景：

-- 误删除
DELETE FROM users WHERE age > 18;
-- 删除了100万条数据

恢复步骤：

# 1. 停止应用，防止继续写入

# 2. 使用binlog恢复
# 找到误删除之前的binlog位置
mysqlbinlog --start-datetime="2024-11-10 09:00:00" \
  --stop-datetime="2024-11-10 09:59:59" \
  /var/lib/mysql/mysql-bin.000010 > recover.sql

# 3. 过滤掉DELETE语句
grep -v "DELETE FROM users" recover.sql > recover_filtered.sql

# 4. 恢复数据
mysql -u root -p mydb < recover_filtered.sql

# 5. 验证数据
SELECT COUNT(*) FROM users;

28.9 故障排查最佳实践

28.9.1 故障排查流程

1. 确认问题
   - 收集现象和错误信息
   - 确定影响范围

2. 快速止损
   - 重启服务
   - 切换到备库
   - 限流降级

3. 定位问题
   - 查看日志
   - 分析监控数据
   - 使用诊断工具

4. 解决问题
   - 临时方案
   - 长期方案

5. 验证
   - 确认问题解决
   - 监控观察

6. 总结
   - 记录故障原因
   - 制定预防措施
   - 更新文档

28.9.2 常用命令清单

# 查看进程
SHOW PROCESSLIST;

# 查看状态
SHOW STATUS;

# 查看变量
SHOW VARIABLES;

# 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

# 查看主从状态
SHOW SLAVE STATUS;

# 查看binlog
SHOW BINARY LOGS;

# 查看错误日志
tail -f /var/log/mysql/error.log

# 查看慢查询日志
tail -f /var/lib/mysql/slow.log

# 查看磁盘空间
df -h

# 查看I/O
iostat -x 1

# 查看CPU
top

28.10 本章总结

本章学习内容：

✅ 故障排查概述
✅ 查询慢故障排查⭐⭐⭐⭐⭐
✅ CPU使用率高故障排查⭐⭐⭐⭐⭐
✅ 连接数耗尽故障排查⭐⭐⭐⭐⭐
✅ 主从复制中断故障排查⭐⭐⭐⭐⭐
✅ 磁盘空间满故障排查⭐⭐⭐⭐⭐
✅ 死锁故障排查⭐⭐⭐⭐⭐
✅ 数据恢复实战
✅ 故障排查最佳实践

重点掌握：

查询慢：EXPLAIN分析，添加索引
CPU高：找出慢查询，优化SQL
连接数满：检查连接泄漏，使用连接池
主从中断：查看SLAVE STATUS，重新搭建
磁盘满：清理binlog，清理日志
死锁：SHOW ENGINE INNODB STATUS分析

故障排查流程：

确认问题
快速止损
定位问题
解决问题
验证
总结

面试重点：

如何排查慢查询
如何处理连接数耗尽
如何处理主从复制中断
如何分析死锁
如何恢复误删除的数据

下一章预告： 用户与权限管理

练习题

如何排查查询慢的问题？
CPU使用率100%如何排查？
连接数耗尽如何处理？
主从复制中断如何排查？
磁盘空间满如何处理？
如何分析死锁？
如何恢复误删除的数据？
故障排查的流程是什么？
如何清理binlog？
实战：模拟一个故障并排查

继续学习： 第29章：用户与权限管理