Mass killing MySQL connections

When MySQL has too many connections and slows the machine, or a database table becomes locked, we need to restore service with as little downtime as possible. My approach is to kill stalled connections, focusing on Query connections that have run longer than others or have Locked status. I avoid Update and Delete connections to prevent data loss. There are several ways to kill connections. This article covers mass killing, which terminates many connections with just two or three commands. The steps are: 1. Inspect the connections so you can identify which ones to kill Check which connections need terminating with select * from information_schema.processlist; or show processlist; Example of select * from information_schema.processlist;

mysql> select * from information_schema.processlist;
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| ID   | USER   | HOST      | DB     | COMMAND | TIME | STATE     | INFO                                         |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 9839 | root   | localhost | NULL   | Query   |    0 | executing | select * from information_schema.processlist |
| 9855 | root   | localhost | DB1    | Query   |    0 |           | select * from Table2                         |
| 9879 | root   | localhost | DB1    | Query   | 1000 |   Locked  | select * from Table1                         |
|  162 | User1  | localhost | User1  | Query   |  204 |   Locked  | select * from DBUSER1                        |
|  373 | User1  | localhost | User1  | Query   |  804 |           | select tag from DBUSER1                      |
|  672 | User1  | localhost | User1  | Query   |  504 |           | select ezylinux from DBUSER1                 |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
6 rows in set (0.00 sec)

Example of show processlist;

mysql> show processlist;
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| Id   | User   | Host      | Db     | Command | Time | State     | Info                                         |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 9839 | root   | localhost | NULL   | Query   |    0 | executing | select * from information_schema.processlist |
| 9855 | root   | localhost | DB1    | Query   |    0 |           | select * from Table2                         |
| 9879 | root   | localhost | DB1    | Query   | 1000 |  Locked   | select * from Table1                         |
|  162 | User1  | localhost | User1  | Query   |  204 |  Locked   | select * from DBUSER1                        |
|  373 | User1  | localhost | User1  | Query   |  804 |           | select tag from DBUSER1                      |
|  672 | User1  | localhost | User1  | Query   |  504 |           | select ezylinux from DBUSER1                 |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
6 rows in set (0.00 sec)

2. After identifying the connections to kill, run the command below with the required conditions in the where clause

mysql> select concat('KILL ',id,';') from information_schema.processlist where command='Locked' into outfile '/tmp/kill.txt';

The command above writes the following data to /tmp/kill.txt:

KILL 9879;
KILL 162;

This data is stored as a text file. To execute the commands in that file, use source:

mysql> source /tmp/kill.txt;

Example: Kill Query connections To kill Query connections, run:

mysql> select concat('KILL ',id,';') from information_schema.processlist where command='Query' into outfile '/tmp/querykill.txt';

Then run source /tmp/querykill.txt;

mysql> source /tmp/querykill.txt;

Example: Kill connections from User1 Run:

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='User1' into outfile '/tmp/kill.txt';

Then run:

mysql> source /tmp/kill.txt;