数据库监控与调优【七】—— MySQL数据库诊断命令

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 语句杀死线程。

语法：

## 不写FULL返回结果里的info字段只会展示前100个字符，超过100个字符会被截断，写了FULL就不会，完整展示
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

• 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 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 详解

SHOW STATUS

作用：查看MYSQL服务器相关信息。返回结果解读详见：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 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
-- 查看INNODB这款数据库引擎相关信息
SHOW ENGINE INNODB STATUS;
SHOW ENGINE INNODB MUTEX;                                                        

参考文档：

SHOW ENGINE 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文章来源地址https://www.toymoban.com/news/detail-503309.html

到了这里，关于数据库监控与调优【七】—— MySQL数据库诊断命令的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！