MySQL：数据库诊断命令

妖狐艹你老母 2022-12-14 12:19 148阅读 0赞

> **TIPS**  
> 本文基于MySQL 8.0，理论支持MySQL 5.0及更高版本。

本文整理了常用的MySQL诊断命令，可以帮助我们了解数据库的运行情况。

### SHOW PROCESSLIST ###

作用：

SHOW \[FULL\] PROCESSLIST用于查看当前正在运行的线程。如果执行此命令的用户拥有 [PROCESS][] 权限，则可看到所有线程；否则只能看到自己的线程（即与当前登录用户关联的线程）。如果不使用FULL关键字，只在Info字段中展示前100个字符。

当遇到“too many connections”错误信息时，想要了解发生了什么，SHOW PROCESSLIST就非常有用。MySQL保留了一个额外的连接，用于让拥有 [CONNECTION\_ADMIN][CONNECTION_ADMIN] （或已废弃的 SUPER ）权限的账户使用，从而确保管理员始终能够连接并检查系统。

可使用 [KILL][] 语句杀死线程。

语法：

SHOW [FULL] PROCESSLIST

示例：

mysql> SHOW FULL PROCESSLIST\G
    *************************** 1. row ***************************
    Id: 1
    User: system user
    Host:
    db: NULL
    Command: Connect
    Time: 1030455
    State: Waiting for master to send event
    Info: NULL
    *************************** 2. row ***************************
    Id: 2
    User: system user
    Host:
    db: NULL
    Command: Connect
    Time: 1004
    State: Has read all relay log; waiting for the slave
           I/O thread to update it
    Info: NULL
    *************************** 3. row ***************************
    Id: 3112
    User: replikator
    Host: artemis:2204
    db: NULL
    Command: Binlog Dump
    Time: 2144
    State: Has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
    *************************** 4. row ***************************
    Id: 3113
    User: replikator
    Host: iconnect2:45781
    db: NULL
    Command: Binlog Dump
    Time: 2086
    State: Has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
    *************************** 5. row ***************************
    Id: 3123
    User: stefan
    Host: localhost
    db: apollon
    Command: Query
    Time: 0
    State: NULL
    Info: SHOW FULL PROCESSLIST
    5 rows in set (0.00 sec)

由结果可知，结果包含如下几列：

*  Id：连接的唯一标识，是CONNECTION\_ID()函数的返回。
 *  User：发出该语句的MySQL用户。
    
     *  system\_user表示服务器产生的非客户端线程，用于处理内部任务。这可能是用来在从库复制或延迟行处理器的IO/SQL线程。对于system\_user，Host字段将会为空
     *  unauthenticated user是指与客户端连接，但尚未完成客户端用户身份认证的线程。
     *  event\_scheduler是指事件调度器的监控线程。（有关事件调度器可详见 “Using the Event Scheduler” ）
    
    > **TIPS**  
    > User字段的值是system\_user和 SYSTEM\_USER 权限不是一回事，前者指内部线程，后者用来区分系统账户和普通账户的类别，不要搞混了。
 *  Host：发出该语句的客户端的主机名（当User是system\_user时，Host为空）。TCP/IP链接的主机名以 host\_name:client\_port 格式上报，以便更轻松地了解哪个客户端在干什么。
 *  db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则值为NULL
 *  Command：当前线程正在执行的命令。有关线程命令的描述，可详见 “[Examining Thread Information][]” 。
 *  Time：线程处于当前状态的时间（单位秒）。对于从库的SQL线程，该字段的值表示上次复制事件的时间和从库机器的实际时间之间经过了多少秒。详见 “[Replication Implementation Details][]”
 *  State：指示线程正在执行的操作、事件或状态。大多数State对应于非常快速的操作。如果线程在给定状态下很久，则需要排查下。
 *  Info：当前线程正在执行的语句，如果未执行任何语句则值为NULL。该语句可能是发送到服务器的那条语句，也可能是内部的语句（如果某个语句执行了其他语句）。例如一条CALL语句执行了一条正在执行SELECT语句的存储过程，则Info字段会展示SELECT语句。

Command取值：

> **TIPS**  
> [参考 Thread Command Values][Thread Command Values]

*  Binlog Dump：主库上的线程，用于将binlog内容发送到从库
 *  Change user：线程正在执行更改用户操作
 *  Close stmt：线程正在关闭一个prepared statement
 *  Connect：一个复制从库已连接到其主库
 *  Connect Out：一个复制从库正在连接到其主库
 *  Create DB：线程正在执行create-database操作
 *  Daemon：服务器内部线程，而非为客户端连接提供服务的线程
 *  Debug：该线程正在生成调试信息
 *  Delayed insert：该线程是延迟插入处理程序
 *  Drop DB：线程正在执行drop-database操作。
 *  Error：你懂的
 *  Execute：线程正在执行一个prepared statement
 *  Fetch：正在从Prepared Statement 中获取执行结果
 *  Field List：该线程正在获取表的字段信息
 *  Init DB：线程正在选择默认数据库。
 *  Kill：该线程正在杀死另一个线程
 *  Long Data：正在从prepared statement中检索long data
 *  Ping：线程正在处理server-ping请求。
 *  Prepare：该线程正在准备一个prepared statement
 *  Processlist：该线程正在生成服务器线程相关信息
 *  Query：线程正在执行一条语句
 *  Quit：线程正在终止
 *  Refresh：该线程是刷新表，日志或缓存；或者正在重置状态变量或在复制服务器信息。
 *  Register Slave：该线程正在注册一个从库
 *  Reset stmt：线程正在重置prepared statement
 *  Set option：线程正在设置或重置client statement-execution选项
 *  Shutdown：线程正在关闭服务器
 *  Sleep：线程正在等待客户端向其发送statement
 *  Statistics：该线程正在生成服务器状态信息
 *  Table Dump：线程正在将表内容发送到从属服务器。
 *  Time：Unused

