個人中心
退出
MySQL 查看和終止正在運行的連接線程
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
使用 SHOW 命令查看連接線程
使用 information_schema.processlist
使用 performance_schema.threads
使用 mysqladmin 工具查看連接線程
使用 CONNECTION_ID() 函數(shù)查看當前連接
使用 KILL 命令終止線程
使用 mysqladmin 工具終止線程
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師，今天給大家介紹一下如何如何查看和終止（kill）MySQL 的連接線程。
當我們連接 MySQL 服務器遇到“too many connections”這種連接數(shù)過多的錯誤時，或者由于其他原因需要找出目前存在哪些客戶端的連接線程，并進一步進行處理。
使用 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)
查詢結(jié)果中每個字段的意義如下：
Id - 連接的唯一標識；
User - 客戶端的用戶名，event_scheduler 是監(jiān)控計劃事件的線程；
Host - 客戶端的主機名和端口號；
db - 默認的數(shù)據(jù)庫，如果沒有設置顯示為 NULL；
Command - 該線程正在執(zhí)行的命令類型；
Time - 該線程處于當前狀態(tài)的秒數(shù)；
State - 該線程正在執(zhí)行的操作、事件或者狀態(tài)；
Info - 該線程正在執(zhí)行的語句，NULL 表示沒有執(zhí)行任何語句。show processlist;命令只會顯示 Info 字段中的前 100 個字符；如果想全部列出內(nèi)容，可以使用show full processlist;命令。
如果擁有 PROCESS 權(quán)限，可以看到所有用戶的連接線程；否則只能看到使用當前用戶名連接的線程。
另外，我們也可以使用以下命令查看當前連接的數(shù)量：
mysql> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 4 |
+-------------------+-------+
1 row in set (0.01 sec)
該命令返回的連接數(shù)量只包含用戶連接的線程，不包含系統(tǒng)連接的線程。
MySQL 允許的最大連接數(shù)可以使用 max_connections 變量查看：
mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 151 |
+-------------------+
1 row in set (0.00 sec)
mysqld 實際上允許 max_connections + 1 個客戶端連接，其中額外的 1 個連接保留給具有 CONNECTION_ADMIN （以前的 SUPER）權(quán)限的用戶，以便連接數(shù)過多時管理員仍然可以連接服務器診斷問題。
使用 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 表中的數(shù)據(jù)與SHOW FULL PROCESSLIST命令的輸出結(jié)果相同。
使用 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)
查詢結(jié)果返回了 46 條記錄。其中，THREAD_ID 是線程的唯一標識；PROCESSLIST_ID 對應了SHOW PROCESSLIST語句和 information_schema.processlist 表中的 id；PROCESSLIST_USER、PROCESSLIST_HOST 等字段也是如此。
performance_schema.threads 表和其他方式存在一些不同之處：
對服務器的性能影響更小，因為訪問該表不需要 mutex 互斥鎖；
提供了更多的信息，例如線程屬于前臺還是后臺線程，線程在服務器中的位置等；
提供了后臺線程的信息，可以用于 DBA 執(zhí)行監(jiān)控；
可以啟用或者禁用線程監(jiān)控和歷史事件記錄。
使用 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 |
+----+-----------------+--------------------+------+---------+--------+------------------------+------------------+
從返回結(jié)果可以看出， mysqladmin processlist命令實際上是調(diào)用了SHOW PROCESSLIST語句。如果給該命令加上–version 或者 -V 選項，實際調(diào)用的是SHOW FULL PROCESSLIST語句。
使用 CONNECTION_ID() 函數(shù)查看當前連接
使用 CONNECTION_ID() 函數(shù)可以查看當前會話的線程 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終止該連接正在執(zhí)行的語句之后終止連接線程，這是默認值；
KILL QUERY終止該連接正在執(zhí)行的語句，但不會終止連接線程。
我們終止線程 id 為 33 的連接：
mysql> kill 33;
Query OK, 0 rows affected (0.02 sec)
此時，如果我們在被終止連接的客戶端執(zhí)行任何語句，都會得到以下錯誤信息：
mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
終止連接線程和語句需要一定的權(quán)限：
如果沒有 CONNECTION_ADMIN（以前的 SUPER）權(quán)限，只能終止自己的線程和語句；
如果擁有 CONNECTION_ADMIN（以前的 SUPER）權(quán)限，可以終止所有的線程和語句；如果終止的是以 SYSTEM_USER 權(quán)限運行的線程和語句，執(zhí)行命令的會話也必須具有 SYSTEM_USER 權(quán)限。
使用 mysqladmin 工具終止線程
另一種終止線程的方式是使用 mysqladmin 工具。以下命令可以終止 id 為 34 和 35 的連接線程：
[root@sqlhost ~]# mysqladmin -u root -p kill 34,35
Enter password:
注意，逗號前后沒有空格。
定期更新數(shù)據(jù)庫領域相關(guān)文章，歡迎關(guān)注??、評論??、點贊??！
分類導航