How To Kill MYSQL Queries

When it comes to killing MySQL processes and queries, there are several things you can try. Here are some of the best ways to kill MySQL queries and processes.

  1. Know the List of Processes
  2. Kill A Particular Thread
  3. Using CONCAT To Kill Multiple MySQL Processes
  4. Kill All Processes
  5. Using a PHP Script
  6. Using Stored Procedure To Exempt Current Processes.
  7. Stored Procedure to Kill Processes of A Particular User

1. Know the list of Processes

Run the following command:

This will show all the open connections to the server. It includes thread IDs, User, Host, DB, TIme, Status, Info, etc.

Before executing this command, make sure you have admin rights. The command outputs currently running queries.

2. Kill a Particular Thread

If you know the “Thread ID” then you can kill that particular thread using the kill command.

Just execute the following command:

For example, if the thread ID is 33, you can execute the following command to kill this thread:

3. Using CONCAT to kill multiple MySQL Processes

Since MySQL does not have a massive kill command, you can use “concat” to produce kill commands from the Process list table.

There are multiple ways you can do this:

Method 1:

Run the following command:

This will kill all your MySQL queries.

Method 2:

You can create a table from processlist.

Run:

Now, use the following:

This will give an output that will look similar to this:

Now you can use either of the following ways to kill all the threads:

1. By manually editing the text:

Copy this output in a txt file and remove pipes, plus, and dashes. Then run all kill commands in MySQL. This will kill all the processes.

2. By using a command to create an output file

Run the following command:

This will create a txt file with the name “kill_list”. You just need to remove the first line from the txt file (kill_list) as it is not a valid MySQL command.

Now you can run this in MySQL. It will execute all the statements in the file which will kill all the threads.

You can also import this file to kill multiple MySQL queries at once:

4. Kill All Processes

You can try using the following query to kill all processes.

5. Using a PHP script

You can use a PHP script like the following:

6. Using Stored Procedure To Exempt Current Processes

You can create a stored procedure to kill all processes one by one without killing the processes currently being used.

You can call it using:

7. Stored Procedure To Kill Processes of A Particular User

You can create a stored procedure that can kill all the processes of a particular user.

You can call the procedure by mentioning the username of database user whose processes you want to kill. Suppose the user is ‘bob’ the you can use:

So you can kill MySQL queries, processes or threads by running these commands. You may have noticed how the “show processlist” command is useful in identifying the processes that should be killed. As there is no single command in MySQL to kill all the processes we have to settle with a workaround. We hope this article helped you. If you have a new approach, please join the discussion in the comments.

Originally posted on May 24, 2019 @ 12:03 pm

Related Post

Leave a Reply