State取值：

State的取值非常多，有一两百个，这里就不展开了，读者可直接前往官方文档查询。详见：

*  [General Thread States][]
 *  [Replication Master Thread States][]
 *  [Replication Slave I/O Thread States][Replication Slave I_O Thread States]
 *  [Replication Slave SQL Thread States][]
 *  [Replication Slave Connection Thread States][]
 *  [NDB Cluster Thread States][]
 *  [Event Scheduler Thread States][]

等价操作：

下面两个命令作用等价：

SHOW FULL PROCESSLIST
    SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

事实上，SHOW PROCESSLIST的结果就是从INFORMATION\_SCHEMA.PROCESSLIST表中获取的。

实用SQL：

分享几个操作 `INFORMATION_SCHEMA.PROCESSLIST` 表的实用SQL。

-- 按照客户端IP分组，看哪个客户端的连接数最多
    select client_ip, count(client_ip) as client_num
    from (select substring_index(host, ':', 1) as client_ip
          from `information_schema`.processlist) as connect_info
    group by client_ip
    order by client_num desc;
    
    -- 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
    select *
    from `information_schema`.processlist
    where Command != 'Sleep'
    order by Time desc;
    
    -- 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
    select concat('kill ', id, ';')
    from `information_schema`.processlist
    where Command != 'Sleep'
      and Time > 300
    order by Time desc;

参考文档：

*  [SHOW PROCESSLIST Statement][]
 *  [mysql: show processlist 详解][mysql_ show processlist]

### SHOW STATUS ###

作用：查看服务器相关信息。返回结果解读详见：[Server Status Variables][]

语法：

SHOW [GLOBAL | SESSION] STATUS
        [LIKE 'pattern' | WHERE expr]

示例：

SHOW STATUS;
    SHOW GLOBAL STATUS like '%Slow%'

参考文档：

[SHOW STATUS Statement][]

### SHOW VARIABLES ###

作用：查看MySQL的变量，内容解读详见： [Server System Variables][]

语法：

SHOW [GLOBAL | SESSION] VARIABLES
        [LIKE 'pattern' | WHERE expr]

示例：

SHOW VARIABLES;

参考文档：

[SHOW VARIABLES Statement][SHOW STATUS Statement]

### SHOW TABLE STATUS ###

作用：查看表以及视图的状态

语法：

SHOW TABLE STATUS
        [{
       FROM | IN} db_name]
        [LIKE 'pattern' | WHERE expr]

示例：

SHOW TABLE STATUS from employees;

参考文档：

[SHOW TABLE STATUS Statement][]

### SHOW INDEX ###

作用：查看索引相关信息

语法：

SHOW [EXTENDED] {
       INDEX | INDEXES | KEYS}
        {
       FROM | IN} tbl_name
        [{
       FROM | IN} db_name]
        [WHERE expr]

示例：

SHOW INDEX FROM mytable FROM mydb;
    SHOW INDEX FROM mydb.mytable;

参考文档：

[SHOW INDEX Statement][]

### SHOW ENGINE ###

作用：展示有关存储引擎的相关信息。

语法：

SHOW ENGINE engine_name {
       STATUS | MUTEX}

示例：

-- 有关innodb的内容解读详见：https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.html
    SHOW ENGINE INNODB STATUS
    SHOW ENGINE INNODB MUTEX

参考文档：

[SHOW ENGINE Statement][SHOW INDEX Statement]

### SHOW MASTER STATUS ###

作用：展示有关master binlog文件的相关信息

语法：

SHOW MASTER STATUS

示例：

SHOW MASTER STATUS

参考文档：

[SHOW MASTER STATUS Statement][]

### SHOW SLAVE STATUS ###

作用：展示slave线程的相关信息

语法：

SHOW SLAVE STATUS [FOR CHANNEL channel]

示例：

SHOW SLAVE STATUS

参考文档：

[SHOW SLAVE STATUS Statement][]

### SHOW PROCEDURE ###

作用：返回存储过程相关信息

语法：

SHOW PROCEDURE STATUS
        [LIKE 'pattern' | WHERE expr]

示例：

SHOW PROCEDURE STATUS LIKE 'sp1'

参考文档：

