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

目录

一：数据库架构

1.案例环境

2.案例需求

3.案例实现思路

二：案例实施

1.MySQL单实例故障排查

2.MySQL主从故障排查

3.MySQL优化

---

前言

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

一：数据库架构

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

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

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

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

1.案例环境

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

2.案例需求

(1)MySQL 常见故障解决。

(2)MySQL 性能优化。

3.案例实现思路

(1)单库常见故障分析。

(2)主从常见故障分析。

(3)从几个不同方面优化 MySQL。

二：案例实施

1.MySQL单实例故障排查

（1）故障现象1

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

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

（2）故障现象2

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

解决方法：

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

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

（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 命令清除缓存，命令执行方法如下:

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

（6）故障现象6

客户端报 Too many connections

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

解决方法：

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

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

（7）故障现象7

Warning: World-writable config file ,/etc/my.cnf is ignoredERROR! MySQL is running but PID file could not be found

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

解决方法：

（8）故障现象8

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

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

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

2.MySQL主从故障排查

（1）故障现象

从库的 Slave_IO_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 thereplicate-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）故障现象

从库的 Slave_IO_Running 为NO

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

解决方法一:

解决方法二：

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

（3）故障现象3

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

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

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

3.MySQL优化

（1）硬件方面

说到服务器硬件，最主要的无非 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)硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多。

（2）MySQL配置文件

核心性能优化

参数	作用	建议配置	注意事项
innodb_buffer_pool_size	InnoDB 缓冲池大小，缓存数据和索引，直接影响读性 能。	设置为物理内存的 50%~70%(如64GB内存配 40G)。	避免超过物理内存，防止系统交换(Swap)。
innodb_log_file_size	单个InnoDB重做日志文件大小	建议1G~4G	修改需停止MySQL，删除旧日志文件后重启。
innodb_flush_log_at_trx_ commit	控制事务日志刷新策略，平衡性能与数据安全。	1(默认，完全持久化)，2(折中,每秒刷盘)，0(高性能，风险高)。	高并发写入场景可设为 2，但需容忍最多 1 秒数据丢失。
max_connections	最大客户端连接数，避免连接耗尽	根据业务需求设置,建议500~2000，配合thread_cache_size(如 100)缓存线程	监控Threads_connected和Threads running调整
tmp_table_size	内存临时表大小上限	建议64M~256M，	过小会导致磁盘临时表，降低性过小会导致磁盘临时表，降低性。
max_heap_table_size	影响复杂查询(如GROUP BY、JOIN)	mp_table_sizemax_heap_table_size两者值需一致(如128M)

查询优化项

参数                                        作用                                         建议配置

• query_cache_type ：查询缓存类型(MySQL 8.0己移除，旧版本慎用)。0FF(默认，高并发下建议关闭)。
• 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)。

日志与监控

参数                                        作用                                         建议配置

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

InnDB高级优化

参数                                        作用                                         建议配置

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

（3）sql方面

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

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

创建测试库

插入10万条数据

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

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

例如：

不使用的情况下：

使用后

3.优化步骤：添加索引

4.优化后查询及explain分析