在MySQL日常使用中，会碰到各种故障,通过下面3个故障，介绍如何快速定位并恢复。

1.MySQL 无响应&Crash

MySQL因为各种各样的原因没有响应，其中可能的原因可以分成以下两类：

• 主机层：内存升高、CPU升高、磁盘性能和空间不足。
• MySQL服务层：SQL质量，参数设置不合理，数据表设计不合理，出现锁，并发过多。

1）主机层出现问题常常会直接导致MySQL崩溃或者宕机。排查方法如下。

• 确定服务器是否崩溃，检查主机是否关机或者重启过uptime命令,message,dmesg。

• 查看错误日志，或者借助监控或者服务器命令确认MySQL是否存活（命令 ps -ef｜grep mysql）。如果服务器同时运行着mysqld_safe守护进程的时候，MySQL会被自动拉起，错误日志也将包含MySQL重启的消息。

• 查看日志中包含[ERROR]的信息，和对应MySQL发生故障的时间，将会得到MySQL究竟为什么发生故障的原因。这可以解决大多数的MySQL crash故障（内核参数，Limit限制）。

• 如果得到了大致问题的方向并且确定了原因之后，应该去查看一下这个问题是否是bug造成的，如果是，是否能够通过数据库升级避免这个bug。如果不是需要怎么也样规避这个问题再次发生。

2)MySQL本身的问题往往一开始会造成无响应，hang住，执行SQL很慢，甚至最终导致崩溃。

• DDL操作会需要元数据锁（Metadata Lock, MDL） 的排它锁，这个锁会阻塞后续所有对该表的操作（包括读和写），导致连接迅速堆积。
• 长时间SQL语句占用CPU、内存（排序、分组）、磁盘IO，并持有锁。当这种SQL并发量上来时，数据库资源会被迅速榨干，形成“雪崩效应”。
• 表设计方面是“先天不足”的问题。 包含：数据量过大，无主键，大字段数据，无索引等情况下，一旦业务增长，性能问题会指数级放大。

解决方法：
1.监控一定要到位（包含操作系统和数据库核心指标）和 优化操作系统内核。
2.官网Bug对应的版本升级。
3.表结构合理设置 和 慢SQL优化。

2.内存使用高

MySQL内存使用高问题需要从多个维度信息分析。如：资源配置，参数配置、透明大页、自身内存泄漏，不规范的SQL语句和第三方补助插件等。

1）操作系统的内存检查
#freee -m
如果OOM还未发生，MySQL还在不断的需求内存时，可以看到此时内存已经不足，并且可能已经使用了swap空间。
如果OOM已经发生，MySQL已经crash，并且被mysqld_safe重新拉起，此时内存使用量应该风平浪静。

使用top监控当前内存的使用：

#top
Mem:  132031556k total, 131418864k used,   612692k free,   212104k buffers
Swap: 16777212k total,        0k used, 16777212k free, 14648144k cache
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14920 mysql     20   0  125g 109g 6164 S  6.6 88.0  27357:08 mysqld

2)查询内存参数配置
通过SQL查询参数分配的内存配置。

mysql>select (@@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + (select count(host) from information_schema.processlist)*(@@read_rnd_buffer_size+ @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size+ @@thread_stack)) / (1024*1024) AS MAX_MEMORY_MB;

MySQL8.0以上版本应除掉query_cache_size:

mysql> select ( @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + (select count(host) from information_schema.processlist)*(@@read_rnd_buffer_size+ @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size+ @@thread_stack)) / (1024*1024) as MAX_MEMORY_MB;

如果分配的内存本身已经超越了系统内存是很容易造成OOM的，此时需要查出哪些参数设置比较大，适当降低内存分配。

3)查询配置其他参数据文件打开的数量
innodb_buffer_pool在mysql中占有很大的内存部分，调小innodb_buffer_pool_size可以降低OOM问题。innodb_buffer_pool_size为系统内存的50%～60%。如果这些参数设置都很合理，还是出现了OOM的问题，可能的原因是mysql由于MVCC特性，虽然减少了锁的争用，但是保存的表镜像太多（也会出现大量的proccesslist中长时间连接的线程作为佐证）。

# ps -ef|grep mysql
15746是ps -ef|grep mysql查出的mysql进程号

# lsof -p 15746 |grep ibd|wc -l 

此时如果发现lsof -p 15746 |grep ibd|wc -l查询的值较大。应当检查MySQL和文件系统对于打开文件数量的限制：

