MySQL 常见故障排查

MySQL 故障排查与生产环境优化全指南

在生产环境中，MySQL 的稳定运行直接关系到业务连续性与用户体验。本文从实际运维场景出发，系统梳理单实例与主从架构下的常见故障排查方法，并从硬件配置、参数优化、SQL 调优等维度提供全方位的性能优化策略，助力构建高效可靠的 MySQL 运行环境。

一、单实例 MySQL 故障排查实战

1. 连接类故障解析

• ERROR 2002 (HY000)：无法通过 socket 连接本地服务器。
  多因数据库未启动、socket 文件路径配置错误或端口被防火墙拦截。解决方式：启动服务（systemctl start mysqld）、检查my.cnf中socket参数配置，或通过firewall-cmd开放 3306 端口。
• ERROR 1045 (28000)：用户权限或密码错误。
  需重置密码：在my.cnf的[mysqld]段添加skip-grant-tables=on，重启后无密码登录，执行ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'，最后移除配置并重启。
• 远程连接缓慢：DNS 解析延迟是主因。
  禁用 DNS 解析可彻底解决：在my.cnf添加skip-name-resolve，重启服务后连接速度显著提升，但需注意授权语句中不可再使用主机名（仅支持 IP）。

2. 数据完整性与权限故障

• 表文件损坏（errno: 145）：常由非正常关机、磁盘满或文件权限异常导致。
  MyISAM 表修复：使用myisamchk -r /var/lib/mysql/数据库名/表名.MYI；InnoDB 表可通过innodb_force_recovery=4启动后备份数据，再重建表结构。
• 主机被阻塞（ERROR 1129）：因连接错误次数超过max_connect_errors阈值（默认 10 次）。
  临时解决：执行mysqladmin -uroot -p flush-hosts；永久方案：在my.cnf设置max_connect_errors=1000，重启服务后生效。
• 连接数超限（Too many connections）：默认最大连接数（151）无法满足高并发需求。
  临时调整：set GLOBAL max_connections=10000；永久生效需在my.cnf中配置该参数，同时结合thread_cache_size（建议 100）减少连接创建开销。

二、主从复制故障排查要点

1. 基础同步异常

• server-id 冲突：主从库server-id相同会导致 Slave IO 线程终止。
  解决：修改从库my.cnf中server-id为唯一值（如主库 1，从库 2），重启从库后执行start slave。
• 数据一致性冲突：常见错误码 1062（主键冲突）、1032（从库记录不存在）。
  临时跳过错误：stop slave; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; start slave；根治需通过数据校验工具（如 pt-table-checksum）确保主从数据一致。
• 中继日志损坏：报错 “I/O error reading the header from the binary log”。
  重建同步：stop slave; change master to master_log_file='主库当前binlog文件', master_log_pos=具体位置; start slave，位置信息可从主库show master status获取。

三、生产环境优化策略

1. 硬件层优化

• CPU：选择 SMP 架构多路 CPU（如 2 颗 Intel Xeon），避免单线程瓶颈，高并发场景优先保障核数（建议 8 核以上）。
• 内存：最低 4GB，推荐 32GB 以上，确保 InnoDB 缓冲池（innodb_buffer_pool_size）可容纳热点数据（建议物理内存的 50%-70%）。
• 磁盘：优先采用 RAID 0+1 阵列（兼顾性能与冗余），或 SSD 硬盘提升随机 I/O 性能，避免 RAID 5（写性能差）。

2. 配置文件核心参数调优

类别	参数	作用与建议配置
事务安全	innodb_flush_log_at_trx_commit	1（默认，完全持久化）；高并发可设 2（每秒刷盘）
连接管理	max_connections	500-2000（根据业务峰值调整）
临时表	tmp_table_size/max_heap_table_size	均设为 128M，避免临时表写入磁盘
慢查询监控	slow_query_log	开启（ON），long_query_time=2秒
InnoDB 性能	innodb_io_capacity	SSD 设 2000-4000，HDD 设 200-400
自增锁	innodb_autoinc_lock_mode	2（连续模式，提升批量插入性能）

3. SQL 与索引优化实践

• 避免全表扫描：通过EXPLAIN分析执行计划，重点关注type列（ALL表示全表扫描），为过滤条件字段创建索引（如name列添加idx_name）。
• 索引设计原则：优先创建联合索引（遵循最左前缀原则），避免冗余索引；对于大表，ALTER TABLE添加索引建议在业务低峰期执行。
• 慢查询优化：启用慢查询日志定位低效 SQL，通过改写查询（如避免SELECT *、拆分大事务）、调整 Join 顺序等方式降低资源消耗。

四、总结与最佳实践

MySQL 故障排查需遵循 “现象→日志→原理” 的分析路径，善用错误日志、show processlist等工具定位根因；而优化则需从硬件到 SQL 进行全链路规划：硬件层保障资源冗余，配置层平衡性能与安全，SQL 层通过索引与执行计划优化提升效率。

生产环境中，建议定期执行：

1. 数据一致性校验（主从架构）；
2. 慢查询日志分析与优化；
3. 配置参数合理性审查。

通过系统化的故障预防与性能调优，可显著降低 MySQL 运行风险，为业务提供稳定高效的数据支撑。