How to terminate active user connection to MySQL or MariaDB server

Managing user connections effectively is crucial in database administration, especially when troubleshooting performance issues or during maintenance. Active connections to a MySQL or MariaDB server can sometimes become problematic, consuming system resources or holding locks for extended periods.

There are instances when administrators might need to forcibly disconnect an active user to either free up resources or prevent potential harm. Both MySQL and MariaDB provide built-in tools that allow administrators to view active connections and, if necessary, terminate specific ones.

Whether it's to mitigate a slow query or to perform routine maintenance tasks, following the steps below will guide you in terminating an active user connection to your MySQL or MariaDB server.

1. Log into the MySQL or MariaDB server using the MySQL client.

   $ mysql -u root -p
   Enter password:

2. List all active connections with their respective connection IDs.

   SHOW PROCESSLIST;

   This will display a table with active connections, the user associated with each connection, and the corresponding ID. Take note of the ID of the connection you wish to terminate.

3. Terminate a specific user connection using its connection ID.

   KILL [connection_ID];

   Be cautious when terminating connections. Ensure that the connection you are terminating is not in the middle of a crucial transaction.

4. Confirm that the connection has been terminated.

   SHOW PROCESSLIST;

   The terminated connection should no longer appear in the list.

5. Exit the MySQL or MariaDB client.

   EXIT;

By following the above steps, you can effectively manage and control active connections to your MySQL or MariaDB server, ensuring optimal performance and security for your database system.