MySQL 故障排查与性能优化指南
文章目录
- MySQL 故障排查与性能优化指南
- 一、MySQL 单实例故障排查
- 1. 错误：无法连接 MySQL 服务器
- 2. 错误：访问被拒绝
- 3. 错误：远程连接数据库很慢
- 4. 错误：无法打开表文件
- 5. 错误：主机因连接错误被阻塞
- 6. 错误：Too many connections
- 7. 错误：配置文件权限错误
- 8. 错误：InnoDB 数据文件损坏
- 二、MySQL 主从复制故障排查
- 1. 从库 I/O 线程停止
- 2. 从库主键冲突导致同步停止
- 3. 中继日志损坏
- 三、MySQL 优化
- 1. 硬件优化
- 2. MySQL 配置优化
- 3. 二进制日志优化
- 4. InnoDB 引擎优化
- 5. MyISAM 引擎优化
- 总结
- 总结
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 数据库的运行效率并减少宕机的可能性。