How to kill MySQL connections

How to kill MySQL connections

No, there is no built-in MySQL command for that. There are various tools and scripts that support it, you can kill some connections manually or restart the server (but that will be slower).

Use SHOW PROCESSLIST to view all connections, and KILL the process IDs you want to kill.

You could edit the timeout setting to have the MySQL daemon kill the inactive processes itself, or raise the connection count. You can even limit the amount of connections per username, so that if the process keeps misbehaving, the only affected process is the process itself and no other clients on your database get locked out.

If you cant connect yourself anymore to the server, you should know that MySQL always reserves 1 extra connection for a user with the SUPER privilege. Unless your offending process is for some reason using a username with that privilege…

Then after you can access your database again, you should fix the process (website) thats spawning that many connections.

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+-------+---------------+
| Id  | User | Host            | db   | Command | Time | State | Info      |
+-----+------+-----------------+------+---------+------+-------+----------------+
| 143 | root | localhost:61179 | cds  | Query   |    0 | init  | SHOW PROCESSLIST |
| 192 | root | localhost:53793 | cds  | Sleep   |    4 |       | NULL      |
+-----+------+-----------------+------+---------+------+-------+----------------+
2 rows in set (0.00 sec)

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

USER 192 :

mysql> SELECT * FROM exept;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM exept;
ERROR 2013 (HY000): Lost connection to MySQL server during query

How to kill MySQL connections

While you cant kill all open connections with a single command, you can create a set of queries to do that for you if there are too many to do by hand.

This example will create a series of KILL <pid>; queries for all some_users connections from 192.168.1.1 to my_db.

SELECT 
CONCAT(KILL , id, ;) 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE `User` = some_user 
AND `Host` = 192.168.1.1
AND `db` = my_db;

Leave a Reply

Your email address will not be published. Required fields are marked *