当前位置：首页 > news >正文

MySQL故障排查

news 来源：原创 2025/7/26 15:15:20

MySQL 单示例故障排查

故障现象一

故障现象二

故障现象三

故障现象四

故障现象五

故障现象六

故障现象七

故障现象八

MySQL主从复制排查

故障现象一

故障现象二

故障现象三

MySQL 优化

硬件方面

关于CPU

关于内存

关于磁盘

MySQL配置文件

核心性能优化项

架构优化

SQL方面

索引优化

MySQL 单示例故障排查

故障现象一

问题分析：以上这种情况一般都是数据库未启动、mysql 配置文件未指定 socket文件或者数据库端口被防火墙拦截导致。

解决方法：启动数据库或者防火墙开放数据库监听端口。

故障现象二

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

解决方法：

修改 my.cnf 主配置文件，在[mysqld]下添加 skip-grant-tables=on，重启数据库。最后修改密码命令如下。

MySQL 5.7版本

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

MySQL 8.0版本

mysql> UPDATE mysql.user SET authentication string='' WHERE user='root'
AND Host='localhost';
mySqI> FLUSH PRIVILEGES :
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

再删除刚刚添加的 skip-grant-tables 参数，重启数据库，使用新密码即可登录。重新授权，命令如下。

MySQL5.7版本

mysql> grant all on *.* to 'root'@'mysql-server' identified by '123456';

MySQL8.0v版本

mysql> CREATE USER 'root'@'mysql-server' IDENTIFIED BY '123456';
mysql> GRANT all ON *.* TO root'@'mysql-server';

故障现象三

在使用远程连接数据库时偶然会发生远程连接数据库很慢的问题。

问题分析：如果 MySQL 主机查询 DNS 很慢或是有很多客户端主机时会导致连接很慢，由于开发机器是不能够连接外网的，在进行MySQL连接时，DNS 解析是不可能完成的，从而也就明白了为什么连接那么慢了。

解决方法：修改 my.cnf 主配置文件，在[mysqld]下添加 skip-name-resolve,重启数据库可以解决。注意在以后授权里面不能再使用主机名授权。

故障现象四

Can't open file:'xxx forums.Myl'.(errno: 145)

问题分析：

服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造成了损坏。
可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个错误。

解决方法：

可以使用下面的两种方式修复数据表(第一种方法仅适合独立主机用户)：

使用 MySQL 自带的专门用户数据表检查和修复工具 myisamchk。一般情况下只有在命令行下面才能运行 myisamchk 命令。常用的修复命令为：

myisamchk -r 数据文件目录/数据表名, MYI;

通过 phphlyAdmin 修复，phpMyAdmin 带有修复数据表的功能，进入到某一个表中后，点击“操作”，在下方的“表维护”中点击“修复表”即可。

注意：以上两种修复方式在执行前一定要备份好数据库。

修改文件的属组(仅适合独立主机用户)。

复制数据库文件的过程中没有将数据库文件设置为 MySQL 运行的帐号可读写(一般适用于 Linux 和 FreeBsD用户)

故障现象五

ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of many
connection errors;
unblock with 'mysqladmin flush-hosts'

问题分析：由于 mysql 数据库的参数:max_connect_errors，其默认值是 10。当大量(max connect_errors)的主机去连接 MySQL，总连接请求超过了 10 次，新的连接就再也无法连接上 MySQL 服务。同一个 ip 在短时间内产生太多中断的数据库连接而导致的阻塞(超过 mysq1 数据库 max connection errors 的最大值) 。

解决方法：

使用 mysqladmin flush-hosts 命令清除缓存，命令执行方法如下：

mysqladmin -uroot -p -h 192.168.10.101 flush-hosts
Enter password:

修改 mysql 配置文件，在[mysqld]下面添加 max connect errors=1000,然后重启 MySQL。

故障现象六

客户端报 Too many connections。

问题分析：连接数超出MySQL的最大连接数限制。

解决方法：

在 my.cnf 配置文件里面增大连接数，然后重启 MySQL 服务

max_connections = 10000

临时修改最大连接数，重启后不生效。需要在 my.cnf 里面修改配置文件，下次重启生效。

set GLOBAL max connections=10000;

故障现象七

Warning: World-writable config file '/etc/my.cnf' is ignored
ERROR! MySQl is running but PID file could not be found

问题分析：MySQL的配置文件/etc/my.cnf 权限不对。

解决方法：

chmod 644 /et/my.cnf

故障现象八

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

问题分析：innodb 数据文件损坏。

解决方法: 修 my.cnf 配置文件,在[mysqld]下添加 innodb force recovery=4,启动数据库后备份数据文件，然后去掉该参数，利用备份文件恢复数据。

MySQL主从复制排查

故障现象一

从库的 Slave_I0_Running为NO
The slave I/0 thread stops because master and slave have equal MySQL serverids; these ids must be different for replication to work (or the-replicate-same-server-id option must be used on slave but this does notalways make sense; please check the manual before using it).

问题分析：主库和从库的 server-id 值一样

解决方法：修改从库的 server-id 的值，修改为和主库不一样。修改完后重启，再同步即可。

故障现象二

从库的 Slave_I0_Running为NO

问题分析：造成从库线程为N0的原因会有很多，主要原因是主键冲突或者主库删除或更新数据，从库找不到记录，数据被修改导致。通常状态码报错有 1007、1032、1062、1452等。

解决方法一：

mysql> stop slave;
mysql> Set GLOBAL SQL_SLAVE_SKIP_COUNTER=1:
mysql> start slave;

解决方法二：

设置用户权限，设置从库只读权限

set global read only=true;

故障现象三

Error initializing relay log position: I/0 error reading the header fromthe binary log

