How to Kill a Process in MySQL
In this tutorial, we will learn how to kill a process in MySQL.
Many times, specific MySQL processes may take too long to execute.
It can cause performance issues on the system. As time passes, different threads and statements are cluttered in a system that hampers the overall functioning of the server.
To resolve this issue and avoid high resource usage, we can kill specific processes that are no longer required to be run.
To kill a process in MySQL, we have to follow two simple steps after logging in to your MySQL account:
- List all the processes running in MySQL.
- Kill the process using the
KILLcommand.
List All the Processes Running in MySQL
Before we aim to delete or terminate any ongoing processes, we first need to identify the process id. Once we have the process id, we can kill or terminate a process.
We can get the list of all the existing processes in MySQL using the show processlist command. As soon as we execute this command, we will get the ongoing processes for the logged-in user.
A snapshot of the ongoing processes is as follows.
As we can see, each process has a process id, User that indicates the user name, Host that indicates the host server, db that indicates the database.
Command indicates the type of command under consideration, Time indicates the time for which the query is running, State indicates the state of the process, and Info indicates any relevant information related to the process.
Kill the Process Using the KILL Command
Once we get the id of the process to be killed, we can use the kill <id> command to terminate the process.
Let us aim at killing the process with the id number 14. This task can be achieved with the following query.
KILL 14;
This will have the id process as 14, killed.
Moreover, if we wish to terminate all the processes associated with a particular user, we can perform this operation using the following query.
SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='preet' INTO OUTFILE '/tmp/processes_text.txt';
In the query above, a new file with the threads and statements has the filename as processes_text. We can review this file to check if we want to terminate all the mentioned processes.
Once we are sure, we can make use of the following query.
source /tmp/processes_text.txt
Therefore, with the help of the KILL command, one or multiple processes in a MySQL database can be terminated.
