How to Find and Kill MySQL Process

How to Find and Kill MySQL Process?

Sometimes a user may face issues in processing the requests while working with MySQL. They experience a delay in processing the request and fetching the data from the server side. This happens due to the piling up of a large number of processes on the server side and eventually, this makes the server slower. The users find it difficult to fetch their information from the database stored on the server. They can't access the tables also they are unable to execute any request.

In this scenario, we need to find the MySQL processes and perform the MySQL kill process.

Prerequisites

Some prerequisites need to be available in your system to find MySQL processes and perform MySQL kill process which are as follows:

• Command line window- This is a necessary part as here we will write the SQL commands and run them to perform MySQL kill the process.
• MySQL- The user must have installed MySQL in their system.
• sudo or root access/privileges- The user should have permission to root.

How to Find MySQL Processes List

In this section, we will see the commands that we can use to find the MySQL process list and after that, we will kill the process. But before that let us create a table with some information stored in them and we will perform the killing process using this table.

We will create a table namely student_score and enter their roll, name, and score. This table will be used to Perform MySQL kill the process in the given section. To create table student_score with the following structure of the table, we use the command given below:

Output:

Then we will store the records of 4 students using the below command:

The final created table can be seen using the command given below:

Output:

Kill A Particular Thread

You can also kill a specific thread of a process. As we know, a thread is a basic unit of execution of any process. Each program may have several processes associated with it and each process may have several threads associated with the process. So, the thread can be referred to as the basic unit of a process or the basic unit of CPU utilization. To perform the MySQL kill process, the user must know the thread ID. The syntax to kill a particular thread is as follows:

For example, if the thread ID is 24, then the command to kill that thread will be like this:

How to Kill MySQL Process

For killing an active MySQL process, we need to know the process ID of every process that needs to be killed. For getting the process ID, we run the following command:

Output:

There are various details related to the processes are provided in the output such as ID, User, Host, name of the database, Time, Status, Info, etc. You can see the columns namely Id and Time. You can see the time column to determine the process which is taking the most time to get executed. Note down the process ID and use this with the kill command as given below:

Then you will receive an output like this:

You can see a message output as " “Query OK, 0 rows affected (0.06 sec)" on the shell. This means that the connection to the database and all the operations related to the query is ended. One thing to remember is that the user must have permission to MySQL kill the process.

You can again run the given query to check if the process you have just killed, do they still exists or not. To check run the below command:

Output: As you can see, there is not any process ID present namely '12'.

How to Kill All MySQL Processes for a Specific User

There is not any separate command using which the user can kill all the processes for a specific user. To kill all sql processes for a specific user, we will again use the CONCAT command. The syntax to kill all the processes for a specific user is as follows:

To specify the user, don't forget to replace the root parameter with the other username.

Using CONCAT To Kill Multiple MySQL Processes

There are various methods to use CONCAT for killing multiple MySQL processes from the process list.

Method 1 You can simply run the given command to kill multiple MySQL processes using CONCAT:

Method 2 First, you need to create a separate table for all the processes from the process list. To create a table of processes from the process list, run the below command:

After running the above command, run the command given below:

After running the above command, you will get an output like this:

Now, you have two to kill the process that you have organized in the table.

1. By editing the text manually:

You can copy the above output and save this data in a text file. Then remove all the special characters that are being used to make the table format such as plus, dash, minus, etc. Then use the "kill all commands". Doing this will kill all the processes mentioned in the table above.

2. Using the command to create a file and then kill: You can use a command to create the output file for the table in which the process IDs are stored. To create the text file, use the command given below:

Running this command will create t text file namely kill_list. But don't forget to remove the first line of the text file because this is not a correct MySQL command. It can not be executed in the MySQL command prompt. If you want to kill multiple MySQL processes, you can import the file by using the command given below:

Kill All Processes

Suppose you want to kill all the processes at once, without determining the run time, ID, or any other parameter, you can kill them all at once. To kill all the processes at once you need to execute the following command:

Using Stored Procedure To Exempt Current Processes.

The purpose of this stored procedure is to kill all other running processes in the MySQL server except for the one that is currently being executed. It does this by first declaring some variables and a cursor that selects the process ID of all processes in the information_schema.processlist of the table. It then opens the cursor and loops through each process ID.

There is the option to kill all the processes one by one without killing the processes that are under process or is in use at the current time. You can do this by running the command given below:

To call it, use the command given below:

CALL kill_other_processes();