MySQL 故障排查与生产环境优化-CSDN博客

本文链接：https://blog.csdn.net/2501_91344566/article/details/148060185

一、MySQL 单实例故障排查

（2）使用EXPLAIN 进行SQL 优化的步骤及实验验证

（3）优化步骤：添加索引

（4）优化后查询及 EXPLAIN 分析

一、MySQL 单实例故障排查

1、故障现象 1

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

问题分析：

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

解决方法：

启动数据库或者防火墙开放数据库监听端口。

2、故障现象 2

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

问题分析：

密码不正确或者没有权限访问。

解决方法：

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

MySQL5.7版本

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

MySQL8.0

update mysql.user set authentication_string='' where user='root' and host='localhost';
flush privileges;
alter user 'root'@'localhost' identified by '123456';

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

MySQL5.7

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

MySQL8.0

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

3、故障现象 3

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

问题分析：

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

解决方法：

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

4、故障现象 4

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

问题分析：

服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造成了损坏。

可能是操作系统下直接将数据库文件拷贝移动，会因为文件的属组问题而产生这个错误。

解决方法：

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

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

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

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

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

修改文件的属组（仅适合独立主机用户）：

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

5、故障现象 5

ERROR 1129 （HYOOO）"Host ‘xxx.xxx.xxx.xxx’ is blocked because of many connection errors;

unblock with 'mysqladmin flush-hostss'

问题分析：

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

解决方法：

使用mysqladmin flush-hosts 命令清除缓存：

mysqladmin -uroot -p123456 flush-hosts

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

6、故障现象 6

客户端报 Too many connections。

问题分析：

连接数超出MySQL的最大连接数限制。

解决方法：

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

max_connections=10000

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

set global max_connections=10000;

7、故障现象 7

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 /etc/my.cnf

8、故障现象 8

InnoDB: Error: page 14178 1og sequence number 29455369832

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

问题分析：

innodb 数据文件损坏。

解决方法：

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

二、MySQL主从故障排查

1、故障现象 1

从库的 Slave_IO_Running 为NO

The slave I/O 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 的值，修改为和主库不一样。修改完后重启再同步即可。

2、故障现象 2

从库的 Slave_IO_Running 为 NO

问题分析：

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

解决方法一：

stop slave;
set global sql_slave_skip_counter=1;
start slave;

解决方法二：

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

set global read_only=true;

3、故障现象 3

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

分析问题：

从库的中继日志 relay-bin 损坏。

解决方法：

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

change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='file',master_log_pos=posttion;

三、MySQL 优化

1、硬件方面

说到服务器硬件，最主要的无非 CPU、内存、磁盘三大关键因素。

（1）关于 CPU

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

（2）关于内存

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

（3）关于磁盘

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

2、MySQL 配置文件

（1）InnoDB 存储引擎核心参数

参数	作用	建议配置	注意事项
`innodb_buffer_pool_size`	缓存 InnoDB 的数据和索引，直接影响查询性能	物理内存的 50%~70%（如 64GB 内存设为 40G）	避免超过物理内存，防止系统 swap 导致性能骤降。
`innodb_log_file_size`	单个重做日志文件大小，影响事务提交性能	单个文件 1G~4G（如 2G），总日志大小建议 4G~8G	修改需停止 MySQL 服务并删除旧日志文件后重启。
`innodb_flush_log_at_trx_commit`	控制事务提交时日志刷盘策略	0（高性能，风险高）、1（强一致性）、2（折中，每秒刷盘）	建议设为 2，平衡性能与数据安全（最多容忍 1 秒数据丢失）。

（2）连接与线程管理

参数	作用	建议配置	注意事项
`max_connections`	最大客户端连接数	500~2000（根据业务并发调整）	需结合`Threads_connected`和`Threads_running`监控调整，避免连接耗尽。
`thread_cache_size`	缓存空闲线程，减少线程创建开销	50~100（默认值可能较小）	高并发场景下适当增大，降低线程频繁创建 / 销毁的性能损耗。

（3）内存临时表与排序优化

参数	作用	建议配置	注意事项
`tmp_table_size`	内存临时表大小上限	64M~256M（如 128M）	与`max_heap_table_size`值需一致，过小会导致磁盘临时表（性能骤降），过大可能耗尽内存。
`max_heap_table_size`	内存临时表最大大小	与`tmp_table_size`一致	同上。
`sort_buffer_size`	排序操作缓冲区大小	2M~8M（如 4M）	仅对无索引的排序有效，过大浪费内存。