#ulimit -a
open files                      (-n) 65535

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 256   |
+------------------+-------+
1 row in set (0.00 sec)

查参数table_open_cache，当打开一个表后会缓存文件描述符：

mysql> show global variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4096  |
1 row in set (0.00 sec)

mysql> show global status like '%open%tables%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_tables            | 276   |
| Opened_tables          | 307   |
+------------------------+-------+
4 rows in set (0.01 sec)

当缓存中的值open_tables 临近到了 table_open_cache 值的时候，说明表缓存池快满了，但Opened_tables 还在一直增长，这说明还有很多未被缓存的表。

用SHOW OPEN TABLES FROM database_name命令，可以查看table_open_cache中缓存的表：

mysql> show open tables from employees;
+-----------+--------------+--------+-------------+
| Database  | Table        | In_use | Name_locked |
+-----------+--------------+--------+-------------+
| employees | departments  |      0 |           0 |
| employees | salaries     |      0 |           0 |
| employees | dept_manager |      0 |           0 |
| employees | dept_emp     |      0 |           0 |
| employees | titles       |      0 |           0 |
| employees | employees    |      0 |           0 |
+-----------+--------------+--------+-------------+
6 rows in set (0.02 sec)

In_use显示当前正在使用此表的线程数，如果大于0也意味着此表被锁。
Name_locked只适用于DROP和RENAME，在执行DROP或RENAME时，table_open_cache中的表文件描述符会被移除，所以不会看到除0以外的其他值。

一般在库表比较多的情况下（分库分表）很容易出现内存占用较大的情况。如果要解决根源，还是需要对库表进行拆分。

4）查看是否有除InnoDB引擎外的其他引擎在消耗内存

mysql>select * from information_schema.tables 
     where engine!='innodb' and  engine!='performance_schema' 
     and TABLE_SCHEMA !='information_schema';

5）大操作导致内存上升
备份（mysqldump，xtrabackup） 等第三方工具备份导致使用内存上升。load data 批量导入也会导致大量内存。

解决方法:
1.调整参数配置核心内存参数。
2.检查表结构设计合理。
3.备份尽量放到备节点，避开高峰时间。

3. CPU负载升高

在长期运行当中，CPU负载升高，往往是问题SQL往往有order by或group by语句，多表连接造成的性能问题。另外一种情况是QPS很大，即使SQL已经质量足够好，但是由于负载很大。

排查方法:
1)top监控查看CPU高的进程
查看是否是MySQL进程暂居大量内存。同时确认内存是否释放。

#top -H -p 1821
top - 21:04:24 up 10 min,  1 user,  load average: 0.00, 0.02, 0.04
Threads:  31 total,   0 running,  31 sleeping,   0 stopped,   0 zombie
%Cpu(s):  90.0 us,  0.2 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  1867048 total,   390212 free,  1227176 used,   249660 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.   454824 avail Mem 

根据具体PID，定位问题SQL

SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;

2)查看执行SQL语句信息
MySQL中执行SHOW FULL PROCESSLIST 查看长时间执行的SQL语句。

mysql> SHOW FULL PROCESSLIST;

找到对应的SQL，进行优化。
当执行完show processlist后出现大量的语句，通常其状态出现sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result, Using filesort 都是sql有性能问题。

• sending data表示：sql正在从表中查询数据，如果查询条件没有适当的索引，则会导致sql执行时间过长。
• Copying to tmp table on disk：出现这种状态，通常情况下是由于临时结果集太大，超过了数据库规定的临时内存大小，需要拷贝临时结果集到磁盘上，这个时候需要用户对sql进行优化。
• Sorting result, Using filesort：出现这种状态，表示sql正在执行排序操作，排序操作都会引起较多的cpu消耗，通常的优化方法会添加适当的索引来消除排序，或者缩小排序的结果集。

解决方法:
1.Explain进行语句的优化。
2.检查表结构设计合理（主键，索引）。
3.备份尽量放到备节点，避开高峰时间。

总结

MySQL故障分析的核心思路：

• 保持冷静：按既定流程操作
• 日志先行：错误日志是解决问题的第一把钥匙
• 分层排查：从应用到硬件逐层排除
• 工具辅助：善用各种监控和分析工具
• 经验积累：每次故障都是一次学习机会

掌握这个分析框架和技术点，就能在面对大多数MySQL故障时，做到心中有数，手中有术。