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.

You can also restart mysqld, e.g., `sudo service mysqld restart` — philfreo, Jun 04 '11 at 15:41

Angelin Nadar · Answer 1 · 2013-05-27T13:37:31.007

236

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")

edited May 27 '13 at 13:37

answered Mar 30 '12 at 06:55

Angelin Nadar

8,944
10
43
53

13

Please remember to refer to the source: http://www.mysqlperformanceblog.com/2009/05/21/mass-killing-of-mysql-connections/ – Artem Goutsoul Dec 26 '12 at 10:16
2

@ArtemGoutsoul but I dont remember I refered to this site. Its my own trend of doing this. – Angelin Nadar Dec 28 '12 at 18:49
@JanusTroelsen but my answer is after 3 yrs '12 – Angelin Nadar Jan 31 '13 at 13:02
Is it possible to do this using linux pipes, without a file? – Silver Light May 22 '13 at 13:24
1

@ShankarDamodaran SURE – Angelin Nadar Oct 27 '15 at 06:58
`select concat('KILL ',id,';') from information_schema.processlist where Command='Query';` Is this return ID only of localhost? – Lnux Oct 17 '16 at 11:36
its the process id. It will return processes running on whichever server you are executing this command – Angelin Nadar Oct 20 '16 at 06:28

score 113 · Accepted Answer · answered Dec 14 '09 at 22:15

113

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);
  }
}

answered Dec 14 '09 at 22:15

Michal Čihař

9,799
6
49
87

that is a good idea. Lemme try converting that to a shell script on a cron...! – M. Faraz Sep 13 '13 at 07:07
5

Shell `for i in {994..1145}; do mysql -uroot -p123456 -e "kill $i" ; done` – zhuguowei Feb 16 '16 at 14:39
mysql -u -p -e "show processlist;" | grep Sleep | awk '{print $1}' | while read LINE; do mysql -u -p -e "kill $LINE"; done – user3770797 Nov 04 '17 at 20:55

Maulik · Answer 3 · 2020-08-11T12:50:09.417

17

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.

edited Aug 11 '20 at 12:50

answered Mar 20 '17 at 04:28

Maulik

2,881
1
22
27

4

In which version of MySQL does this exist? I don't see it documented in the [MySQL 5.7 reference](https://dev.mysql.com/doc/refman/5.7/en/kill.html); 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. – Birchlabs Jul 24 '17 at 14:24
4

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. – Birchlabs Jul 25 '17 at 09:25
3

This doesn't work for Mysql. Please read before posting advice related to other DB systems... – RyanH Aug 13 '19 at 02:37

score 16 · Answer 4 · answered May 15 '12 at 21:02

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.

Steve Chambers · Answer 5 · 2019-01-03T15:22:56.713

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;

score 9 · Answer 6 · answered Sep 10 '14 at 22:20

9

Or... in shell...

service mysql restart

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

answered Sep 10 '14 at 22:20

EcchiOli

802
9
11

12

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 – HellBaby Dec 07 '15 at 08:59
8

Also it kills all process on all databases – Diego Andrés Díaz Espinoza Mar 10 '16 at 15:28

score 9 · Answer 7 · answered Sep 08 '15 at 19:26

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.

This looks promising but isn't supported by MySQL: https://dev.mysql.com/doc/refman/8.0/en/prepare.html — Chris Adams, Jul 16 '21 at 20:55

score 6 · Answer 8 · edited Nov 04 '19 at 11:39

6

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

edited Nov 04 '19 at 11:39

Rahul

18,271
7
41
60

answered Oct 31 '13 at 15:59

mikesl

2,133
20
25

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` – David Sep 24 '20 at 03:19

score 6 · Answer 9 · edited Feb 04 '14 at 11:48

6

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;

edited Feb 04 '14 at 11:48

Taryn

242,637
56
362
405

answered Feb 04 '14 at 08:28

user3269995

69
1
1

score 4 · Answer 10 · answered Jun 01 '16 at 17:21

4

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

answered Jun 01 '16 at 17:21

Fedir RYKHTIK

9,844
6
58
68

score 4 · Answer 11 · edited Feb 19 '18 at 14:21

4

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

kill id;

Example:

kill 13412

That will remove it.

edited Feb 19 '18 at 14:21

Sam

7,252
16
46
65

answered Feb 19 '18 at 12:47

Aroon

991
2
15
34

This sounds better answer as there was not language mentioned above. – DeshDeep Singh Jul 20 '18 at 08:20

wiebel · Answer 12 · 2019-02-21T14:59:52.723

4

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

edited Feb 21 '19 at 14:59

answered Oct 26 '18 at 00:03

wiebel

51
4

score 2 · Answer 13 · edited Aug 03 '15 at 10:29

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');

user3721740 · Answer 14 · 2017-02-21T11:17:10.167

2

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

edited Feb 21 '17 at 11:17

answered Mar 05 '15 at 13:49

user3721740

99
1
3

1

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? – Félix Adriyel Gagnon-Grenier Mar 05 '15 at 16:15
1

Added the -u and -p to give access to the user... work's fine ! – Lord St John May 26 '15 at 08:36

score 1 · Answer 15 · answered Nov 08 '13 at 07:44

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)

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. — Hristo Petev, Jul 29 '14 at 08:18
@badbod99 I've ended up here looking for how to kill a mysql process and this answer helped me so I upvoted it. — Bogdan, Nov 21 '18 at 07:16

score 1 · Answer 16 · answered Nov 03 '17 at 08:12

1

The following worked great for me:

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

answered Nov 03 '17 at 08:12

Timon de Groot

7,255
4
23
38

score 1 · Answer 17 · answered Mar 27 '18 at 02:54

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()

score 0 · Answer 18 · answered Aug 13 '14 at 10:56

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)

score 0 · Answer 19 · answered Mar 10 '16 at 15:29

#! /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;

score 0 · Answer 20 · answered Oct 19 '17 at 10:01

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

score 0 · Answer 21 · answered Dec 05 '17 at 13:48

0

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

answered Dec 05 '17 at 13:48

Meltzer

127
2
10

1

doesnt work. Flush the tables but maintain the sleep coonections active – gtryonp Mar 19 '18 at 16:58

score 0 · Answer 22 · answered Jan 06 '19 at 10:38

0

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.

answered Jan 06 '19 at 10:38

Ziaur Rahman

1,148
11
24

This worked for me in laravel 8+ – Theodory Nov 17 '22 at 14:12

score 0 · Answer 23 · answered Jun 04 '21 at 20:53

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

score -5 · Answer 24 · answered Dec 01 '15 at 12:50

-5

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.

answered Dec 01 '15 at 12:50

Saurav Sood

101
2
12

I upvoted then realised you just copied the answer below rofl; https://stackoverflow.com/a/21547386/3652863 – Robert Pounder Jun 09 '17 at 15:32
FYI it was of my own, but whatever. – Saurav Sood Jun 16 '17 at 14:09

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

24 Answers24

Linked

Related