每日MySQL之022：使用SHOW PROCESSLIST查看应用与使用KILL杀掉应用

1. 查看应用

MySQL中，每一个连接进来，都会对应一个独立的线程。可以使用mysqladmin命令的processlist参数来查看所有应用，或者使用SHOW PROCESSLIST命令；要查看当前的连接，可以使用CONNECTION_ID() 函数:

root@db2a:~# mysqladmin -pqingsong processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+------------+----+---------+------+----------+------------------+
| Id | User | Host       | db | Command | Time | State    | Info             |
+----+------+------------+----+---------+------+----------+------------------+
| 25 | root | localhost  |    | Sleep   | 56   |          |                  |
| 26 | root | localhost  |    | Sleep   | 44   |          |                  |
| 27 | root | db2b:51470 |    | Sleep   | 25   |          |                  |
| 28 | root | localhost  |    | Query   | 0    | starting | show processlist |
+----+------+------------+----+---------+------+----------+------------------+

root@db2a:~# mysql
..
mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host       | db   | Command | Time | State    | Info             |
+----+------+------------+------+---------+------+----------+------------------+
| 25 | root | localhost  | NULL | Sleep   |   86 |          | NULL             |
| 26 | root | localhost  | NULL | Sleep   |   74 |          | NULL             |
| 27 | root | db2b:51470 | NULL | Sleep   |   55 |          | NULL             |
| 29 | root | localhost  | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

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

2. 杀掉应用

可以使用KILL命令，或者mysqladmin的KILL参数，杀掉某个连接，语法如下：

KILL [CONNECTION | QUERY] processlist_id

它有 CONNECTION 和 QUERY 两种模式，默认为CONNECTION，会将整个应用杀掉。而 QUERY 模式会将当前应用正在执行的语句杀掉，应用本身还是保持着连接。查看所有的应用需要 PROCESS 权限，杀掉应用需要 SUPER 权限，否则只能看到和杀掉自己。

发出命令之后，会给相应的线程设置一个kill flag，大部分情况下，线程需要花点时间才能终止，因为kill flag是以固定的周期来检查的。

示例，第一个KILL命令并没有终止连接，第二个和第三个命令终止了连接：

mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host       | db   | Command | Time | State    | Info             |
+----+------+------------+------+---------+------+----------+------------------+
| 25 | root | localhost  | NULL | Sleep   |   86 |          | NULL             |
| 26 | root | localhost  | NULL | Sleep   |   74 |          | NULL             |
| 27 | root | db2b:51470 | NULL | Sleep   |   55 |          | NULL             |
| 29 | root | localhost  | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

mysql> KILL QUERY 25;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host       | db   | Command | Time | State    | Info             |
+----+------+------------+------+---------+------+----------+------------------+
| 25 | root | localhost  | NULL | Sleep   |  616 |          | NULL             |
| 26 | root | localhost  | NULL | Sleep   |  604 |          | NULL             |
| 27 | root | db2b:51470 | NULL | Sleep   |  585 |          | NULL             |
| 29 | root | localhost  | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

mysql> KILL 25;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host       | db   | Command | Time | State    | Info             |
+----+------+------------+------+---------+------+----------+------------------+
| 26 | root | localhost  | NULL | Sleep   |  622 |          | NULL             |
| 27 | root | db2b:51470 | NULL | Sleep   |  603 |          | NULL             |
| 29 | root | localhost  | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

mysql> KILL CONNECTION 27;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 26 | root | localhost | NULL | Sleep   |  674 |          | NULL             |
| 29 | root | localhost | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

注意，一个KILL命令只能杀掉一个应用，如果要在一条命令里杀掉多个，可以考虑使用mysqladmin的kill参数，示例如下：

qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+------------+-------+---------+------+----------+------------------+
| Id | User | Host       | db    | Command | Time | State    | Info             |
+----+------+------------+-------+---------+------+----------+------------------+
| 26 | root | localhost  |       | Sleep   | 878  |          |                  |
| 29 | root | localhost  |       | Sleep   | 184  |          |                  |
| 30 | root | db2b:51472 | test1 | Sleep   | 194  |          |                  |
| 31 | root | db2b:51474 |       | Query   | 0    | starting | show processlist |
+----+------+------------+-------+---------+------+----------+------------------+

qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong kill 26,29,30
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+------------+----+---------+------+----------+------------------+
| Id | User | Host       | db | Command | Time | State    | Info             |
+----+------+------------+----+---------+------+----------+------------------+
| 33 | root | db2b:51478 |    | Query   | 0    | starting | show processlist |
+----+------+------------+----+---------+------+----------+------------------+