MySQL故障排查

目录

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配置文件

核心性能优化项

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

架构优化

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

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