How to terminate active user connection to MySQL or MariaDB server

Databases play a crucial role in managing and storing data for various applications. MySQL and MariaDB are two popular relational database management systems that are widely used for their performance, scalability, and robustness. In certain scenarios, such as when an application is malfunctioning or when a user is hogging resources, it may become necessary to terminate active user connections to the database server. This helps ensure the stability and performance of the system and prevents potential data corruption or loss.

In MySQL and MariaDB, each user connection corresponds to a specific process, identified by a unique process ID. To terminate an active user connection, you must first identify the process ID associated with that connection, and then execute the appropriate command to kill the process. Administrators should exercise caution when terminating connections, as abrupt disconnection can lead to data loss or inconsistencies.

This guide will walk you through the process of terminating active user connections to a MySQL or MariaDB server, step by step. By following these instructions, you'll be able to identify and disconnect specific user connections, thus maintaining the overall stability of your database server.

1. Log in to the MySQL or MariaDB server.
2. Open a terminal or command prompt and connect to the database server using the following command.

   mysql -u root -p

   Replace “root” with your MySQL or MariaDB username if necessary. You will be prompted to enter your password.

3. Display a list of all currently active connections to the database server.

   SHOW FULL PROCESSLIST;

4. Review the output of the SHOW FULL PROCESSLIST command and locate the connection you want to terminate, by noting down the process ID, which can be found in the “Id” column.
5. Terminate the connection by executing the KILL command followed by the process ID.

   KILL process_id;

   Replace “process_id” with the actual process ID you identified in the previous step.

6. Verify that the connection has been successfully terminated by running the SHOW FULL PROCESSLIST command once again.
7. Exit the MySQL or MariaDB shell once you have successfully terminated the desired connection(s).

   EXIT;