How to Find and Terminate / Kill MySQL Process

Introduction

Unnecessary MySQL processes can cause performance issues on your system. Over time, active threads pile up and stall your server preventing users from accessing tables and executing requests.

When resource usage is extremely high, you may need to kill MySQL processes. This guide will show you how to identify MySQL processes and kill a process.

Prerequisites

• Access to a command line/terminal window
• MySQL or MariaDB installed
• User with sudo or root privileges

How to Find MySQL Processes List

Before you can locate a process and kill it, you must access either a local or remote MySQL server. To log into your MySQL local account as root, open the terminal and enter:

mysql -u root -p

Type in the password when prompted. When the MySQL shell loads, the prompt displays mysql>.

To locate a process to kill or terminate, load the list with all active sessions.
In MySQL shell, use this query:

SHOW PROCESSLIST;

The output displays the following information:

You may have more entries on your list. Note the Id of the process you want to kill. The Time column helps you determine the longest-running processes. Those are usually the ones you want to terminate first.

How to Kill MySQL Process

To kill a MySQL session from the list, use the KILL query followed by the thread Id you noted earlier.

KILL 14;

The shell displays the query status and the number of affected rows: “Query OK, 0 rows affected (0.06 sec).” This query ends the connection to the database, including all operations associated with the connection.

Remember that a user must have proper privileges to be able to kill a process.

How to Kill All MySQL Processes for a Specific User

MySQL does not have a unique command for killing all processes.

To kill all processes for a specific user, use CONCAT to create a file with the list of threads and statements. In our case, we entered root as the user. To specify another user, replace root with the desired username.

SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/process_list.txt';

This query created a file called process_list.txt. You can edit the name to your liking. Open the file and review if those are the processes you want to kill. When you are ready, enter:

SOURCE /tmp/process_list.txt;

You can add conditions to the query to narrow down the list of processes in the output file. For example, add time > 1000 to the command to include only the processes with the time value higher than 1000.

SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' and time>1000 INTO OUTFILE '/tmp/process_list.txt';

Conclusion

This guide has outlined the easiest way to find and kill MySQL processes. Make sure you always double-check which processes you are terminating before running a MySQL query.

For options to further optimize a MySQL Database, read our article on MySQL Performance Tuning.