MySQL故障排查

故障现象 1：ERROR 2002 (HY000): Can't connect to local MySQL server through socket
- 原因：数据库未启动、socket 文件路径错误或防火墙拦截端口。
- 解决：启动服务（systemctl start mysql）、检查配置文件socket路径、开放端口（如firewall-cmd --add-port=3306/tcp）。
故障现象 2：ERROR 1045 (28000): Access denied for user
- 原因：密码错误或权限不足。
- 解决：
  1. 在my.cnf中添加skip-grant-tables=on，重启数据库。
  2. 修改密码（MySQL 8.0 需用ALTER USER语句）。
  3. 移除skip-grant-tables，重启并重新授权。
故障现象 3：远程连接缓慢
- 原因：DNS 解析延迟（开发环境无法连接外网）。
- 解决：在my.cnf中添加skip-name-resolve，禁止 DNS 解析（授权时需用 IP 而非主机名）。

2. 表损坏与权限故障

故障现象 4：Can't open file: 'xxx.MYI' (errno: 145)
- 原因：非正常关机、磁盘满或文件属组错误。
- 解决：
  - 修复表：myisamchk -r /路径/表名.MYI 或通过 phpMyAdmin 修复。
  - 检查文件属组：确保归属于 MySQL 用户（chown mysql:mysql 文件名）。
故障现象 5：Host 'xxx' is blocked because of many connection errors
- 原因：连接错误次数超过max_connect_errors（默认 10 次）。
- 解决：mysqladmin flush-hosts 清除阻塞，或增大max_connect_errors参数。

3. 其他常见故障

故障现象 6：Too many connections
- 原因：连接数超过max_connections限制。
- 解决：临时调整set GLOBAL max_connections=10000，或修改my.cnf永久生效。
故障现象 7：Warning: World-writable config file '/etc/my.cnf' is ignored
- 原因：配置文件权限错误（需为 644）。
- 解决：chmod 644 /etc/my.cnf。
故障现象 8：InnoDB: Error: page ... is in the future!
- 原因：InnoDB 数据文件损坏。
- 解决：在my.cnf中添加innodb_force_recovery=4启动，备份数据后恢复。

二、MySQL 主从故障排查

1. 基础配置问题

故障现象 1：从库Slave_IO_Running为NO，提示server ids must be different
- 原因：主从库server-id重复。
- 解决：修改从库server-id为唯一值（如主库 1，从库 2），重启后重新同步。

2. 数据同步异常

故障现象 2：从库Slave_IO_Running为NO，报错1007/1032/1062等
- 原因：主键冲突、主库数据删除 / 更新导致从库找不到记录。
- 解决：
  - 跳过错误：stop slave; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; start slave;
  - 设置从库只读：set global read_only=true（防止误操作）。
故障现象 3：Error initializing relay log position
- 原因：中继日志（relay-bin）损坏。
- 解决：手动指定主库 binlog 文件和位置，重新同步：
  sql
```
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx', MASTER_LOG_POS=xxx;
```

三、MySQL 性能优化策略

1. 硬件优化

CPU：推荐多路对称 CPU（如 4 核以上），支持多线程处理。
内存：分配物理内存的 50%-70% 给 InnoDB 缓冲池（innodb_buffer_pool_size），建议至少 4GB。
磁盘：使用 SSD 或 RAID-0+1 提升 I/O 性能（避免 RAID-5），定期检查磁盘健康。

2. 配置文件优化

参数	作用	建议配置	注意事项
`innodb_buffer_pool_size`	缓存数据和索引，提升读性能	物理内存的 50%-70%（如 64GB 设 40G）	避免超过内存，防止 Swap
`innodb_log_file_size`	事务日志文件大小，影响恢复速度	单文件 1-4GB（总大小不超过缓冲池）	修改需重启，删除旧日志后生效
`innodb_flush_log_at_trx_commit`	日志刷新策略	1（强一致性）/2（折中）	高并发可设 2，但容忍 1 秒数据丢失
`max_connections`	最大连接数	500-2000	结合`Threads_connected`监控调整
`slow_query_log`	慢查询日志开启	`ON`	记录执行超过`long_query_time`的 SQL
`binlog_format`	二进制日志格式	`ROW`	主从复制推荐，数据一致性更高

3. SQL 优化

索引优化：
- 使用EXPLAIN分析执行计划，确保type为range/ref而非ALL（全表扫描）。
- 避免在索引列使用函数（如WHERE DATE(created_at) = '2023-01-01'），导致索引失效。
查询改写：
- 子查询转连接：SELECT * FROM A WHERE id IN (SELECT id FROM B) → SELECT A.* FROM A JOIN B ON A.id = B.id。
- 批量操作：INSERT INTO users (...) VALUES (...), (...), ... 减少交互次数。
慢查询分析：
- 开启慢查询日志（slow_query_log=ON），设置阈值（long_query_time=1秒），定位低效 SQL。

四、优化示例（my.cnf 配置片段）

[mysqld]
# 核心配置
innodb_buffer_pool_size = 40G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
max_connections = 1000
thread_cache_size = 100

# 查询优化
tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 4M
join_buffer_size = 8M

# 日志与监控
slow_query_log = ON
long_query_time = 1
log_error = /var/log/mysql/error.log
binlog_format = ROW
expire_logs_days = 7

# InnoDB 高级优化
innodb_io_capacity = 2000  # SSD场景
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2  # 高并发插入优化

