How do I kill all the processes in Mysql "show processlist"?

Question

Because I see a lot of processes there, and the "time" column shows big values for all of them.

Peking

You can also restart mysqld, e.g., sudo service mysqld restart – Bresee

Answer 1

You need to kill them one by one, MySQL does not have any massive kill command. You can script it in any language, for example in PHP you can use something like:

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

Gradey

that is a good idea. Lemme try converting that to a shell script on a cron...! – Gothic

Shell for i in {994..1145}; do mysql -uroot -p123456 -e "kill $i" ; done – Demo

mysql -u -p -e "show processlist;" | grep Sleep | awk '{print $1}' | while read LINE; do mysql -u -p -e "kill $LINE"; done – Immersionism

Answer 2

Mass killing operation saves time. Do it in MySql itself:

Run these commands

mysql> select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time > 200 into outfile '/tmp/a.txt';

mysql> source /tmp/a.txt;

Reference

---------edit------------

if you do not want to store in file, store in a variable

Just run in your command prompt

> out1=$(mysql -B test -uroot -proot --disable-column-names  -e "select concat('KILL ',id,';') from information_schema.processlist where user='root' and time > 200;")

> out2= $(mysql -B test -uroot -proot --disable-column-names  -e "$out1")

Detrain

Please remember to refer to the source: mysqlperformanceblog.com/2009/05/21/… – Crowberry

@ArtemGoutsoul but I dont remember I refered to this site. Its my own trend of doing this. – Detrain

@JanusTroelsen but my answer is after 3 yrs '12 – Detrain

Is it possible to do this using linux pipes, without a file? – Matejka

@ShankarDamodaran SURE – Detrain

select concat('KILL ',id,';') from information_schema.processlist where Command='Query'; Is this return ID only of localhost? – Jamisonjammal

its the process id. It will return processes running on whichever server you are executing this command – Detrain

Answer 3

Only for mariaDB

It doesn't get simpler then this, Just execute this in mysql prompt.

kill USER username;

It will kill all process under provided username. because most of the people use same user for all purpose, it works!

I have tested this on MariaDB not sure about mysql.

Megathere

In which version of MySQL does this exist? I don't see it documented in the MySQL 5.7 reference; there's no evidence that it's possible to kill by user: KILL [CONNECTION | QUERY] processlist_id. I tried your query in MySQL 5.6.34 and it's considered a syntax error. – Mcmillian

I have tried this in the MySQL 5.6.34 prompt: mysql> kill user root; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'root' at line 1 Probably your answer is a feature particular to MariaDB only. – Mcmillian

This doesn't work for Mysql. Please read before posting advice related to other DB systems... – Tails

Answer 4

I have also searched how to parse through MySQL the command SHOW PROCESSLIST and ended with a one-liner in a Shell:

mysqladmin processlist -u <USERNAME> -p<PASSWORD> | \
awk '$2 ~ /^[0-9]/ {print "KILL "$2";"}' | \
mysql -u <USERNAME> -p<PASSWORD>

• mysqladmin processlist will print a table with the thread ids;
• awk will parse from the second column only the numbers (thread ids) and generate MySQL KILL commands;
• and finally the last call to mysql will execute the passed commands.

You can run grep before the awk command to filter a particular database name.

Toothbrush

Answer 5

The following will create a simple stored procedure that uses a cursor to kill all processes one by one except for the process 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 ;

It can be run with SELECTs to show the processes before and after as follows:

SELECT * FROM information_schema.processlist;
CALL kill_other_processes();
SELECT * FROM information_schema.processlist;

Geopolitics

Answer 6

Or... in shell...

service mysql restart

Yeah, I know, I'm lazy, but it can be handy too.

Pirate

this is a very bad idea... in special if you have the table engine set on memory, cause all data will be lost... or if you use innodb engine type, cause it will redo all the indexes on restart – Georgia

Also it kills all process on all databases – Ptyalin

Answer 7

I recently needed to do this and I came up with this

-- GROUP_CONCAT turns all the rows into 1
-- @q:= stores all the kill commands to a variable
select @q:=GROUP_CONCAT(CONCAT('KILL ',ID) SEPARATOR ';')  
FROM information_schema.processlist 
-- If you don't need it, you can remove the WHERE command altogether
WHERE user = 'user';
-- Creates statement and execute it
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

That way, you don't need to store to file and run all queries with a single command.

Harlot

This looks promising but isn't supported by MySQL: dev.mysql.com/doc/refman/8.0/en/prepare.html – Leary

Answer 8

If you don't have information_schema:

mysql -e "show full processlist" | cut -f1 | sed -e 's/^/kill /' | sed -e 's/$/;/' ;  > /tmp/kill.txt
mysql> . /tmp/kill.txt

Disseise

i believe the second line should be mysql < /tmp/kill.txt. And i think in most case you'll find that you need to specify the user and the password for running the mysql command, so it will be mysql -u root -p < /tmp/kill.txt – Stetson

Answer 9

KILL ALL SELECT QUERIES

select concat('KILL ',id,';') 
from information_schema.processlist 
where user='root' 
  and INFO like 'SELECT%' into outfile '/tmp/a.txt'; 
source /tmp/a.txt;

Done

Answer 10

We can do it by MySQL Workbench. Just execute this:

kill id;

Example:

kill 13412

That will remove it.

Croft

This sounds better answer as there was not language mentioned above. – Cyano

