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

1. 查看应用

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

root@db2a:~# mysqladmin -pqingsong processlistmysqladmin: [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 processlistmysqladmin: [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,30mysqladmin: [Warning] Using a password on the command line interface can be insecure.qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong processlistmysqladmin: [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 |+----+------+------------+----+---------+------+----------+------------------+