（4）日志与监控

参数	作用	建议配置	注意事项
`slow_query_log`	启用慢查询日志，记录执行缓慢的 SQL	ON	用于定位性能瓶颈，需定期分析日志。
`long_query_time`	慢查询阈值（秒）	1~2 秒（根据业务容忍度调整）	阈值过低可能产生大量日志，过高可能漏掉真实慢查询。
`binlog_format`	二进制日志格式（主从复制依赖）	ROW（推荐，数据一致性高）	避免使用 STATEMENT 格式（可能导致主从数据不一致）。
`expire_logs_days`	自动清理旧二进制日志的天数	7~14 天（根据备份策略调整）	避免日志堆积占用磁盘空间，需与备份策略配合。

（5）InnoDB 高级优化参数

参数	作用	建议配置	注意事项
`innodb_io_capacity`	存储引擎 I/O 能力（控制脏页刷新速度）	HDD：200~400；SSD：2000~4000	根据磁盘类型调整（SSD 性能远高于 HDD）。
`innodb_flush_method`	控制数据文件与日志文件的刷新方式	O_DIRECT（默认，避免双缓冲）	减少操作系统缓存介入，提升 I/O 效率。
`innodb_autoinc_lock_mode`	自增锁模式，影响插入性能	2（连续模式，高并发插入推荐）	设为 2 可减少锁竞争，提升 INSERT 性能（适用于非事务型自增场景）。

（6）示例配置片段（my.cnf）

[mysqld]
# 核心配置
innodb_buffer_pool_size = 40G       # 64G内存的70%
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
jojn_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

3、SQL 方面

SQL优化是确保数据库高效运行的关键，其核心在于通过减少资源消耗(如CPU、内存、磁盘 I/O)来提升査询响应速度，避免慢查询导致用户体验下降或系统崩溃。未优化的 SQL可能引发全表扫描、几余计算或锁竞争，尤其在数据量大或高并发场景下,会导致服务器负载飙升、响应延迟,甚至影响业务连续性(如交易超时)。通过索引调优、查询改写、执行计划分析等手段，可显著降低数据库压力，支撑业务规模扩展，同时控制硬件成本与运维复杂度

（1）创建测试表并插入数据

#创建测试库
Create database test;

#创建用户表
Use test;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);


#插入 10 万条测试数据（使用存储过程生成）
DELIMITER $$
CREATE PROCEDURE insert_users()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000 DO
        INSERT INTO users (name, email, age) 
        VALUES (CONCAT('user', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insert_users();

（2）使用EXPLAIN 进行SQL 优化的步骤及实验验证

EXPLAIN 是 MySQL 中用于分析 SQL 执行计划的工具，通过模拟查询执行过程输出关键信息(如访问类型type、使用索引 key、预估扫描行数rows、额外操作 Extra 等)，帮助开发者识别全表扫描、索引失效等性能瓶颈，从而指导优化方向(如添加索引、改写查询或调整表结构)，是提升数据库效率不可或缺的诊断手段。

例如：

explain select * from users where name='user123';

字段	说明	优化关注点
id	查询序列号，相同id 为同一执行层，不同id 按序执行（如子2查询）。	复制查询的嵌套层级
select_type	查询类型（SIMPLE、PRIMARY、SUBQUERY、DERIVED等）。	识别子查询或临时表操作。
table	访问的表名或别名	确认查询涉及的表。
type	访问类型，性能从优到劣：system>const>eq_ref>range>index>ALL	避免ALL（全表扫描），优先优化为ref 或range。
possible_keys	可能使用的索引	检查是否有合适索引未被使用
key	实际使用的索引	确认是否命中最佳索引
rows	预估扫描的行数。	行数越少，查询效率越高
Extra	附加信息（如Using where、Using index、Using temporary等）。	发现潜在性能问题（如临时表、文件排序）

根据关键字说明，对 explain SELECT*FROM users WHERE name ='user123的结果分析如下：

type=ALL：全表扫描，效率极低。

possible_keys=NULL：未命中索引。

rows=100000：扫描全部数据。

（3）优化步骤：添加索引

alter table users add index idx_name(name);

（4）优化后查询及 EXPLAIN 分析

explain select * from users where name='user123';

type=ALL：索引查找，效率高。

possible_keys=NULL：命中新创建的索引。

rows=100000：仅扫描一行数据。