MYSQL故障排查和环境优化

一、MySQL故障排查

1. 单实例常见故障

（1）连接失败类问题

• ERROR 2002 (HY000): Can't connect to MySQL server
  原因：MySQL未启动或端口被防火墙拦截。
  解决：启动MySQL服务（systemctl start mysqld）或开放端口（如3306）367。

• ERROR 1045 (28000): Access denied
  原因：密码错误或权限不足。
  解决：修改配置文件添加skip-grant-tables，重启后重置密码并授权367。

（2）性能与资源类问题

• Too many connections
  原因：连接数超出max_connections限制。
  解决：临时调整（SET GLOBAL max_connections=10000;）或永久修改配置文件36。

• Host blocked due to connection errors
  原因：同一IP频繁连接失败超过max_connect_errors阈值。
  解决：执行mysqladmin flush-hosts或增大max_connect_errors值36。

（3）数据损坏与表修复

• 表文件损坏（如MYI文件错误）
  原因：非正常关机或磁盘空间不足。
  解决：使用myisamchk -r修复或通过phpMyAdmin修复表36。

• InnoDB数据文件损坏
  解决：设置innodb_force_recovery=4启动数据库，备份后重建表37。

2. 主从复制故障

• Slave_IO_Running为NO
  原因：主从server-id重复或网络中断。
  解决：修改从库server-id并重启同步367。

• Slave_SQL_Running为NO（主键冲突）
  解决：跳过错误（SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;）或设置从库只读36。

• 中继日志损坏
  解决：重新指定同步点（CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;）36。

二、生产环境优化策略

1. 硬件与存储优化

• CPU：选择多核高主频处理器（如Intel Xeon），支持高并发处理17。

• 内存：建议至少4GB，优先分配大内存给innodb_buffer_pool_size（占物理内存70%~80%）157。

• 磁盘：使用SSD或RAID 10阵列，避免RAID 5（写性能差）157。

2. 查询与索引优化

• 索引设计

  • 选择性高列优先：如用户ID而非性别字段25。

  • 覆盖索引：避免回表查询（如SELECT name FROM users WHERE name='Alice'）25。

  • 复合索引：遵循最左前缀原则（如索引(col1,col2)需条件包含col1）25。

• SQL语句优化

  • 避免全表扫描：使用EXPLAIN检查执行计划29。

  • 分页优化：用WHERE id > 1000 LIMIT 10替代LIMIT 1000,1025。

  • 批量操作：合并INSERT语句减少事务开销25。

3. 配置参数调优

• 缓冲池：innodb_buffer_pool_size设置为物理内存的70%~80%57。

• 连接管理：调整max_connections（默认151）和wait_timeout（建议120秒）57。

• 日志配置：开启慢查询日志（slow_query_log=ON）并设置合理阈值（long_query_time=1）25。

4. 架构与维护优化

• 读写分离：通过主从复制分离读/写流量，使用中间件（如ProxySQL）1410。

• 分库分表：按业务拆分大表，减少单表压力14。

• 定期维护：

  • 使用OPTIMIZE TABLE减少碎片57。

  • 监控工具：Percona Monitoring或Prometheus+Grafana10。

三、高级技巧与注意事项

• 关闭DNS解析：在配置中添加skip-name-resolve提升连接速度（需改用IP授权）8。

• 磁盘调度策略：设置为deadline或noop（SSD适用）以提高I/O效率8。

• 网络优化：调整TCP缓冲队列（net.ipv4.tcp_max_syn_backlog）和随机端口范围8。