故障排查核心思路：从连接、权限、表损坏、主从配置等维度逐层分析，结合日志（错误日志、慢查询日志）定位问题。
优化方向：硬件提供基础性能，配置参数合理分配资源，SQL 优化直击效率瓶颈，三者需结合业务场景动态调整。

继续

以下是对 MySQL 故障排查与优化的进一步补充，涵盖索引优化细节、主从复制进阶问题、监控工具及实战建议：

五、索引优化深度解析

1. 索引失效场景与规避

场景 1：模糊查询前置百分号
```
SELECT * FROM users WHERE name LIKE '%user'; -- 全表扫描，索引失效
```
优化：改用覆盖索引或调整业务逻辑（如限制搜索长度）。
场景 2：数据类型不匹配

sql
```
SELECT * FROM orders WHERE order_id = '123'; -- order_id 为 INT 类型，隐式转换导致索引失效
```
优化：确保查询条件与字段类型一致（避免字符串与数值混合）。
场景 3：OR 条件跨非索引列
```
SELECT * FROM products WHERE category_id=1 OR brand='XYZ'; -- 若 brand 无索引，全表扫描
```
优化：为brand添加索引，或改用联合索引(category_id, brand)。

2. 联合索引设计原则

最左匹配原则：联合索引(a, b, c)可匹配a、a+b、a+b+c的查询条件。

-- 有效利用索引
SELECT * FROM orders WHERE user_id=100 AND order_date>'2023-01-01'; -- 联合索引 (user_id, order_date)

选择性高的列优先：将过滤性强的列（如唯一标识、枚举值）放在索引左侧。

-- 推荐：user_id 选择性高，order_date 选择性低
CREATE INDEX idx_user_date ON orders (user_id, order_date);

避免冗余索引：若已有索引(a, b)，无需再创建单独的(a)索引（除非查询仅用a）。

六、主从复制进阶问题

1. 主从延迟优化

现象：从库Seconds_Behind_Master持续大于 0，数据同步延迟。
原因：
- 主库写入压力大，从库 I/O 或 SQL 线程处理慢。
- 从库硬件性能不足（如磁盘 I/O 瓶颈）。
- 主库执行大事务（如批量更新），从库回放耗时。
解决：
- 硬件层面：为从库配置更高性能的磁盘（如 SSD），增加内存。
- 配置层面：
  - 启用从库多线程复制（MySQL 5.7+）：
    ini
```
slave-parallel-workers=4  # 根据CPU核数调整
slave-parallel-type=LOGICAL_CLOCK  # 基于组提交的并行复制
```
  - 增大从库中继日志缓存：relay_log_buffer_size=64M。
- 业务层面：拆分大事务为小事务，避免主库长时间锁表。

2. 主从数据不一致修复

场景：主从库数据因误操作（如从库写入）或同步中断导致不一致。
修复步骤：
1. 停止主从同步：STOP SLAVE;
2. 在主库执行FLUSH TABLES WITH READ LOCK;，确保数据一致性。
3. 备份主库数据（如mysqldump），恢复到从库。
4. 重置从库同步位点，指向主库当前 binlog：
  sql
```
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx', MASTER_LOG_POS=xxx;
START SLAVE;
```
5. 释放主库锁：UNLOCK TABLES;

七、监控与诊断工具

1. 内置工具

SHOW STATUS：查看数据库运行状态（如连接数、慢查询次数）。

SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
SHOW STATUS LIKE 'Slow_queries'; -- 慢查询总数

SHOW ENGINE INNODB STATUS：分析 InnoDB 引擎状态（锁等待、事务信息）
```
SHOW ENGINE INNODB STATUS\G; -- 查看最新事务和锁信息
```
pt-query-digest（Percona Toolkit）：分析慢查询日志，生成统计报告。
```
pt-query-digest /var/log/mysql/slow-query.log > slow_report.txt
```

2. 可视化工具

Prometheus + Grafana：监控 MySQL 指标（如 QPS、TPS、缓存命中率）。
- 关键指标：
  - innodb_buffer_pool_read命中率 = (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
  - 连接利用率 = (Threads_connected / max_connections) * 100%
MySQL Workbench：执行计划分析、服务器管理、主从拓扑监控。

八、生产环境实战建议

1. 变更管理

灰度发布：重大 SQL 变更（如加索引、改表结构）先在测试环境验证，再逐步推送到生产。
事务控制：避免长事务，业务代码中尽量缩短事务范围（如先获取锁，再查询数据）。

2. 备份策略

全量备份：每周一次mysqldump --all-databases或物理备份（如Percona XtraBackup）。
增量备份：每日基于二进制日志（binlog）备份，恢复时结合全量备份点。
验证备份：定期恢复备份到测试环境，确保可用性。

3. 安全加固

权限最小化：禁止root用户远程登录，为应用创建独立账号（仅授予必要权限）。

sql

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'app_user'@'localhost';

加密传输：启用 SSL 连接（require_secure_transport=ON），防止数据明文传输。

九、常见误区与避坑指南

过度使用查询缓存：
- MySQL 8.0 已移除查询缓存，旧版本中高并发场景下缓存失效会导致性能下降，建议关闭（query_cache_type=OFF）。
忽略字符集一致性：
- 确保服务器、数据库、表、字段四级字符集一致（如utf8mb4），避免乱码问题。
```
SET NAMES utf8mb4; -- 客户端连接时设置
```
主从配置疏忽：
- 主库必须开启二进制日志（log_bin=ON），从库需配置唯一server-id，避免auto_increment冲突（主库设为 1，从库设为 2，步长 2）

转载自CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/2501_91112123/article/details/148069870