How to terminate an active user connection in MySQL or MariaDB

Terminating a stuck or unwanted database session frees locks, clears blocked maintenance, and stops an application connection that is holding a transaction open or running a statement that should no longer continue.

In MySQL and MariaDB, every client session has a connection ID in the process list. SHOW FULL PROCESSLIST shows the session, the client host, the current command, how long it has been in that state, and the SQL text when available. KILL QUERY cancels only the statement currently running on that session, while KILL CONNECTION removes the session itself.

Visibility and control depend on privileges. Seeing every thread requires PROCESS, and killing another user's session typically requires CONNECTION_ADMIN or, on older servers, SUPER. Current MySQL releases also require SYSTEM_USER to kill sessions running with that account category. Because disconnecting a live session can roll back open work and trigger application retries, identify the target thread carefully and use KILL QUERY first when stopping the statement is enough.

1. Open the mysql client as an account that can view and terminate the target session.

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

   On hosts configured for local socket authentication, sudo mysql can open an administrative session without prompting for a password.

2. Print the current session ID before touching other threads so you do not disconnect your own administrative session by mistake.

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

3. List visible sessions and identify the Id for the connection you want to stop.

   mysql> SHOW FULL PROCESSLIST;
   +----+----------+-----------+------+---------+------+------------+-----------------------+
   | Id | User     | Host      | db   | Command | Time | State      | Info                  |
   +----+----------+-----------+------+---------+------+------------+-----------------------+
   |  5 | app_user | localhost | NULL | Query   |    2 | User sleep | SELECT SLEEP(300)     |
   |  6 | root     | localhost | NULL | Query   |    0 | starting   | SHOW FULL PROCESSLIST |
   +----+----------+-----------+------+---------+------+------------+-----------------------+
   2 rows in set (0.00 sec)

   SHOW PROCESSLIST truncates the Info column after 100 characters. MariaDB still exposes INFORMATION_SCHEMA.PROCESSLIST for queryable filtering, while current MySQL releases prefer Performance Schema process-list tables because the Information Schema implementation is deprecated.

4. Cancel only the running statement when you want the client to stay connected.

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

   The client running that statement typically receives ERROR 1317 (70100): Query execution was interrupted, but the session itself remains connected.

5. Check the process list again to confirm that the same session ID is still connected and has returned to Sleep.

   mysql> SHOW PROCESSLIST;
   +----+----------+-----------+------+---------+------+----------+------------------+
   | Id | User     | Host      | db   | Command | Time | State    | Info             |
   +----+----------+-----------+------+---------+------+----------+------------------+
   |  5 | app_user | localhost | NULL | Sleep   |    1 |          | NULL             |
   |  6 | root     | localhost | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
   +----+----------+-----------+------+---------+------+----------+------------------+
   2 rows in set (0.00 sec)

6. Disconnect the session entirely when the client itself must be removed.

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

   Disconnecting a session drops that client's connection and rolls back any open transaction for that session.

   Plain KILL 5 is equivalent to KILL CONNECTION 5 if you prefer the shorter form.

7. Verify that the session ID no longer appears in the process list.

   mysql> SHOW PROCESSLIST;
   +----+------+-----------+------+---------+------+----------+------------------+
   | Id | User | Host      | db   | Command | Time | State    | Info             |
   +----+------+-----------+------+---------+------+----------+------------------+
   |  6 | root | localhost | NULL | Query   |    0 | starting | SHOW PROCESSLIST |
   +----+------+-----------+------+---------+------+----------+------------------+
   1 row in set (0.00 sec)

8. Exit the mysql client when finished.

   mysql> EXIT;
   Bye