Terminate Connection in MySQL

When working with MySQL, you may sometimes need to terminate specific connections, especially if they are causing performance issues, consuming unnecessary resources, or stuck in a sleep state. This tutorial explains how to terminate a connection in MySQL.

To check all active MySQL connections, use:

SHOW PROCESSLIST;

This command provides details such as the connection ID, user, host, database, command type, execution time, and current state. Here's an example output:

+----+-----------------+-----------------+--------+---------+------+------------------------+------------------+---------+-----------+---------------+
| Id | User            | Host            | db     | Command | Time | State                  | Info             | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------------+--------+---------+------+------------------------+------------------+---------+-----------+---------------+
|  6 | event_scheduler | localhost       | NULL   | Daemon  |  672 | Waiting on empty queue | NULL             |  671571 |         0 |             0 |
| 16 | myuser          | 127.0.0.1:43718 | testdb | Sleep   |  107 |                        | NULL             |  106655 |         0 |             0 |
| 18 | myuser          | 127.0.0.1:37704 | testdb | Sleep   |   56 |                        | NULL             |   55153 |         1 |             1 |
| 19 | myuser          | 127.0.0.1:58180 | testdb | Sleep   |   55 |                        | NULL             |   55083 |         0 |             0 |
| 20 | root            | 127.0.0.1:38062 | NULL   | Query   |    0 | init                   | SHOW PROCESSLIST |       0 |         0 |             0 |
+----+-----------------+-----------------+--------+---------+------+------------------------+------------------+---------+-----------+---------------+

1. Single connection

Once you identify the connection you want to terminate, use the KILL command followed by the connection ID. For example, to kill connection 16:

KILL 16;

2. Multiple connections

There isn't a single command to terminate all open connections at once, we can generate a series of queries to accomplish this:

SELECT CONCAT('KILL ', id, ';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE `User` = 'myuser'
AND `db` = 'testdb';

This query outputs:

+--------------------------+
| CONCAT('KILL ', id, ';') |
+--------------------------+
| KILL 16;                 |
| KILL 18;                 |
| KILL 19;                 |
+--------------------------+

You can then copy and execute these KILL commands to terminate all matching connections.