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. Quick side note, if you use a good WooCommerce hosting provider, you won’t have to manually adjust MySQL processes to keep your website online.

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:

show processlist; 

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:

 kill thread_id;

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

 kill 33;

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:

 mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 
FROM information_schema.processlist WHERE user <> 'system user'; 

This will kill all your MySQL queries.

Method 2:

You can create a table from process list.

Run:

 SELECT * FROM processlist\G; 

Now, use the following:

 SELECT CONCAT('KILL ',id,';') AS kill_list FROM information_schema.processlist WHERE user='root' AND info = 'SELECT * FROM processlist';

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

+------------------------+
| kill_list              |
+------------------------+
| KILL 1;                |
| KILL 2;                |
| KILL 3;                |
| KILL 4;                |
+------------------------+

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 text 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:

 SELECT CONCAT('KILL ',id,';') AS run_this FROM information_schema.processlist WHERE user='root' 
AND info = 'SELECT * FROM processlist' INTO OUTFILE '/tmp/kill_list.txt' 

This will create a text file with the name “kill_list”. You just need to remove the first line from the text 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:

 mysql -uroot -p{yourpassword} < /tmp/kill_list.txt

4. Kill All Processes

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

 mysql -e "show full processlist;" -ss | awk '{print "KILL "$1";"}'| mysql 

5. Using a PHP script

You can use a PHP script like the following:

 $result = mysql_query("SHOW FULL PROCESSLIST");
  while ($row=mysql_fetch_array($result)){
  $process_id=$row["Id"];
 if ($row["Time"] > 10 ) {
  $sql="KILL $process_id";
  mysql_query($sql);
  }
 }

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.

 DROP PROCEDURE IF EXISTS kill_other_processes;
DELIMITER $$ 
CREATE PROCEDURE kill_other_processes() 
BEGIN   
  DECLARE finished INT DEFAULT 0; 
  DECLARE proc_id INT; 
  DECLARE proc_id_cursor CURSOR FOR SELECT id
  FROM information_schema.processlist; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;   
  OPEN proc_id_cursor;
  proc_id_cursor_loop: LOOP
     FETCH proc_id_cursor INTO proc_id; 

    IF finished = 1 THEN 
       LEAVE proc_id_cursor_loop; 
    END IF; 

    IF proc_id <> CONNECTION_ID() THEN  
     KILL proc_id; 
    END IF; 
  END LOOP proc_id_cursor_loop; 
  CLOSE proc_id_cursor;
 END$$ 
DELIMITER ; 

You can call it using:

CALL kill_other_processes();

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.

 DROP PROCEDURE IF EXISTS  kill_user_processes$$  
CREATE PROCEDURE `kill_user_processes`(   
  IN user_to_kill VARCHAR(255)
 ) READS SQL DATA 
BEGIN
   DECLARE name_val VARCHAR(255);
   DECLARE no_more_rows BOOLEAN;
   DECLARE loop_cntr INT DEFAULT 0;
   DECLARE num_rows INT DEFAULT 0;

   DECLARE friends_cur CURSOR FOR
     SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE USER=user_to_kill;

   DECLARE CONTINUE HANDLER FOR NOT FOUND 
    SET no_more_rows = TRUE; 

  OPEN friends_cur; 
  SELECT FOUND_ROWS() INTO num_rows;

   the_loop: LOOP 

    FETCH  friends_cur
    INTO   name_val;

     IF no_more_rows THEN 
        CLOSE friends_cur;
         LEAVE the_loop;
     END IF;
  SET @s = name_val;
     PREPARE stmt FROM @s;
     EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;
     
SELECT name_val;
     
SET loop_cntr = loop_cntr + 1;   

END LOOP the_loop;   

SELECT num_rows, loop_cntr; 

END $$ 
DELIMITER ; 

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

CALL kill_user_processes('bob')

 

 

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 methods that got to 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.