[SHOW PROCEDURE STATUS Statement][]

### SHOW FUNCTION STATUS ###

作用：查看函数相关信息

语法：

SHOW FUNCTION STATUS
        [LIKE 'pattern' | WHERE expr]

示例：

SHOW FUNCTION STATUS

参考文档：

[SHOW FUNCTION STATUS Statement][]

### SHOW TRIGGERS ###

作用：查看触发器相关信息

语法：

SHOW TRIGGERS
        [{
       FROM | IN} db_name]
        [LIKE 'pattern' | WHERE expr]

示例：

SHOW TRIGGERS LIKE 'acc%'

参考文档：

[SHOW TRIGGERS Statement][]

### SHOW WARNINGS ###

作用：展示error、warning、note级别的诊断信息

语法：

SHOW WARNINGS [LIMIT [offset,] row_count]
    SHOW COUNT(*) WARNINGS

示例：

mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4));
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> INSERT INTO t1 VALUES(10,'mysql'), (NULL,'test'), (300,'xyz');
    Query OK, 3 rows affected, 3 warnings (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 3
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Warning
       Code: 1265
    Message: Data truncated for column 'b' at row 1
    *************************** 2. row ***************************
      Level: Warning
       Code: 1048
    Message: Column 'a' cannot be null
    *************************** 3. row ***************************
      Level: Warning
       Code: 1264
    Message: Out of range value for column 'a' at row 3
    3 rows in set (0.00 sec)

参考文档：

[SHOW WARNINGS Statement][]

### SHOW ERRORS ###

作用：展示error级别的诊断信息，和show warnings类似。

语法：

SHOW ERRORS [LIMIT [offset,] row_count]
    SHOW COUNT(*) ERRORS

示例：

SHOW COUNT(*) ERRORS;
    SELECT @@error_count;

参考文档：

[SHOW ERRORS Statement][]

### SHOW BINARY LOGS ###

作用：列出服务器上的所有binary log

语法：

SHOW BINARY LOGS
    SHOW MASTER LOGS

示例：

SHOW BINARY LOGS;

参考文档：  
[SHOW BINARY LOGS Statement][]

### SHOW BINLOG EVENTS ###

作用：查看binary log中的事件

语法：

SHOW BINLOG EVENTS
       [IN 'log_name']
       [FROM pos]
       [LIMIT [offset,] row_count]

示例：

SHOW BINLOG EVENTS

参考文档：  
[SHOW BINLOG EVENTS Statement][]

### SHOW RELAYLOG EVENTS ###

作用：查看复制从库的relay log事件相关信息

语法：

SHOW RELAYLOG EVENTS
        [IN 'log_name']
        [FROM pos]
        [LIMIT [offset,] row_count]
        [channel_option]
    
    channel_option:
        FOR CHANNEL channel

示例：

SHOW RELAYLOG EVENTS

参考文档：

[SHOW RELAYLOG EVENTS Statement][]

#### 参考文档 ####

本文只列出了MySQL常用的诊断命令，还有一些其他的，详见 [SHOW Statements][]

[PROCESS]: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process
[CONNECTION_ADMIN]: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_connection-admin
[KILL]: https://dev.mysql.com/doc/refman/8.0/en/kill.html
[Examining Thread Information]: https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html
[Replication Implementation Details]: https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html
[Thread Command Values]: https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html
[General Thread States]: https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
[Replication Master Thread States]: https://dev.mysql.com/doc/refman/8.0/en/master-thread-states.html
[Replication Slave I_O Thread States]: https://dev.mysql.com/doc/refman/8.0/en/slave-io-thread-states.html
[Replication Slave SQL Thread States]: https://dev.mysql.com/doc/refman/8.0/en/slave-sql-thread-states.html
[Replication Slave Connection Thread States]: https://dev.mysql.com/doc/refman/8.0/en/slave-connection-thread-states.html
[NDB Cluster Thread States]: https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-thread-states.html
[Event Scheduler Thread States]: https://dev.mysql.com/doc/refman/8.0/en/event-scheduler-thread-states.html
[SHOW PROCESSLIST Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html
[mysql_ show processlist]: https://zhuanlan.zhihu.com/p/30743094
[Server Status Variables]: https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
[SHOW STATUS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-variables.html
[Server System Variables]: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
[SHOW TABLE STATUS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html
[SHOW INDEX Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-engine.html
[SHOW MASTER STATUS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-master-status.html
[SHOW SLAVE STATUS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-slave-status.html
[SHOW PROCEDURE STATUS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-procedure-status.html
[SHOW FUNCTION STATUS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-function-status.html
[SHOW TRIGGERS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-triggers.html
[SHOW WARNINGS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-warnings.html
[SHOW ERRORS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-errors.html
[SHOW BINARY LOGS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-binary-logs.html
[SHOW BINLOG EVENTS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-binlog-events.html
[SHOW RELAYLOG EVENTS Statement]: https://dev.mysql.com/doc/refman/8.0/en/show-relaylog-events.html
[SHOW Statements]: https://dev.mysql.com/doc/refman/8.0/en/show.html