MySQL 数据库诊断命令 | Eddie'Blog
MySQL 数据库诊断命令

MySQL 数据库诊断命令

eddie 341 2021-05-13

目录

MySQL-数据库诊断命令

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 (或已废弃的 SUPER )权限的账户使用,从而确保管理员始终能够连接并检查系统。

可使用 KILL 语句杀死线程。

语法:

SHOW [FULL] PROCESSLIST

示例:

mysql> SHOW FULL PROCESSLIST;  
+----+-----------------+-----------+-----------+---------+--------+------------------------+-----------------------+
| Id | User            | Host      | db        | Command | Time   | State                  | Info                  |
+----+-----------------+-----------+-----------+---------+--------+------------------------+-----------------------+
|  4 | event_scheduler | localhost | NULL      | Daemon  | 255008 | Waiting on empty queue | NULL                  |
| 23 | root            | localhost | employees | Query   |      0 | starting               | SHOW FULL PROCESSLIST |
+----+-----------------+-----------+-----------+---------+--------+------------------------+-----------------------+
2 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

  • 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的取值非常多,有一两百个,这里就不展开了,读者可直接前往官方文档查询。详见:

等价操作:

下面两个命令作用等价:

mysql> SHOW FULL PROCESSLIST;  
+----+-----------------+-----------+-----------+---------+--------+------------------------+-----------------------+
| Id | User            | Host      | db        | Command | Time   | State                  | Info                  |
+----+-----------------+-----------+-----------+---------+--------+------------------------+-----------------------+
|  4 | event_scheduler | localhost | NULL      | Daemon  | 255008 | Waiting on empty queue | NULL                  |
| 23 | root            | localhost | employees | Query   |      0 | starting               | SHOW FULL PROCESSLIST |
+----+-----------------+-----------+-----------+---------+--------+------------------------+-----------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+----+-----------------+-----------+-----------+---------+--------+------------------------+----------------------------------------------+
| ID | USER            | HOST      | DB        | COMMAND | TIME   | STATE                  | INFO                                         |
+----+-----------------+-----------+-----------+---------+--------+------------------------+----------------------------------------------+
|  4 | event_scheduler | localhost | NULL      | Daemon  | 255494 | Waiting on empty queue | NULL                                         |
| 23 | root            | localhost | employees | Query   |      0 | executing              | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST |
+----+-----------------+-----------+-----------+---------+--------+------------------------+----------------------------------------------+
2 rows in set (0.00 sec)

事实上,SHOW PROCESSLIST的结果就是从INFORMATION_SCHEMA.PROCESSLIST表中获取的。

实用SQL:

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

-- 按照客户端IP分组,看哪个客户端的连接数最多
mysql> 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;
+-----------+------------+
| client_ip | client_num |
+-----------+------------+
| localhost |          2 |
+-----------+------------+
1 row in set (0.00 sec)


-- 查看正在执行的线程,并按 Time 倒排序,看看有没有执行时间特别长的线程

mysql> select * from `information_schema`.processlist where Command != 'Sleep' order by Time desc;
+----+-----------------+-----------+-----------+---------+--------+------------------------+--------------------------------------------------------------------------------------------+
| ID | USER            | HOST      | DB        | COMMAND | TIME   | STATE                  | INFO                                                                                       |
+----+-----------------+-----------+-----------+---------+--------+------------------------+--------------------------------------------------------------------------------------------+
|  4 | event_scheduler | localhost | NULL      | Daemon  | 255695 | Waiting on empty queue | NULL                                                                                       |
| 23 | root            | localhost | employees | Query   |      0 | executing              | select * from `information_schema`.processlist where Command != 'Sleep' order by Time desc |
+----+-----------------+-----------+-----------+---------+--------+------------------------+--------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)



-- 找出所有执行时间超过 5 分钟的线程,拼凑出 kill 语句,方便后面查杀
mysql> select concat('kill ', id, ';') from `information_schema`.processlist where Command != 'Sleep'   and Time > 300 order by Time desc;
+--------------------------+
| concat('kill ', id, ';') |
+--------------------------+
| kill 4;                  |
+--------------------------+
1 row in set (0.01 sec)

参考文档:

SHOW STATUS

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

语法:

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

示例:

mysql> SHOW STATUS;

mysql> SHOW GLOBAL STATUS like '%Slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 178   |
+---------------------+-------+
2 rows in set (0.00 sec)

参考文档:

SHOW STATUS Statement

SHOW VARIABLES

作用:查看MySQL的变量,内容解读详见: Server System Variables

语法:

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

示例:

mysql> SHOW VARIABLES;

