​​​​​​​MySQL数据库故障排查指南

一、连接类问题

1. 无法连接数据库

• 现象：应用或客户端无法连接MySQL服务。

• 排查步骤：

  1. 检查服务状态：

     bash

     复制

     下载

     systemctl status mysqld      # 检查MySQL是否运行
     netstat -tuln | grep 3306    # 确认3306端口是否监听

  2. 检查网络问题：

     • 防火墙是否放行3306端口：

       bash

       复制

       下载

       iptables -L -n | grep 3306

     • 使用telnet或nc测试网络连通性：

       bash

       复制

       下载

       telnet <MySQL_IP> 3306

  3. 检查用户权限：

     • 确认用户是否有远程访问权限：

       sql

       复制

       下载

       SELECT Host, User FROM mysql.user;
       GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
       FLUSH PRIVILEGES;

  4. 检查配置文件：

     • 查看my.cnf中是否绑定到127.0.0.1：

       ini

       复制

       下载

       bind-address = 0.0.0.0    # 允许所有IP连接

---

二、性能类问题

1. 查询缓慢

• 排查步骤：

  1. 开启慢查询日志：

     sql

     复制

     下载

     SET GLOBAL slow_query_log = 'ON';
     SET GLOBAL long_query_time = 2;  -- 定义慢查询阈值（秒）

  2. 分析慢查询：

     • 查看慢查询日志文件路径：

       sql

       复制

       下载

       SHOW VARIABLES LIKE 'slow_query_log_file';

     • 使用mysqldumpslow工具分析日志：

       bash

       复制

       下载

       mysqldumpslow -s t /var/log/mysql/slow.log

  3. 使用EXPLAIN分析SQL执行计划：

     sql

     复制

     下载

     EXPLAIN SELECT * FROM table WHERE condition;

  4. 检查索引缺失：

     • 查看表索引状态：

       sql

       复制

       下载

       SHOW INDEX FROM table_name;

     • 使用OPTIMIZE TABLE优化表：

       sql

       复制

       下载

       OPTIMIZE TABLE table_name;

2. 锁竞争或死锁

• 排查步骤：

  1. 查看当前锁状态：

     sql

     复制

     下载

     SHOW ENGINE INNODB STATUS;  -- 查看事务和锁信息
     SHOW OPEN TABLES WHERE In_use > 0;

  2. 杀死阻塞进程：

     sql

     复制

     下载

     SHOW PROCESSLIST;          -- 找到阻塞的进程ID
     KILL <process_id>;

  3. 设置锁超时（避免长事务）：

     sql

     复制

     下载

     SET GLOBAL innodb_lock_wait_timeout = 30;  -- 默认50秒

---

三、数据一致性问题

1. 表损坏或数据丢失

• 排查步骤：

  1. 检查表状态：

     sql

     复制

     下载

     CHECK TABLE table_name;    -- 检查表是否损坏

  2. 修复表：

     sql

     复制

     下载

     REPAIR TABLE table_name;   -- 仅对MyISAM表有效

  3. 使用innodb_force_recovery（InnoDB引擎）：

     • 修改my.cnf并重启：

       ini

       复制

       下载

       innodb_force_recovery = 1  -- 从1到6逐步尝试（6为最高修复级别）

2. 主从复制错误

• 排查步骤：

  1. 查看复制状态：

     sql

     复制

     下载

     SHOW SLAVE STATUS\G  -- 关注Slave_IO_Running和Slave_SQL_Running

  2. 跳过指定错误（谨慎操作）：

     sql

     复制

     下载

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

  3. 重新同步数据：

     • 使用mysqldump重新导出主库数据并导入从库。

---

四、日志与错误分析

1. 错误日志分析

• 查看错误日志路径：

  sql

  复制

  下载

  SHOW VARIABLES LIKE 'log_error';

• 常见错误码：

  • 1045：权限错误 → 检查用户密码和主机白名单。

  • 2003：连接超时 → 检查网络和防火墙。

  • 1213：死锁 → 优化事务逻辑。

2. 二进制日志（Binlog）恢复

• 恢复误删数据：

  bash

  复制

  下载

  mysqlbinlog --start-datetime="2023-10-01 00:00:00" /var/log/mysql/binlog.000001 | mysql -u root -p

---

五、资源瓶颈排查

1. CPU/内存过高

• 排查步骤：

  1. 查看系统资源：

     bash

     复制

     下载

     top -c          # 查看进程CPU/内存占用
     vmstat 2 5      # 监控系统资源

  2. 优化MySQL配置：

     • 调整innodb_buffer_pool_size（建议为物理内存的70%）。

     • 减少并发连接数（max_connections）。

---

六、备份与恢复

1. 定期备份

• 物理备份：

  bash

  复制

  下载

  innobackupex --user=root --password=xxx /backup/

• 逻辑备份：

  bash

  复制

  下载

  mysqldump -u root -p --all-databases > backup.sql

2. 恢复数据

• 全量恢复：

  bash

  复制

  下载

  mysql -u root -p < backup.sql

---

七、高级故障排查工具

• Percona Toolkit：分析慢查询、死锁和索引问题。

• pt-query-digest：分析慢查询日志。

• mytop：实时监控MySQL线程和查询。

---

八、预防措施

1. 监控告警：使用Prometheus + Grafana监控MySQL状态。

2. 定期优化：每周执行OPTIMIZE TABLE和ANALYZE TABLE。

3. 权限最小化：避免使用root账号运行应用。

4. 测试备份恢复流程：确保备份文件可恢复。

---

通过以上步骤，可系统化定位并解决MySQL的常见故障。对于复杂问题，建议结合日志、监控工具和数据库快照进一步分析。