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.
