Kill process in MySQL processlist

0
628
MySQL Database
MySQL Database

When you want to diagnose MySQL database, you might want to have a look at all current connection or processes show up on process list, which can be done via this query:


mysql> SHOW PROCESSLIST;
+----+------+-----------------+--------------+---------+------+----------+------------------+
| Id | User | Host            | db           | Command | Time | State    | Info             |
+----+------+-----------------+--------------+---------+------+----------+------------------+
| 26 | root | localhost:62441 | NULL         | Query   |    0 | starting | SHOW PROCESSLIST |
| 27 | root | localhost:62446 | users_db     | Sleep   |    5 |          | NULL             |
| 28 | root | localhost:62447 | users_db     | Sleep   |    6 |          | NULL             |
+----+------+-----------------+--------------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

To kill or terminate a connection, just use command KILL with correspondent connection ID.


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

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

In practice, you might want to kill more than one connections at a time. However, it should follow a condition, because you cannot just kill all connections.
For example, you might want to kill any connection that has been connected for more than 24 hours.

Here it is:


mysql> SELECT CONCAT('KILL', Id, ';') FROM information_schema.processlist WHERE time > 86400 INTO OUTFILE '/tmp/kill_list.txt';
Query OK, 2 rows affected (0.00 sec)

mysql> SOURCE /tmp/kill_list.txt;
Query OK, 0 rows affected (0.00 sec)

Since MySQL does not provide any mechanism to kill connections in mass, so we need to generate kill list and save into a file, and then load that SQL file to execute.
This is a MySQL tip from Percona blog.