MySQL数据库故障排查与解决方案

一、故障排查流程图

---

二、分场景故障排查与解决方案

场景1：连接问题（应用程序无法连接MySQL）

现象：

• 应用程序报错“Connection refused”或“Access denied”。
• 部分用户反馈无法登录系统。

排查步骤：

1. 检查MySQL服务状态

   systemctl status mysqld  # Linux
   # 或通过任务管理器查看MySQL进程（Windows）
   

   • 若未运行：启动服务并检查日志 /var/log/mysqld.log 是否有启动错误。

2. 验证网络连通性

   telnet <MySQL_IP> 3306  # 测试端口是否开放
   ping <MySQL_IP>          # 测试基础网络连通性
   

   • 若不通：检查防火墙规则（如iptables -L）或云服务器安全组配置。

3. 检查用户权限

   SELECT host, user FROM mysql.user;  -- 查看用户权限配置
   

   • 若用户无远程访问权限

     GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
     FLUSH PRIVILEGES;
     

4. 检查连接数限制

   SHOW VARIABLES LIKE 'max_connections';  -- 默认151，可能不足
   SHOW STATUS LIKE 'Threads_connected';  -- 当前连接数
   

   • 若连接数耗尽

     • 临时调整：SET GLOBAL max_connections = 300;
     • 永久生效：修改my.cnf文件并重启MySQL。

解决方案：

• 启动MySQL服务并修复配置文件（如bind-address=0.0.0.0允许远程连接）。
• 调整防火墙规则或安全组策略。
• 优化应用连接池配置（如HikariCP的maximumPoolSize）。

---

场景2：性能问题（查询响应慢）

现象：

• 用户反馈系统响应时间超过5秒。
• 监控显示CPU使用率持续100%。

排查步骤：

1. 识别慢查询

   SHOW VARIABLES LIKE 'slow_query_log%';  -- 确认慢查询日志是否开启
   SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;  -- 查看最近慢查询
   

   • 若未开启

     SET GLOBAL slow_query_log = 'ON';
     SET GLOBAL long_query_time = 1;  -- 记录超过1秒的查询
     

2. 分析执行计划

   sqlEXPLAIN SELECT * FROM orders WHERE user_id = 123;  -- 示例查询
   

   • 关键指标：type（避免ALL全表扫描）、key（是否使用索引）、rows（预估扫描行数）。

3. 检查锁等待

   SHOW ENGINE INNODB STATUS\G  -- 查看锁等待详情
   SELECT * FROM information_schema.INNODB_TRX;  -- 当前事务列表
   

   • 若存在死锁

     • 终止阻塞事务：KILL <trx_mysql_thread_id>;
     • 优化事务设计（减少大事务、避免长事务）。

4. 监控硬件资源

   top -c                     # 查看CPU和内存使用
   iostat -x 1 3              # 查看磁盘I/O延迟（>50ms需优化）
   

解决方案：

• 为高频查询字段添加索引（如ALTER TABLE orders ADD INDEX idx_user_id (user_id);）。
• 优化SQL语句（避免SELECT *、使用JOIN替代子查询）。
• 升级硬件（如SSD磁盘、增加内存）或分库分表。

---

场景3：数据损坏（表无法访问）

现象：

• 查询某表时报错“Table is marked as crashed”。
• 数据库启动失败，日志显示“InnoDB: Tablespace is missing”。

排查步骤：

1. 检查表状态

   sqlCHECK TABLE orders;  -- 返回“Table is marked as crashed”
   

2. 尝试自动修复

   sqlREPAIR TABLE orders;  -- 仅适用于MyISAM表
   

3. InnoDB表损坏处理

   • 若为InnoDB表且损坏严重：

     1. 备份现有数据文件（.ibd和.frm）。

     2. 删除表空间文件并重启MySQL（会丢失数据）：

        rm /var/lib/mysql/db_name/table_name.ibd
        systemctl restart mysqld
        

     3. 从备份恢复数据。

解决方案：

• 定期执行mysqlcheck -uroot -p --auto-repair --all-databases预防损坏。
• 启用InnoDB的innodb_force_recovery参数（值1-6逐步尝试）强制启动数据库。

---

场景4：备份恢复失败

现象：

• 执行mysqldump备份时中断。
• 恢复备份后数据不一致。

排查步骤：

1. 检查备份文件完整性

   head -n 10 backup.sql  # 确认开头有DROP/CREATE语句
   tail -n 10 backup.sql  # 确认结尾有COMMIT语句
   

2. 验证备份过程

   • 使用

     --single-transaction
     

     参数避免锁表：

     bashmysqldump -uroot -p --single-transaction db_name > backup.sql
     

3. 恢复测试

   • 在测试环境模拟恢复：

     bashmysql -uroot -p < backup.sql
     

解决方案：

• 改用Percona XtraBackup进行热备份。

• 恢复前校验备份文件MD5值：

  bashmd5sum backup.sql
  

---

三、预防措施

1. 监控告警：

   • 部署Prometheus + Grafana监控MySQL关键指标（QPS、连接数、慢查询数）。
   • 设置阈值告警（如连接数>80%时通知）。

2. 自动化巡检： 编写Shell脚本每日检查：

   # 示例：检查慢查询
   if [ $(grep -c "Query_time" /var/log/mysql/mysql-slow.log) -gt 100 ]; thenecho "发现慢查询，请优化！" | mail -s "MySQL告警" [e-mail]
   fi
   

3. 灾备演练： 每季度执行一次主从切换或异地恢复演练。

---

四、总结

通过分场景排查、结合日志与监控工具，可快速定位MySQL故障根源。建议技术团队：

1. 优先恢复服务：通过临时调整参数（如增加连接数）快速恢复业务。
2. 根本解决：针对性能问题优化SQL，针对连接问题修复配置。
3. 长期预防：完善监控、备份与容灾体系，减少故障复发概率。