分析问题： 从库的中继日志 relay-bin 损坏。

解决方法：手工修复，重新找到同步的 binlog 和 pos 点，然后重新同步即可。

mysqI>CHAN GEMASTER TO MASTER LOG FILE='mysql-bin.xxx’,MASTER LOG POS=xxx;

MySQL 优化

硬件方面

服务器最主要的无非就是CPU、内存、硬盘三大关键因素。

关于CPU

CPU 对于 MySQL 应用，推荐使用 S.M.P.架构的多路对称 CPU。例如:可以使用两颗 Intel Xeon 3.6GHz的 CPU。现在比较推荐用 4U 的服务器来专门做数据库服务器，不仅仅是针对于 MySQL。

关于内存

物理内存对于一台使用 MySQL的 Database Server 来说，服务器内存建议不要小于 2GB，推荐使用 4GB 以上的物理内存。不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了32G.

关于磁盘

磁盘寻道能力(磁盘 I/0)。以目前市场上普遍高转速 SAS 硬盘(15000 转/秒)为例，这种硬盘理论上每秒寻道 15000 次，这是物理特性决定的，没有办法改变。 MySQL 每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以通常认为磁盘 I/0 是制约 MySQL 性能的最大因素之一，通常是使用RAID-0+1 磁盘阵列，注意不要尝试使用 RAID-5，MySQL 在 RAID-5 磁盘阵列上的效率并不高。如果不考虑硬件的投入成本，也可以考虑固态(SSD)硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多。

MySQL配置文件

核心性能优化项

内存相关
- innodb_buffer_pool_size：缓存数据和索引。
- innodb_log_buffer_size：增大日志缓冲区，减少磁盘写入。
- sort_buffer_size、join_buffer_size：优化排序和JOIN操作。
连接与线程
- max_connections：根据并发需求调整，避免资源耗尽。
- thread_cache_size：缓存线程，减少线程创建开销。
I/O相关
- innodb_flush_log_at_trx_commit：权衡数据安全性和性能（1为安全，2为性能）。
- innodb_io_capacity：根据磁盘性能调整，优化后台I/O操作。
日志与监控
- 开启慢查询日志（slow_query_log），定位慢查询。
- 使用pt-query-digest分析慢查询日志，优化高频低效查询。

架构优化

读写分离
- 主库负责写操作，从库负责读操作，提升并发能力。
- 使用中间件（如MySQL Router、ProxySQL）实现自动路由。
分库分表
- 水平分表：按用户ID、时间等维度拆分数据。
- 垂直分库：按业务模块拆分数据库，减少单库负载。
缓存策略
- 使用Redis等缓存热点数据，减少数据库压力。
- 实施多级缓存（如本地缓存+分布式缓存）。
消息队列削峰
- 使用Kafka、RabbitMQ等队列缓冲高并发写入，平滑数据库压力。

SQL方面

避免全表扫描

案例：

-- 低效：无WHERE条件，全表扫描
SELECT * FROM orders;-- 高效：添加索引字段过滤
SELECT * FROM orders WHERE user_id = 1001;

策略：
- 确保WHERE、JOIN、ORDER BY、GROUP BY中的字段有索引。
- 使用LIMIT限制结果集大小，避免返回冗余数据。

少返回数据量

案例：

-- 低效：返回所有字段
SELECT * FROM products;-- 高效：只查询必要字段
SELECT id, name, price FROM products WHERE stock > 0;

策略：
- 避免SELECT *，明确指定字段名。
- 使用分页（LIMIT offset, size）处理大数据集。

优化JOIN操作

案例：

-- 低效：JOIN表过多或无索引
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
JOIN products p ON o.product_id = p.id;-- 高效：减少JOIN表数量，确保JOIN字段有索引
SELECT o.id, o.amount, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'completed';

策略：
- 优先使用内连接（INNER JOIN），避免外连接（LEFT/RIGHT JOIN）除非必要。
- 确保JOIN字段有索引，且数据类型一致（如INT vs VARCHAR会导致隐式转换）。

索引优化

合理创建索引

案例：

-- 为高频查询字段创建索引
CREATE INDEX idx_user_email ON users(email);-- 复合索引示例（遵循最左前缀原则）
CREATE INDEX idx_order_user_status ON orders(user_id, status);

策略：
- 为WHERE、JOIN、ORDER BY、GROUP BY中的字段创建索引。
- 复合索引的字段顺序应与查询条件匹配（如WHERE user_id = 1 AND status = 'active'）。

避免索引失效

案例：

-- 低效：索引列使用函数或隐式转换
SELECT * FROM users WHERE YEAR(create_time) = 2025;  -- 索引失效
SELECT * FROM orders WHERE user_id = '123';         -- 字符串与INT比较，隐式转换-- 高效：避免函数操作，明确类型
SELECT * FROM users WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM orders WHERE user_id = 123;           -- 明确INT类型

策略：
- 避免在索引列上使用函数（如UPPER()、YEAR()）。
- 确保比较值的数据类型与字段类型一致。

覆盖索引

案例：

-- 覆盖索引示例（查询字段全部包含在索引中）
SELECT user_id, status FROM orders WHERE user_id = 1001;  -- 假设有索引(user_id, status)

策略：
- 设计索引时覆盖查询所需字段，避免回表操作。

MySQL 单示例故障排查

故障现象一

故障现象二

故障现象三

故障现象四

故障现象五

故障现象六

故障现象七

故障现象八

MySQL主从复制排查

故障现象一

故障现象二

故障现象三

MySQL 优化

硬件方面

关于CPU

关于内存

关于磁盘

MySQL配置文件

核心性能优化项

架构优化

SQL方面

索引优化

相关文章：