How to terminate an active user connection in MySQL or MariaDB

Terminating a stuck or unwanted database connection releases locks, frees resources, and unblocks maintenance when an application session is misbehaving or holding a transaction open.

Each client session to a MySQL or MariaDB server is represented internally as a server thread with a numeric Id, visible via SHOW (FULL) PROCESSLIST or the INFORMATION_SCHEMA.PROCESSLIST view. The KILL command targets that Id to either interrupt a running statement or drop the entire connection.

Forcibly killing sessions is disruptive: open transactions are rolled back, in-flight writes are aborted, and applications may retry aggressively. Terminating other users’ sessions typically requires elevated privileges (for example, SUPER or CONNECTION_ADMIN depending on server and version), so use KILL QUERY first when only a runaway statement needs stopping.

1. Open the mysql client as an account permitted to view and terminate other sessions.

   $ mysql --user=root --password
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 19
   ##### snipped #####
   mysql>

2. Print the current session thread ID to avoid terminating the active admin session.

   mysql> SELECT CONNECTION_ID();
   +-----------------+
   | CONNECTION_ID() |
   +-----------------+
   |              19 |
   +-----------------+
   1 row in set (0.00 sec)

3. List active server threads to identify the Id of the target session.

   mysql> SHOW FULL PROCESSLIST;
   +----+------+-----------+------+---------+------+----------------------+----------------------------------------------+
   | Id | User | Host      | db   | Command | Time | State                | Info                                         |
   +----+------+-----------+------+---------+------+----------------------+----------------------------------------------+
   | 19 | root | localhost | NULL | Query   |    0 | starting             | SHOW FULL PROCESSLIST                        |
   | 74 | app  | 10.0.0.25 | prod | Query   |   56 | Sending data         | SELECT * FROM orders WHERE status = 'OPEN'   |
   | 88 | app  | 10.0.0.25 | prod | Sleep   |  812 |                      | NULL                                         |
   +----+------+-----------+------+---------+------+----------------------+----------------------------------------------+

   SHOW PROCESSLIST truncates long statements, and SHOW FULL PROCESSLIST shows the full Info text.

4. Filter the process list when only a specific user, host, or database session needs attention.

   mysql> SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 120) AS INFO
       -> FROM INFORMATION_SCHEMA.PROCESSLIST
       -> WHERE USER = 'app' AND DB = 'prod'
       -> ORDER BY TIME DESC;
   +----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
   | ID | USER | HOST      | DB   | COMMAND | TIME | STATE        | INFO                                                     |
   +----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
   | 88 | app  | 10.0.0.25 | prod | Sleep   |  812 |              | NULL                                                     |
   | 74 | app  | 10.0.0.25 | prod | Query   |   56 | Sending data | SELECT * FROM orders WHERE status = 'OPEN'               |
   +----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
   2 rows in set (0.00 sec)

5. Interrupt only the currently running statement for a session using KILL QUERY.

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

   The session remains connected, and the client running the statement receives an interruption error.

6. Disconnect a session entirely using KILL with the session Id.

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

   Disconnecting a session aborts in-flight work and rolls back any open transaction for that connection.

7. Verify that the target thread ID no longer exists.

   mysql> SELECT COUNT(*) AS still_present
       -> FROM INFORMATION_SCHEMA.PROCESSLIST
       -> WHERE ID = 88;
   +---------------+
   | still_present |
   +---------------+
   |             0 |
   +---------------+
   1 row in set (0.00 sec)

8. Exit the mysql client.

   mysql> EXIT;
   Bye