MySQL案例：数据库先hang后crash的故障分析

1. 故障现象描述
  • 阶段一：Hang（无响应）
  • 应用端连接超时，出现ERROR 1205 (HY000): Lock wait timeout exceeded错误
  • SHOW PROCESSLIST显示大量线程阻塞在Waiting for table metadata lock状态
  • 系统监控显示CPU/I/O资源正常，但查询吞吐量骤降至零
  • 阶段二：Crash（崩溃）
  • MySQL进程意外终止，错误日志出现InnoDB: Assertion failure或mysqld got signal 11
  • 自动重启后存在数据页损坏报错：InnoDB: Page ... log sequence number ...
2. 关键诊断步骤
-- 检查阻塞链 (hang阶段)
SELECT * FROM sys.innodb_lock_waits; 

-- 崩溃后日志分析 (重点区域)
grep -E "ERROR|Warning|assert" /var/log/mysql/error.log

诊断证据：

  1. 死锁循环：发现多个DDL操作（如ALTER TABLE）与长事务互相阻塞
  • 事务A持有元数据锁（MDL）等待行锁，事务B持有行锁等待MDL
  1. 内存溢出：崩溃前日志出现[ERROR] InnoDB: Out of memory in pool
  2. 磁盘异常：系统日志dmesg显示I/O error in device sdb
3. 根本原因分析

原因类型

具体表现

元数据锁冲突

在线DDL与未提交事务竞争资源，触发MDL死锁

内存配置缺陷

innodb_buffer_pool_size过大导致OOM，尤其发生在并发峰值时段

硬件故障

RAID卡电池故障引发写缓存异常，造成InnoDB页校验失败

版本缺陷

MySQL 5.7.21的InnoDB清理线程bug(Bug#88718)

4. 解决方案

紧急恢复：

# 强制跳过损坏页 (仅限紧急情况)
innodb_force_recovery = 4  # 添加到my.cnf后启动

根本性修复：

  1. 锁优化：
-- 设置事务超时 (防长事务)
SET GLOBAL innodb_lock_wait_timeout = 30;
-- 启用DDL原子性 (MySQL 8.0+)
ALTER TABLE orders ALGORITHM=INPLACE, LOCK=NONE;
  1. 内存调整：
# 计算安全缓冲池大小 (推荐公式)
innodb_buffer_pool_size = 0.7 * total_ram
  1. 硬件维护：更换RAID电池并启用innodb_flush_method=O_DIRECT_NO_FSYNC
5. 预防措施
  • 监控增强：
  • 部署Prometheus监控：mdl_lock_wait_time和buffer_pool_wait_free
  • 设置报警规则：Threads_running > 100持续60s
  • 架构改进：
  • 使用pt-online-schema-change执行无锁DDL
  • 读写分离架构，避免主库长事务
  • 版本管理：
  • 升级至MySQL 8.0.28+（修复已知MDL死锁bug）
  • 定期打补丁：mysql_upgrade --force

经验总结：该故障本质是软件配置缺陷与硬件老化的叠加效应。通过调整内存模型、优化DDL策略及硬件巡检，同环境后续稳定运行超过180天。建议每季度进行sysbench --test=oltp_lock_wait压力测试验证锁机制健康度。