MySQL 查看和终止正在运行的连接线程

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net

文章目录

        使用 SHOW 命令查看连接线程
        使用 information_schema.processlist
        使用 performance_schema.threads
        使用 mysqladmin 工具查看连接线程
        使用 CONNECTION_ID() 函数查看当前连接
        使用 KILL 命令终止线程
        使用 mysqladmin 工具终止线程

大家好,我是只谈技术不剪发的 Tony 老师,今天给大家介绍一下如何如何查看和终止(kill)MySQL 的连接线程。

当我们连接 MySQL 服务器遇到“too many connections”这种连接数过多的错误时,或者由于其他原因需要找出目前存在哪些客户端的连接线程,并进一步进行处理。
使用 SHOW 命令查看连接线程

每个MySQL 客户端的连接都对应一个服务器端的线程,使用 SHOW PROCESSLIST 命令可以查看连接线程的详细信息:

mysql> show processlist;
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host               | db   | Command | Time   | State                  | Info             |
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+
|  7 | event_scheduler | localhost          | NULL | Daemon  | 389855 | Waiting on empty queue | NULL             |
| 32 | root            | localhost          | NULL | Query   |      0 | starting               | show processlist |
| 33 | root            | 192.168.56.1:54174 | hrdb | Sleep   |     19 |                        | NULL             |
| 34 | root            | 192.168.56.1:54175 | hrdb | Sleep   |      3 |                        | NULL             |
| 35 | root            | 192.168.56.1:54176 | hrdb | Sleep   |      3 |                        | NULL             |
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+
5 rows in set (0.00 sec)


查询结果中每个字段的意义如下:

    Id - 连接的唯一标识;
    User - 客户端的用户名,event_scheduler 是监控计划事件的线程;
    Host - 客户端的主机名和端口号;
    db - 默认的数据库,如果没有设置显示为 NULL;
    Command - 该线程正在执行的命令类型;
    Time - 该线程处于当前状态的秒数;
    State - 该线程正在执行的操作、事件或者状态;
    Info - 该线程正在执行的语句,NULL 表示没有执行任何语句。show processlist;命令只会显示 Info 字段中的前 100 个字符;如果想全部列出内容,可以使用show full processlist;命令。

如果拥有 PROCESS 权限,可以看到所有用户的连接线程;否则只能看到使用当前用户名连接的线程。

另外,我们也可以使用以下命令查看当前连接的数量:

mysql> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 4     |
+-------------------+-------+
1 row in set (0.01 sec)



该命令返回的连接数量只包含用户连接的线程,不包含系统连接的线程。

MySQL 允许的最大连接数可以使用 max_connections 变量查看:

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)



mysqld 实际上允许 max_connections + 1 个客户端连接,其中额外的 1 个连接保留给具有 CONNECTION_ADMIN (以前的 SUPER)权限的用户,以便连接数过多时管理员仍然可以连接服务器诊断问题。
使用 information_schema.processlist

我们也可以通过 information_schema.processlist 表查看连接线程:

mysql> select *
    -> from information_schema.processlist
    -> order by id;
+----+-----------------+--------------------+------+---------+--------+------------------------+----------------------------------------------------------+
| ID | USER            | HOST               | DB   | COMMAND | TIME   | STATE                  | INFO                                                     |
+----+-----------------+--------------------+------+---------+--------+------------------------+----------------------------------------------------------+
|  7 | event_scheduler | localhost          | NULL | Daemon  | 390338 | Waiting on empty queue | NULL                                                     |
| 32 | root            | localhost          | NULL | Query   |      0 | executing              | select *
from information_schema.processlist
order by id |
| 33 | root            | 192.168.56.1:54174 | hrdb | Sleep   |    502 |                        | NULL                                                     |
| 34 | root            | 192.168.56.1:54175 | hrdb | Sleep   |    486 |                        | NULL                                                     |
| 35 | root            | 192.168.56.1:54176 | hrdb | Sleep   |    486 |                        | NULL                                                     |
+----+-----------------+--------------------+------+---------+--------+------------------------+----------------------------------------------------------+
5 rows in set (0.00 sec)



information_schema.processlist 表中的数据与SHOW FULL PROCESSLIST命令的输出结果相同。
使用 performance_schema.threads

performance_schema.threads 表中存储了所有线程的详细信息,包括各种 MySQL 后台服务器线程。例如:

