MySQL 故障排查与生产环境优化

     前言

       MySQL 是目前企业最常见的数据库之一，占用绝大部分市场份额。在日常维护管理的过程中相信大家肯定会遇到很多常见的故障。为了提高故障处理的及时性，本章案例将常见故障进行汇总，增长学习经验。生产环境中数据库的默认配置无法满足高性能网站架构的需求，本章会从实际工作经验出发，总结 MySQL 数据库应该如何优化。

一：前置知识点

   

要学习如何优化，首先要对 MySQL 的逻辑架构深入的了解。下图 是 MySQL 逻辑架构图，可以让我们更清晰了解 MySQL 的运行原理。

       图中，最上层是一些客户端和连接服务，包含本地 sock 通信和大多基于客户端 / 服务器端工具实现的 TCP/IP 通信。主要完成一些连接处理、授权认证、及相关的安全方案等。在该层上引入了线程池的概念，为通过安全认证接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

      第二层架构主要完成大多数的核心服务功能，如 SQL 接口、缓存的查询、SQL 的分析和优化以及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层上服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

      存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取。数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

1.本案例实验环境

本案例故障汇总和优化相当于是对数据库经验的总结。案例环境比较简单，只需一台数据库模拟单实例环境，另外两台数据库模拟主从环境即可。本章 MySQL 版本使用的是 8.0。

2.案例需求

（1）MySQL 常见故障解决。
（2）MySQL 性能优化。

3.案例实现思路

（1）单库常见故障分析。
（2）主从常见故障分析。
（3）从几个不同方面优化 MySQL。

二：案例实施

1.MySQL 单实例故障排查

（1）故障现象 1

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/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=on，重启数据库。最后修改密码命令如下。
  • Mysql5.7 版本

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

• Mysql8.0

mysql> UPDATE mysql.user SET authentication_string='' WHERE user='root' AND Host='localhost';
mysql> 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.0

mysql> CREATE USER 'root'@'mysql-server' IDENTIFIED BY '123456';
mysql> 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 (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 在短时间内产生太多中断的数据库连接而导致的阻塞（超过 mysql 数据库 max_connection_errors 的最大值）。
• 解决方法：
  • 使用 mysqladmin flush-hosts 命令清除缓存，命令执行方法如下：

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

• 修改 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 /et/my.cnf

（8）故障现象 8

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，启动数据库后备份数据文件，然后去掉该参数，利用备份文件恢复数据。

2.MySQL  主从故障排查

（1）故障排查 1

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

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

（2）故障排查 2

从库的 Slave_IO_Running 为 NO

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

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> 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 点，然后重新同步即可。

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

3.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_buffer_pool_size	InnoDB 缓冲池大小，缓存数据和索引，直接影响读写性能	设置为物理内存的 50% - 70%（如 64GB 内存配 40G）	避免超过物理内存，防止系统交换（Swap）
innodb_log_file_size	单个 InnoDB 重做日志文件大小，影响重做日志（innodb_log_file）	1G - 4G（如 2G）	修改需停止 MySQL，删除旧日志文件后重启
innodb_flush_log_at_trx_commit	事务提交速度和崩溃恢复时间，控制事务日志刷新策略，平衡性能与数据安全	0（高性能，风险高）、1（默认，完全持久化）、2（折中，每秒刷盘）	高并发写入场景可设为 2，但需容忍最多 1 秒数据丢失
max_connections	最大客户端连接数	根据业务需求设置，建议 500 - 2000	监控 Threads_connected 和 Threads_running 调整，避免连接耗尽，配合 thread_cache_size（如 100）缓存线程
tmp_table_size、max_heap_table_size	内存临时表大小上限，影响复杂查询（如 GROUP BY、JOIN）	建议 64M - 256M，两者值需一致（如 128M）	过小会导致磁盘临时表，降低性能；过大可能耗尽内存

2.查询项目优化项

参数	作用	建议配置
query_cache_type	查询缓存类型（MySQL 8.0 已移除，旧版本慎用）	OFF（默认，高并发下建议关闭）
sort_buffer_size	排序操作缓冲区大小	2M - 8M（如 4M ，过大浪费内存）
join_buffer_size	JOIN 操作缓冲区大小	4M - 16M（仅对无索引 JOIN 有效，如 8M ）
read_buffer_size	顺序读缓冲区大小	2M - 8M（如 4M ）
read_rnd_buffer_size	随机读缓冲区大小	4M - 16M（如 8M ）

3.日志与监控

参数	作用	建议配置
slow_query_log	启用慢查询日志	ON
long_query_time	定义慢查询阈值（秒），记录执行时间长的 SQL	1 - 2（根据业务容忍度调整）
log_error	错误日志路径，用于故障排查	指定路径（如 /var/log/mysql/error.log ）
binlog_format	二进制日志格式（主从复制依赖）	ROW（推荐，数据一致性高）
expire_logs_days	自动清理旧的二进制日志天数	7 - 14（根据备份策略调整）

4.InnoDB 高级优化

参数	作用	建议配置
innodb_io_capacity	InnoDB 后台任务的 I/O 能力（如刷新脏页）	SSD 建议 2000 - 4000，HDD 建议 200 - 400
innodb_flush_method	控制数据文件与日志文件的刷新方式	O_DIRECT（默认，避免双缓冲）
innodb_thread_concurrency	InnoDB 并发线程数	0（默认，自适应），高并发场景可设为 CPU 核数 * 2
innodb_autoinc_lock_mode	自增锁模式，影响插入性能	2（连续模式，高并发插入推荐）

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

[mysqld]
# 核心配置
innodb_buffer_pool_size = 40G
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
join_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
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_autoinc_lock_mode = 2

（3）SQL 方面

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

1.创建测试表并插入数据

创建测试库

Create database test;

创建用户表

sql

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 等），帮助开发者识别全表扫描、索引失效等性能瓶颈，从而指导优化方向（如添加索引、改写查询或调整表结构），是提升数据库效率不可或缺的诊断手段。

例如：

mysql>EXPLAIN SELECT * FROM users WHERE name = 'user123';

3.优化步骤：添加索引

mysql>ALTER TABLE users ADD INDEX idx_name (name);

4.优化后查询及EXPLANIN分析

mysql>EXPLAIN SELECT * FROM users WHERE name = 'user123';

优化后结果分析如下：

• type=ref：索引查找，效率高。
• key=idx_name：命中新创建的索引。
• rows=1：仅扫描一行数据