Answer 11

This snipped worked for me (MySQL server 5.5) to kill all MySQL processes :

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

Vara

Answer 12

I'd combine bash and mysql:

for i in $(mysql -Ne "select id from information_schema.processlist where user like 'foo%user' and time > 300;"); do
  mysql -e "kill ${i}"
done

Superfetation

Answer 13

mysqladmin pr -u 'USERNAME' -p'PASSWORD' | awk '$2~/^[0-9]+/{print $2}' | xargs -i mysqladmin -u 'USERNAME' -p'PASSWORD' kill {}

Paralytic

Hey there. Not sure if you're a bot, Community or a human but anyway, it would be great if you added some words explaining what's going on with this line of code and how it does solve the issue at hand? – Undetermined

Added the -u and -p to give access to the user... work's fine ! – Ternate

Answer 14

Here is a solution that you can execute without relying on the operating system:

STEP 1: Create a stored procedure.

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 ;

STEP 2: Call the stored procedure giving it the name of a database user whose processes you want to kill. You could rewrite the stored procedure to filter on some other criteria if you like.

CALL kill_user_processes('devdba');

Denote

Answer 15

login to Mysql as admin:

 mysql -uroot -ppassword;

And than run command:

mysql> show processlist;

You will get something like below :

+----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id | User        | Host               | db       | Command | Time | State | Info             |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| 49 | application | 192.168.44.1:51718 | XXXXXXXX | Sleep   |  183 |       | NULL             ||
| 55 | application | 192.168.44.1:51769 | XXXXXXXX | Sleep   |  148 |       | NULL             |
| 56 | application | 192.168.44.1:51770 | XXXXXXXX | Sleep   |  148 |       | NULL             |
| 57 | application | 192.168.44.1:51771 | XXXXXXXX | Sleep   |  148 |       | NULL             |
| 58 | application | 192.168.44.1:51968 | XXXXXXXX | Sleep   |   11 |       | NULL             |
| 59 | root        | localhost          | NULL     | Query   |    0 | NULL  | show processlist |
+----+-------------+--------------------+----------+---------+------+-------+------------------+

You will see complete details of different connections. Now you can kill the sleeping connection as below:

mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)

Maihem

This is not the answer to the question. Here you are killing a single process while the question is how can you kill process at once. – Nassi

@badbod99 I've ended up here looking for how to kill a mysql process and this answer helped me so I upvoted it. – Burgonet

Answer 16

The following worked great for me:

echo "show processlist" | mysql | grep -v ^Id | awk '{print $1}' | xargs -i echo "KILL {}; | mysql"

Patentee

Answer 17

for python language, you can do like this

import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             db='mysql',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    cursor.execute('SHOW PROCESSLIST')
    for item in cursor.fetchall():
        if item.get('Time') > 200:
            _id = item.get('Id')
            print('kill %s' % item)
            cursor.execute('kill %s', _id)
    connection.close()

Thibeault

Answer 18

An easy way would be to restart the mysql server.. Open "services.msc" in windows Run, select Mysql from the list. Right click and stop the service. Then Start again and all the processes would have been killed except the one (the default reserved connection)

Anaerobe

Answer 19

#! /bin/bash
if [ $# != "1" ];then
    echo "Not enough arguments.";
    echo "Usage: killQueryByDB.sh <db_name>";
    exit;
fi;

DB=${1};
for i in `mysql -u <user> -h localhost ${DB} -p<password> -e "show processlist" | sed 's/\(^[0-9]*\).*/\1/'`; do
    echo "Killing query ${i}";
    mysql -u <user> -h localhost ${DB} -p<password> -e "kill query ${i}";
done;

Tevere

Answer 20

Sometimes I have some zombies mysql processes that can't be killed (using MAMP Pro).

First get a list of all mysql processes:

ps -ax | grep mysql

Next kill every one of them with (replace processId with the first column in previous command result):

kill -9 processId

Bellybutton

Answer 21

I used the command flush tables to kill all inactive connections which where actually the mass problem.

Bilious

doesnt work. Flush the tables but maintain the sleep coonections active – Acrophobia

Answer 22

If you are using laravel then this is for you:

$query = "SHOW FULL PROCESSLIST";
    $results = DB::select(DB::raw($query));

    foreach($results as $result){
        if($result->Command == "Sleep"){
            $sql="KILL ". $result->Id;
            DB::select(DB::raw($sql));
        }
    }

Of-course, you should use this use Illuminate\Support\Facades\DB; after your namespace.

Triglyceride

This worked for me in laravel 8+ – Rosellaroselle

Answer 23

Kill does not work if the running process is not yours. I merged some of these solutions and extended them to create a more simple one (at least to me).

m=mysql -p $password -h $host -u$user #you can also inline it of course 
for i in `$m -e "show processlist" | awk '/$anySearchString/ {print $1}'`; do $m -e "call mysql.rds_kill($i);"; done

Hairstreak

Answer 24

Query 1 select concat('KILL ',id,';') from information_schema.processlist where user='username' into outfile '/tmp/a.txt';

Query 2 source a.txt

This will enable you to kill all the queries in show processlist by specific user.

Rae

I upvoted then realised you just copied the answer below rofl; https://mcmap.net/q/159437/-how-do-i-kill-all-the-processes-in-mysql-quot-show-processlist-quot – Brokenhearted

FYI it was of my own, but whatever. – Rae