mysql> SHOW GLOBAL VARIABLES like '%time%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| binlog_max_flush_queue_time       | 0        |
| connect_timeout                   | 10       |
| default_password_lifetime         | 0        |
| delayed_insert_timeout            | 300      |
| explicit_defaults_for_timestamp   | ON       |
| flush_time                        | 0        |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_old_blocks_time            | 1000     |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lc_time_names                     | en_US    |
| lock_wait_timeout                 | 31536000 |
| log_timestamps                    | UTC      |
| long_query_time                   | 0.001000 |
| max_execution_time                | 0        |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| regexp_time_limit                 | 32       |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| slow_launch_time                  | 2        |
| system_time_zone                  | UTC      |
| time_zone                         | +08:00   |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+
33 rows in set (0.01 sec)

参考文档:

SHOW VARIABLES Statement

SHOW TABLE STATUS

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

语法:

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

示例:

mysql> SHOW TABLE STATUS from employees;
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name                 | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| current_dept_emp     | NULL   |    NULL | NULL       |    NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2021-05-10 20:18:06 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| departments          | InnoDB |      10 | Dynamic    |       9 |           1820 |       16384 |               0 |        16384 |         0 |           NULL | 2021-05-10 20:18:06 | 2021-05-10 20:18:06 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| dept_emp             | InnoDB |      10 | Dynamic    |  331143 |             36 |    12075008 |               0 |      5783552 |   4194304 |           NULL | 2021-05-10 20:18:06 | 2021-05-10 20:18:24 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| dept_emp_latest_date | NULL   |    NULL | NULL       |    NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2021-05-10 20:18:06 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| dept_manager         | InnoDB |      10 | Dynamic    |      24 |            682 |       16384 |               0 |        16384 |         0 |           NULL | 2021-05-10 20:18:06 | 2021-05-10 20:18:24 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| employees            | InnoDB |      10 | Dynamic    |  299290 |             50 |    15220736 |               0 |            0 |   4194304 |           NULL | 2021-05-10 20:18:06 | 2021-05-10 20:18:20 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| salaries             | InnoDB |      10 | Dynamic    | 2838649 |             33 |    96075776 |               0 |            0 |   4194304 |           NULL | 2021-05-10 20:18:06 | 2021-05-10 20:18:59 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
| titles               | InnoDB |      10 | Dynamic    |  442189 |             46 |    20512768 |               0 |            0 |   4194304 |           NULL | 2021-05-10 20:18:06 | 2021-05-10 20:18:28 | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
8 rows in set (0.02 sec)

参考文档:

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;
mysql> SHOW INDEX FROM employees FROM employees;       
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      299290 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

-- 样式:SHOW INDEX FROM mydb.mytable;
mysql> SHOW INDEX FROM employees.employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      299290 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

参考文档:

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
mysql> SHOW ENGINE INNODB STATUS

mysql> SHOW ENGINE INNODB MUTEX;
+--------+----------------------------+-----------+
| Type   | Name                       | Status    |
+--------+----------------------------+-----------+
| InnoDB | rwlock: fil0fil.cc:3013    | waits=1   |
| InnoDB | rwlock: dict0dict.cc:1035  | waits=3   |
| InnoDB | sum rwlock: buf0buf.cc:781 | waits=129 |
+--------+----------------------------+-----------+
3 rows in set (0.00 sec)

参考文档:

SHOW ENGINE Statement

SHOW MASTER STATUS

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

语法:

mysql> SHOW MASTER STATUS;
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000003 | 204139227 |              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

示例:

mysql> SHOW MASTER STATUS;
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000003 | 204139227 |              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

参考文档:

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.03 sec)

mysql> INSERT INTO t1 VALUES(10,'mysql'), (NULL,'test'), (300,'xyz');
ERROR 1406 (22001): Data too long for column 'b' at row 1
mysql> 
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Error
   Code: 1406
Message: Data too long for column 'b' at row 1
1 row in set (0.00 sec)

参考文档:

SHOW WARNINGS Statement

SHOW ERRORS

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

语法:

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

示例:

mysql> SHOW COUNT(*) ERRORS;
+-----------------------+
| @@session.error_count |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

参考文档:

SHOW ERRORS Statement

SHOW BINARY LOGS

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

语法:

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3084717 | No        |
| binlog.000002 |      4695 | No        |
| binlog.000003 | 204139449 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SHOW MASTER LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3084717 | No        |
| binlog.000002 |      4695 | No        |
| binlog.000003 | 204139449 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

示例:

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3084717 | No        |
| binlog.000002 |      4695 | No        |
| binlog.000003 | 204139449 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

参考文档:
SHOW BINARY LOGS 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诊断命令大全