MySQL 故障排查与性能优化指南

MySQL 故障排查与性能优化指南

在日常管理 MySQL 数据库时，数据库可能会遇到各种故障，而快速有效的故障排查与优化是保证数据库稳定运行的关键。本文将从 MySQL 单实例故障、主从复制故障排查以及性能优化等方面，介绍常见问题的解决方法和数据库性能优化建议。

一、MySQL 单实例故障排查

1. 错误：无法连接 MySQL 服务器

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock' (2)

问题分析：可能是数据库未启动或防火墙阻止了数据库端口。

解决方法：启动 MySQL 或开放防火墙的数据库端口。

---

2. 错误：访问被拒绝

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

问题分析：密码不正确或没有访问权限。

解决方法：通过修改 my.cnf 配置文件，在 [mysqld] 下添加 skip-grant-tables 并重启数据库。然后更新密码并删除 skip-grant-tables 参数。

mysql> use mysql;
mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
mysql> flush privileges;

---

3. 错误：远程连接数据库很慢

问题分析：MySQL 主机查询 DNS 过慢导致。

解决方法：在 my.cnf 中添加 skip-name-resolve，禁用 DNS 解析，重启 MySQL。

---

4. 错误：无法打开表文件

Can't open file: 'xxx_forums.MYI'. (errno: 145)

问题分析：数据库表损坏或文件权限问题。

解决方法：使用 myisamchk 修复表，或通过 phpMyAdmin 执行修复。

---

5. 错误：主机因连接错误被阻塞

ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of many connection errors

问题分析：由于过多连接错误，主机被阻塞。

解决方法：通过以下命令清除连接错误：

mysqladmin flush-hosts

修改 MySQL 配置，增加 max_connect_errors 值：

[mysqld]
max_connect_errors = 1000

---

6. 错误：Too many connections

问题分析：连接数超过 MySQL 的最大连接数。

解决方法：增加 max_connections 值，或临时修改：

set GLOBAL max_connections=10000;

---

7. 错误：配置文件权限错误

Warning: World-writable config file '/etc/my.cnf' is ignored

解决方法：修复配置文件权限：

chmod 644 /etc/my.cnf

---

8. 错误：InnoDB 数据文件损坏

InnoDB: Error: page 14178 log sequence number 29455369832 is in the future!

解决方法：在 my.cnf 中添加 innodb_force_recovery=4，启动 MySQL 并备份数据后删除该参数。

---

二、MySQL 主从复制故障排查

1. 从库 I/O 线程停止

Slave_IO_Running = NO

问题分析：主库和从库的 server-id 相同。

解决方法：修改从库的 server-id 并重启同步。

---

2. 从库主键冲突导致同步停止

问题分析：主键冲突或数据不一致。

解决方法一：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

解决方法二：设置从库为只读模式：

set global read_only=true;

---

3. 中继日志损坏

Error initializing relay log position: I/O error reading the header from the binary log

解决方法：重新找到 binlog 和 pos 点并重新同步：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx', MASTER_LOG_POS=xxx;

---

三、MySQL 优化

1. 硬件优化

• CPU：推荐使用多核 CPU，如 Intel Xeon。
• 内存：建议 MySQL 服务器内存至少 4GB，性能较好。
• 磁盘：选择高转速硬盘或 SSD，避免使用 RAID-5，推荐 RAID-0+1。

2. MySQL 配置优化

优化参数示例：

[mysqld]
default-time-zone = '+8:00'
interactive_timeout = 120
wait_timeout = 120
open_files_limit = 10240
max_connections = 5000
max_connect_errors = 6000
table_open_cache = 2048
sort_buffer_size = 512M
query_cache_size = 512M
innodb_buffer_pool_size = 20480M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

3. 二进制日志优化

log-bin = mysql-bin
sync_binlog = 1
binlog_format = mixed
expire_logs_days = 7

4. InnoDB 引擎优化

innodb_buffer_pool_size = 20480M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_max_dirty_pages_pct = 90

5. MyISAM 引擎优化

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

总结

ble = 1
innodb_max_dirty_pages_pct = 90

### 5. MyISAM 引擎优化
```bash
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

总结

本篇文章介绍了 MySQL 常见故障的排查方法和性能优化的配置建议。在日常的数据库管理中，确保数据库的稳定性和高性能是每一位 DBA 必须掌握的技能。通过优化硬件、调整 MySQL 配置和对症排查故障，可以大幅提高 MySQL 数据库的运行效率并减少宕机的可能性。