前言
MySQL故障排查的意义在于及时识别并解决数据库系统中的问题，确保数据的完整性和可靠性；而性能优化则旨在提高数据库系统的效率和响应速度，从而提升用户体验和系统整体性能。这两方面的工作都对于保证数据库系统稳定运行、提升业务效率和降低维护成本至关重要。
目录
一、MySQL 逻辑架构图
二、常见故障案例及解决方法
1. MySQL单实例常见故障
案例一
故障现象：
ERROR 2002 (HY000): Can't connect to local MysQL server through socketYdata/mysql/mysql.segk'(2)
# 这个错误表明 MySQL 客户端无法通过套接字连接到本地的 MySQL 服务器。问题分析：
- MySQL 服务器未运行：MySQL 服务器可能未启动或已经停止
- 防火墙：查看是否被拦截
- 端口：数据库端口3306故障
解决方法：
启动数据库、关闭防火墙、检查开放数据库监听端口
案例二
故障现象：
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:NO)
# 用户 'root'@'localhost' 的访问被拒绝问题分析：
- 密码不正确或者没有权限访问
- 'root' 用户没有足够的权限来执行所请求的操作
解决方法：
① 修改my.cnf主配置文件，在[mysqld]下添加：skip-grant-tables，重启服务
- mysql 直接登录
- update更新user表authentication_string字段，格式为：update mysql.user set authentication_string = password('新密码') where user='用户名';
- 还原/etc/my.cnf配置
② 确保 'root' 用户具有执行所需操作的足够权限
- 查看权限：show grants for '用户名'@'来源地址';
- 申请授权：grant 权限列表 on 库.表 to '用户名'@'来源地址' [identified by '密码'];
案例三
故障现象：
在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题
问题分析：
- DNS 解析：DNS解析慢、客户端连接过多
- 索引问题：如果查询需要大量数据传输，可能会导致远程连接的延迟
- 网络延迟：网络连接不稳定或存在延迟
解决方法：
① 修改my.cnf主配置文件，增加skip-name-resolve参数：如果在内网，可以禁用dns解析
② 确保相应的表上有适当的索引
③ 检查并考虑升级网络
案例四
故障现象：
Can't open file:'xxx forums.MYl'.(errno: 145)
# MySQL 数据库无法打开名为 "xxx forums.MYI" 的文件，这通常表示表损坏或者出现了其他文件系统相关的问题问题分析：
- 服务器非正常关机，数据库所在空间已满，或一些其它未知的原因对数据库表造成了损坏
- 因拷贝数据库导致文件的属组发生变化
解决方法：
① 尝试修复数据表(myisamchk、phpMyAdmin)
② 如果有可用的备份，可以考虑从备份中恢复损坏的表
③ 修改文件的属组
案例五
故障现象：
ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx' is blocked because of manyconnection errors; unblock with 'mysqladmin flush-hosts'
# 这个错误表明主机 'xxx.xxx.xxx.xxx' 由于连接错误过多而被阻止。这通常是因为该主机发起了太多的连接尝试，但是这些连接都以错误结束。问题分析：
超出最大连接错误数量限制
解决方法：
① 清除缓存（flush-hosts关键字），可以使用 mysqladmin 工具以及 flush-hosts 关键字：mysqladmin flush-hosts -u your_username -p'密码'
② 添加修改mysql配置文件（通常是 my.cnf 或 my.ini），在 [mysqld] 块中添加 max connecterrors=1000，保存文件并重新启动 mysql 服务
案例六
故障现象：
Too many connections
# 表明 MySQL 服务器当前的连接数已经达到了最大允许的连接数，因此无法接受新的连接请求。这可能是由于系统资源不足或配置不当导致的。问题分析：
连接数超出MySQL的最大连接限制
解决方法：
① 修改MySQL配置文件(max connections=10000)，在 [mysqld] 部分中，添加或修改 max_connections 参数
临时修改参数：set GLOBAL max connections=10000；
② 监控系统资源：确保监控系统资源使用情况，包括 CPU、内存和磁盘等。如果资源使用已经接近极限，可能需要考虑升级硬件或优化数据库服务器的配置
③ 连接池：考虑在应用程序中实现连接池来更有效地管理连接，避免频繁地打开和关闭连接。
案例七
故障现象：
Warning: World-writable config file /etc/my.cnf is ignoredERROR! MySQL is running but PlD file could not be found
# 这个警告表明 MySQL 配置文件 /etc/my.cnf 具有全局可写的权限，这可能存在安全风险，因为任何用户都可以修改这个文件
问题分析：
MySQL的配置文件/etc/my.cnf权限问题
解决方法：
chmod 644 /etc/my.cnf，或者修改属组、属主
案例八
故障现象：
InnoDB:Error: page 14178 log sequence number 29455369832InnoDB: is in the future! Current system log sequence number 29455369832
# 表明 InnoDB 存储引擎检测到一个问题，指示某个页面的日志序列号处于未来状态。这可能是由于系统时间不正确导致的问题分析：
- innodb数据文件损坏
- 系统时间不正确
解决方法：
① 强制 InnoDB 存储引擎以特定的恢复模式启动：修改my.cnf配置文件(innodb force recovery=4)
innodb_force_recovery 参数有以下几个可能的取值（0-6）：
- 0：表示禁用强制恢复模式，InnoDB 将按照正常流程启动
- 1-6：表示启用不同级别的强制恢复模式，数字越大代表的修复级别越高
② 增量备份还原到一张新表，给甲方或上级确认，然后insert into 新表中
③ 数据库查询装（或者备用数据库），做全表恢复一般不使用，除非迫不得已
2. MySQL主从环境常见故障
案例一
故障现象：从库的Slave lO Running为NO
The slave l/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 not always make sense;please check the manual before using it).
# 这个错误表明 MySQL 复制中的从库停止了 l/0 线程，因为主库和从库具有相同的 MySQL 服务器ID。在 MySQL 复制中，主库和从库的服务器ID必须是唯一的，否则会导致复制无法正常工作问题分析：
- 主库和从库的 server-id 值一样
- 网络是否通讯，防火墙策略
- 从服务slave是否开启
- master_log_file 与 master_log_pos 值跟 master 值是否一致
解决方法：
① 修改从库的server-id的值，修改为和主库不一样：[mysqld] server-id = newid，重新启动数据库并再次同步：systemctl restart mysqld.service
② ping，查看防火墙状态及 iptables 策略
③ show slave status\G
④ 在 Master 节点查看二进制文件和同步点：show master status;
在 Slave 节点执行同步操作：change master to master_host='Master ip',master_user='用户名',master_password='密码',master_log_file='日志名',master_log_pos=偏移量;
确保“日志名”和“偏移量”与主机查询的数据一致
案例二
故障现象：
查询数据库装状态时，从库的Slavel0 Running为NO：show slave status\G
问题分析：
主键冲突或者主库删除或更新数据，从库内找不到记录，数据被修改导致
解决方法：
① 设置跳过错误：
mysql> stop slave;
mysql> set global sql slave skip counter=1:mysql> start slave; # 设置一个全局的 SQL 语句跳过计数器
mysql> start slave;② 将 MySQL 数据库实例设置为只读模式。当数据库处于只读模式时，所有对数据的写操作（如插入、更新、删除）都将被禁止，只允许进行读取操作
set global read only=true;案例三
故障现象：
Error initializing relay log position: l/0 error reading the header from the binarylog
# 这个错误表明 MySQL 复制中的从库在初始化中遇到了问题，无法正确读取二进制日志文件的头部信息问题分析：
- 从库的中继日志relay-bin损坏
- 二进制日志文件可能损坏或不完整
- 从库可能没有足够的权限来访问二进制日志文件或者所在的目录
解决方法：
① 手工修复，重新找到同步的binlog和pos点，然后重新同步即可：
- 在 Master 节点查看二进制文件和同步点：show master status;
- 在 Slave 节点执行同步操作：change master to master_host='Master ip',master_user='用户名',master_password='密码',master_log_file='日志名',master_log_pos=偏移量;
- 确保“日志名”和“偏移量”与主机查询的数据一致
② 从数据库同步使用：
grant replication slave on *.* to 'slave用户'@'来源地址' identified by '密码';
manager 使用：
grant all privileges on *.* to 'mha用户'@'来源地址' identified by 'manager密码';
三、MySQL 优化
1. 物理优化
1.1 选择合适的硬件
- CPU：推荐使用S.M.P.架构的多路对称CPU
- 内存：4GB以上的物理内存，以便 MySQL 能够缓存数据和索引，从而加快查询速度
- 存储：选择高性能的存储设备，如固态硬盘（SSD），以减少 I/O 瓶颈，RAID-0+1磁盘阵列
1.2 配置操作系统
- 选择合适的文件系统，如 XFS 或者 ext4
- 调整文件描述符限制、内核参数
1.3 网络优化
- 网络带宽和延迟性：确保有足够的网络带宽，尤其是对于分布式数据库或者需要大量数据传输的场景；优化网络延迟和稳定性
1.4 监控
- 使用监控工具来跟踪数据库性能，及时发现瓶颈并进行调整
2. 功能优化
2.1 设置索引
mysql 索引相关介绍请参考：MySQL数据库索引介绍-CSDN博客
① 普通索引（index）：普通索引是最基本的索引类型，允许列中存在重复的值，没有特殊的要求和规则
② 唯一索引（unique index）：唯一索引要求索引列的值是唯一的，不允许重复值；唯一索引允许有空值（注意和主键不同，且在生产环境中只允许一次）
③ 主键索引是一种特殊的唯一索引，必须指定为“primary key”；不允许有 NULL 值，且每个表只能有一个主键索引
④ 组合索引：组合索引是指将多个列组合起来创建索引（也可以单列），需要满足最左原则，因为select语句的 where条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效
⑤ 全文索引：可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建，在生产环境中每个表只允许一个全文索引（索引本身占用空间，且每次修改数据索引也要跟着变动）
需要注意的是：一张表最多建立5个索引
2.2 创建 mysql 视图
mysql 视图相关介绍请参考：MySQL高级SQL语言常用查询与连接查询-CSDN博客
数据库中的虚拟表，不包含真实数据，只是映射；简化SQL语句，简化查询结果集、灵活查询，针对不同的用户呈现不同的结果集；只适合查询，不适合增删改。视图有表之后才能存在，它的内容都来自基本表，一个视图可对应一个或多个基本表。
视图内容会随着表变化（修改表不能修改以复合函数方式计算出来的字段）；修改视图会影响表。删除视图不影响表，反之会影响。
2.3 MHA 高可用配置
mysql MHA 高可用配置相关介绍请参考：MySQL数据库MHA高可用集群-CSDN博客
① MHA 是 MySQL 高可用环境下故障切换和主从复制的软件，用于数据库的高可用性解决方案
② MHA 能够监控主服务器、数据库集群的状态，发生故障能在0-30秒内自动切换，可以解决单点故障
③ MHA 能在故障切换的过程中最大程度上保证数据的一致性以达到真正意义上的高可用
2.4 读写分离
mysql 读写分离相关介绍请参考：MySQL主从复制与读写分离-CSDN博客
读写分离则可以将读操作分发到从数据库，减轻主数据库的读压力，提高整体性能。
① 当 master 数据更新后会将完整的 sql 语句记录在二进制日志文件 mysql-bin-* 中
② master dump 进程正常处于休眠状态，不定期按需检测二进制日志，发现日志有更新，会通知 slave i/o 线程
③ slave i/o 线程向 master dump 进程进行数据请求，得到回复后，将二进制日志文件复制到本地，保存为中继日志
④ slave 基于中继日志进行读取恢复操作，sql 线程将执行的sql语句同步到自己的数据库中，从而达到与 master 数据的一致性
2.5 分库、分表、分区
分表和分区功能需要使用支持这些功能的存储引擎，如InnoDB
2.5.1 分库
① 介绍：分库是将整个数据库按照一定的规则分割成多个独立的数据库实例。每个数据库实例通常被称为一个分片（shard），并且包含了数据的一个子集
② 优势：
- 横向扩展：允许数据库系统在多台服务器上分布数据，以应对大量数据和高并发查询的需求
- 负载均衡：通过将数据分布到不同的节点上，可以更好地实现负载均衡，提高系统整体的吞吐量和响应能力
2.5.2 分表
① 介绍：分表是将单个表中的数据按照特定的规则分割成多个物理表。这些物理表通常具有相似的结构，但包含不同的数据子集
② 优势：
- 性能提升：通过减少单个表的数据量，可以提高查询性能和维护效率
- 数据管理：更好地管理历史数据，例如按时间范围进行分表，可以简化数据归档和删除操作
2.5.3 分区
① 介绍：分区是将单个表中的数据按照特定的规则分割成多个逻辑部分，每个部分称为一个分区。分区本质上还是属于同一个表，只是数据的存储方式变得更加灵活
② 优势：
- 性能提升：通过分区可以减少查询的数据量，提高查询性能
- 数据管理：更好地管理历史数据，例如按时间范围进行分区，可以简化数据归档和删除操作
3. 查询方式
（1）查看每个客户端IP过来的连接消耗了多少资源
mysql> select * from sys.x$host_summary;
# sys.x$host_summary是Information Schema数据库中的一个视图，用于提供关于主机（host）的汇总信息。这个视图通常包含了有关连接、查询和表访问的统计信息
# sys.x$host_summary视图可能包含以下列：
# host：主机名或IP地址
# statements：执行的语句数量
# statement_latency：所有语句的总延迟时间
# statement_avg_latency：平均每条语句的延迟时间
# table_scans：表扫描次数
# file_ios：文件I/O 操作次数
# file_io_latency：文件I/O 的总延迟时间
# current_connections：当前连接数
# total_connections：总连接数（2）查看某个数据文件上发生了多少IO请求
mysql> select * from sys.x$io_global_by_file_by_bytes;
# sys.x$io_global_by_file_by_bytes 是 MySQL 中的一个系统视图，用于提供有关文件 I/O 操作按字节计的全局统计信息。这个视图通常包含了有关不同文件的 I/O 操作的详细信息。
# sys.x$io_global_by_file_by_bytes 视图可能包含以下列：
# file_name：文件名
# count_read：读取次数
# count_write：写入次数
# total_read：总读取字节数
# total_written：总写入字节数（3）查看每个用户消耗了多少资源
mysql> select * from sys.x$user_summary;
# sys.x$user_summary是一个系统视图，用于提供关于用户的汇总信息。这个视图通常包含了有关用户连接、查询和表访问的统计信息。
# sys.x$user_summary视图可能包含以下列：
# user：用户名
# statements：执行的语句数量
# statement_latency：所有语句的总延迟时间
# statement_avg_latency：平均每条语句的延迟时间
# table_scans：表扫描次数
# file_ios：文件I/O 操作次数
# file_io_latency：文件I/O 的总延迟时间
# current_connections：当前连接数
# total_connections：总连接数（4）查看总共分配了多少内存
mysql> select * from sys.x$memory_global_total;
# sys.x$memory_global_total是一个系统视图，用于提供有关全局内存使用情况的汇总信息。这个视图通常包含了有关MySQL实例整体内存使用的统计信息。
# sys.x$memory_global_total视图可能包含以下列：
# total_allocated：已分配的内存总量
# unreclaimable：不可回收的内存总量
# free：可用内存总量（5）数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？查看当前连接情况
mysql> select host, current_connections, statements from sys.x$host_summary;
# 从 MySQL 的系统视图 sys.x$host_summary 中选择主机名（host）、当前连接数（current_connections）和语句数量（statements）的数据。
# 这个查询的目的是获取关于主机的连接和语句执行情况的汇总信息。通过执行这个查询，可以了解到每个主机当前的连接数以及执行的语句数量，有助于监控数据库服务器的负载情况。（6）查看当前正在执行的SQL和执行show full processlist的效果相当
mysql> select conn_id, user, current_statement, last_statement from sys.x$session;
# 从 MySQL 的系统视图 sys.x$session 中选择连接ID（conn_id）、用户（user）、当前语句（current_statement）和上一个语句（last_statement）的数据。
# 这个查询的目的是获取有关数据库会话的信息，包括当前正在执行的语句以及上一个执行的语句。通过执行这个查询，可以了解每个数据库会话的活动情况，有助于跟踪和监控数据库中不同会话的操作。（7）数据库中哪些SQL被频繁执行？查询TOP10 SQL
mysql> select db,exec_count,query from sys.x$statement_analysis order by exec_count desc limit 10;
# 从 MySQL 的系统视图 sys.x$statement_analysis 中选择数据库名（db）、执行次数（exec_count）和查询语句（query），并按执行次数降序排列，最终只显示前10条记录。
# 这个查询的目的是获取执行次数最多的查询语句以及它们所属的数据库。通过执行这个查询，可以了解哪些查询在数据库中被频繁执行，有助于识别和优化高负载的查询，提高数据库性能。（8）哪个文件产生了最多的IO，读多，还是写的多？
mysql> select * from sys.x$io_global_by_file_by_bytes limit 10;
# 从 MySQL 的系统视图 sys.x$io_global_by_file_by_bytes 中选择所有列的数据，并且限制结果只显示前10条记录。
# 这个查询的目的是获取有关文件 I/O 操作按字节计的全局统计信息的前10条记录。这些信息包括文件名、读取次数、写入次数、总读取字节数以及总写入字节数。通过执行这个查询，可以快速查看数据库中文件 I/O 活动的摘要信息。（9）哪个表上的IO请求最多？
mysql> select * from sys.x$io_global_by_file_by_bytes where file like "%ibd" order by total desc limit 10;
# 从 MySQL 的系统视图 sys.x$io_global_by_file_by_bytes 中选择所有列的数据，其中文件名（file）包含 "ibd" 的记录，并按照总字节数（total）降序排列，最终只显示前10条记录。
# 这个查询的目的是获取文件名中包含 "ibd" 的文件在文件 I/O 操作中按字节计的全局统计信息的前10条记录。通过执行这个查询，可以了解与 InnoDB 数据文件相关的 I/O 活动，有助于监控数据库中 InnoDB 存储引擎的文件 I/O 使用情况。（10）哪个表被访问的最多？
mysql> select * from sys.x$statement_analysis order by avg_latency desc limit 10;
# 从 sys.x$statement_analysis 视图中选择所有列，并按照 avg_latency 字段的降序排列，然后限制结果显示前 10 行数据。
# 先访问statement_analysis，根据热门SQL排序找到相应的数据表。 哪些语句延迟比较严重？ 查看statement_analysis中avg_latency的最高的SQL。
# 这个查询将返回平均延迟时间最高的前 10 条 SQL 语句的详细信息，包括这些 SQL 语句的文本内容、执行次数、平均延迟时间等。（11）哪些SQL执行了全表扫描，如果没有使用索引，则考虑为大型表添加索引
mysql> select * from sys.x$statements_with_full_table_scans;
# 从 sys.x$statements_with_full_table_scans 视图中选择所有列，该视图用于显示执行全表扫描的 SQL 语句的相关信息。全表扫描是一种查询方式，它会对表中的每一行进行扫描，而不使用索引来加速查询。
# 将获得执行全表扫描的 SQL 语句的详细信息，包括这些语句的文本内容、执行次数、扫描的表名等（12）列出所有做过排序的规范化语句
mysql> select * from sys.x$statements_with_sorting;
# 从 sys.x$statements_with_sorting 视图中选择所有列，该视图用于显示执行排序操作的 SQL 语句的相关信息。排序操作在数据库查询中经常用到，它可以按照指定的列对结果进行排序。
# 获得执行排序操作的 SQL 语句的详细信息，包括这些语句的文本内容、执行次数、涉及的排序字段等（13）哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表？
查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可。
mysql> select db, query, tmp_tables, tmp_disk_tables from sys.x$statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;
# 从 sys.x$statement_analysis 视图中选择数据库名称 (db)、SQL 查询语句 (query)、临时表数目 (tmp_tables) 和使用磁盘的临时表数目 (tmp_disk_tables) 这四个列的数据。查询的条件是临时表数目大于 0 或者使用磁盘的临时表数目大于 0，然后按照临时表数目和使用磁盘的临时表数目之和的降序排列，最后限制结果显示前 20 行数据。（14）列出所有使用临时表的语句——访问最高的磁盘临时表，然后访问内存临时表
SELECT *
FROM sys.x$statement_analysis
WHERE tmp_disk_tables > 0
ORDER BY tmp_disk_tables DESC;
SELECT *
FROM sys.x$statement_analysis
WHERE tmp_tables > 0
ORDER BY tmp_tables DESC;
（15）哪个表占用了最多的buffer pool？
mysql> select * from sys.x$innodb_buffer_stats_by_table order by allocated desc limit 10;
# 从 sys.x$innodb_buffer_stats_by_table 表中检索数据，并按照分配的缓冲区大小降序排列，然后限制结果为前10行。返回分配缓冲区大小最高的前10个表的统计信息（16）每个库（database）占用多少buffer pool？
mysql> select * from sys.x$innodb_buffer_stats_by_schema order by allocated desc limit 10;
# 从 sys.x$innodb_buffer_stats_by_schema 表中检索数据，并按照分配的缓冲区大小降序排列，然后限制结果为前10行。返回分配缓冲区大小最高的前10个架构（schema）的统计信息4. 配置优化
4.1 缓冲池设置
- innodb_buffer_pool_size：设置 InnoDB 缓冲池大小，建议分配给系统内存的 50%-70%
- key_buffer_size：用于 MyISAM 表索引缓存的大小，适用于 MyISAM 存储引擎
示例：16GB 内存和 8 个核心
[mysqld]
innodb_buffer_pool_size = 12G
key_buffer_size = 256M4.2 连接和线程设置
- max_connections：设置允许的最大连接数
- thread_cache_size：设置线程缓存的大小，以减少线程创建和销毁的开销
示例：16GB 内存和 8 核心
max_connections = 200
thread_cache_size = 164.3 日志设置
- log_error：指定错误日志路径
- slow_query_log：启用慢查询日志
- long_query_time：设置慢查询的阈值，如 5 秒
- log_queries_not_using_indexes：记录未使用索引的查询
示例：16GB 内存和 8 核心
log_error = /var/log/mysql/error.log
slow_query_log = 1
long_query_time = 5
log_queries_not_using_indexes = 1
# 可以记录执行时间超过5秒和没有使用索引的查询 4.4 性能调整
- query_cache_type：根据需求设置是否启用查询缓存
- query_cache_size：可以设置为 0 禁用查询缓存或根据需要调整大小
示例：
query_cache_type = 0
query_cache_size = 04.5 InnoDB 相关设置
- innodb_flush_method：根据文件系统选择合适的刷新方法，如 O_DIRECT
- innodb_flush_log_at_trx_commit：可以设置为 2 以提高性能
示例：
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 24.6 临时表设置
- tmp_table_size 和 max_heap_table_size：根据需求设置合适的临时表大小，如 256M
示例：
tmp_table_size = 256M
max_heap_table_size = 256M5. 其他优化
5.1 定期优化表
使用 optimize table 命令或者定期清理无用数据，以提高表的性能。
① 登录到 MySQL 数据库服务器的命令行界面或者使用 MySQL 客户端工具。
② 选择要优化的数据库：use 库
③ 运行 optimize table 命令来优化指定的表：optimize table 表名;
④ 对于 InnoDB 表，optimize table 实际上会重建表，以清理碎片并重新组织数据，从而提高性能
5.2 对查询进行缓存
通过合理配置查询缓存、使用应用程序级缓存、定期清理缓存等方法，可以有效地提高 MySQL 数据库的性能，并减少对数据库的查询压力。
① 启动查询缓存
vim /etc/my.cnf
[mysqld] #在此字段中添加
query_cache_size = 32M # 设置查询缓存的大小
systemctl restart mysqld.server
② 查询缓存
mysql> show status like 'qcache%';