mysql> SELECT * FROM performance_schema.threads;
+-----------+---------------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
| THREAD_ID | NAME                                        | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE          | PROCESSLIST_INFO                                                                                                                                                                                                                                                                                                | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP |
+-----------+---------------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
|         1 | thread/sql/main                             | BACKGROUND |           NULL | NULL             | NULL             | mysql          | NULL                |           391020 | NULL                       | NULL                                                                                                                                                                                                                                                                                                            |             NULL | NULL | YES          | YES     | NULL            |        26586 | SYS_default    |
|         3 | thread/innodb/io_ibuf_thread                | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL                       | NULL                                                                                                                                                                                                                                                                                                            |             NULL | NULL | YES          | YES     | NULL            |        26593 | SYS_default    |
|         4 | thread/innodb/io_log_thread                 | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL                       | NULL                                                                                                                                                                                                                                                                                                            |             NULL | NULL | YES          | YES     | NULL            |        26594 | SYS_default    |
|         5 | thread/innodb/io_read_thread                | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL                       | NULL                                                                                                                                                                                                                                                                                                            |             NULL | NULL | YES          | YES     | NULL            |        26595 | SYS_default    |
...
|        72 | thread/sql/one_connection                   | FOREGROUND |             32 | root             | localhost        | NULL           | Query               |                0 | executing                  | SELECT * FROM performance_schema.threads                                                                                                                                                                                                                                                                        |             NULL | NULL | YES          | YES     | Socket          |        17087 | USR_default    |
|        73 | thread/sql/one_connection                   | FOREGROUND |             33 | root             | 192.168.56.1     | hrdb           | Sleep               |             1184 | NULL                       | /* ApplicationName=DBeaver 7.0.4 - Main */ SELECT DATABASE()                                                                                                                                                                                                                                                    |             NULL | NULL | YES          | YES     | TCP/IP          |        22232 | USR_default    |
|        74 | thread/sql/one_connection                   | FOREGROUND |             34 | root             | 192.168.56.1     | hrdb           | Sleep               |             1168 | NULL                       | /* ApplicationName=DBeaver 7.0.4 - Metadata */ SELECT kc.CONSTRAINT_NAME,kc.TABLE_NAME,kc.COLUMN_NAME,kc.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc WHERE kc.TABLE_SCHEMA='hrdb' AND kc.REFERENCED_TABLE_NAME IS NULL AND kc.TABLE_NAME='job'
ORDER BY kc.CONSTRAINT_NAME,kc.ORDINAL_POSITION |             NULL | NULL | YES          | YES     | TCP/IP          |        22231 | USR_default    |
|        75 | thread/sql/one_connection                   | FOREGROUND |             35 | root             | 192.168.56.1     | hrdb           | Sleep               |             1168 | NULL                       | /* ApplicationName=DBeaver 7.0.4 - SQLEditor <Script-19.sql> */ SELECT DATABASE()                                                                                                                                                                                                                               |             NULL | NULL | YES          | YES     | TCP/IP          |        22229 | USR_default    |
+-----------+---------------------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
46 rows in set (0.00 sec)



查询结果返回了 46 条记录。其中,THREAD_ID 是线程的唯一标识;PROCESSLIST_ID 对应了SHOW PROCESSLIST语句和 information_schema.processlist 表中的 id;PROCESSLIST_USER、PROCESSLIST_HOST 等字段也是如此。

performance_schema.threads 表和其他方式存在一些不同之处:

    对服务器的性能影响更小,因为访问该表不需要 mutex 互斥锁;
    提供了更多的信息,例如线程属于前台还是后台线程,线程在服务器中的位置等;
    提供了后台线程的信息,可以用于 DBA 执行监控;
    可以启用或者禁用线程监控和历史事件记录。

使用 mysqladmin 工具查看连接线程

mysqladmin 是一个用于 MySQL 服务器管理的客户端工具,支持查看连接线程信息:

[root@sqlhost ~]#  mysqladmin -u root -p processlist
Enter password:
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host               | db   | Command | Time   | State                  | Info             |
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+
| 7  | event_scheduler | localhost          |      | Daemon  | 390747 | Waiting on empty queue |                  |
| 32 | root            | localhost          |      | Sleep   | 409    |                        |                  |
| 33 | root            | 192.168.56.1:54174 | hrdb | Sleep   | 911    |                        |                  |
| 34 | root            | 192.168.56.1:54175 | hrdb | Sleep   | 895    |                        |                  |
| 35 | root            | 192.168.56.1:54176 | hrdb | Sleep   | 895    |                        |                  |
| 37 | root            | localhost          |      | Query   | 0      | starting               | show processlist |
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+



从返回结果可以看出, mysqladmin processlist命令实际上是调用了SHOW PROCESSLIST语句。如果给该命令加上–version 或者 -V 选项,实际调用的是SHOW FULL PROCESSLIST语句。
使用 CONNECTION_ID() 函数查看当前连接

使用 CONNECTION_ID() 函数可以查看当前会话的线程 id:

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              32 |
+-----------------+
1 row in set (0.00 sec)


使用 KILL 命令终止线程

使用 KILL 命令可以终止服务器端的连接线程:

KILL [CONNECTION | QUERY] pid;


其中,pid 是通过上述各种方式获取到的连接标识。KILL支持两个选项:

    KILL CONNECTION终止该连接正在执行的语句之后终止连接线程,这是默认值;
    KILL QUERY终止该连接正在执行的语句,但不会终止连接线程。

我们终止线程 id 为 33 的连接:

mysql> kill 33;
Query OK, 0 rows affected (0.02 sec)



此时,如果我们在被终止连接的客户端执行任何语句,都会得到以下错误信息:

mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away



终止连接线程和语句需要一定的权限:

    如果没有 CONNECTION_ADMIN(以前的 SUPER)权限,只能终止自己的线程和语句;
    如果拥有 CONNECTION_ADMIN(以前的 SUPER)权限,可以终止所有的线程和语句;如果终止的是以 SYSTEM_USER 权限运行的线程和语句,执行命令的会话也必须具有 SYSTEM_USER 权限。

使用 mysqladmin 工具终止线程

另一种终止线程的方式是使用 mysqladmin 工具。以下命令可以终止 id 为 34 和 35 的连接线程:

[root@sqlhost ~]#  mysqladmin -u root -p kill 34,35
Enter password:



注意,逗号前后没有空格。

定期更新数据库领域相关文章,欢迎关注❤️、评论📝、点赞👍!