Mysql故障排查与生产环境优化

一、故障排查常用方法

1. 连接问题排查

• 检查网络与端口：

  telnet <host> <port> # 测试端口连通性 ping <host> # 测试网络连通性

• 检查 MySQL 服务状态：

  systemctl status mysql # Linux 系统 # 或查看进程 ps -ef | grep mysql

• 检查错误日志：

  SHOW VARIABLES LIKE 'log_error'; -- 查看日志路径

  常见错误：权限不足（Access denied）、最大连接数满（Too many connections）。

2. 性能问题排查

• 查看当前连接与进程：

  SHOW PROCESSLIST; -- 查看正在执行的线程 SHOW FULL PROCESSLIST; -- 查看完整 SQL 语句

• 分析慢查询：

  -- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 定义慢查询阈值（秒） SHOW VARIABLES LIKE 'slow_query_log%'; -- 查看日志路径

• 检查锁等待：

  SHOW ENGINE INNODB STATUS; -- 查看 InnoDB 引擎状态（含锁信息）

3. 主从复制问题排查

• 检查复制状态：

  SHOW SLAVE STATUS\G -- 查看 Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes

• 常见错误：
  • 主从数据不一致（Last_SQL_Error）
  • 网络中断导致的连接失败
  • 二进制日志（binlog）损坏

二、生产环境优化策略

1. 配置文件优化（my.cnf）

[mysqld] # 内存相关 innodb_buffer_pool_size = 70-80% 物理内存 # InnoDB 缓冲池（最重要） innodb_log_file_size = 256M-1G # 日志文件大小 innodb_flush_log_at_trx_commit = 1 # 事务持久性（0/1/2 权衡） # 连接与并发 max_connections = 500-2000 # 最大连接数 thread_cache_size = 64 # 线程缓存 # 慢查询与日志 slow_query_log = 1 long_query_time = 2 log_queries_not_using_indexes = 1 # 记录未使用索引的查询

2. SQL 与索引优化

• 添加合适索引：

  -- 查看索引使用情况 SHOW INDEX FROM table_name; -- 创建复合索引（遵循最左前缀原则） CREATE INDEX idx_col1_col2 ON table_name(col1, col2);

• 优化查询语句：
  • 避免SELECT *，只查需要的字段
  • 避免在WHERE子句中使用函数或表达式
  • 使用EXPLAIN分析执行计划：

    EXPLAIN SELECT * FROM table_name WHERE col = 'value';

3. 架构优化

• 读写分离：通过主从复制实现，主库写、从库读。
• 分库分表：
  • 垂直拆分：按业务拆分表到不同库
  • 水平拆分：将大表数据拆分到多个表（如按 ID 哈希或范围）
• 引入缓存：使用 Redis 等缓存热点数据，减少数据库压力。

三、日常运维建议

1. 定期备份：

   mysqldump -u root -p --single-transaction --routines --triggers db_name > backup.sql

2. 监控与告警：
   • 关注指标：QPS、TPS、连接数、缓冲池命中率、慢查询数量
   • 工具：Prometheus + Grafana、Percona Monitoring and Management (PMM)
3. 版本升级：保持 MySQL 版本稳定，定期升级小版本修复 Bug。

四、典型故障案例

• 死锁：通过SHOW ENGINE INNODB STATUS查看最近的死锁日志，优化事务逻辑。
• 主从延迟：检查从库配置（如sync_binlog、innodb_flush_log_at_trx_commit），或使用并行复制。
• 内存 OOM：调整innodb_buffer_pool_size，避免内存溢出