How to kill all MySQL processes in processlist?

Question

I want to kill all the MySQL processes in the process list. Is there any way to do that?

# mysqladmin -u root -p processlist
Enter password: 
+------+----------------------+---------------------+----+---------+------+-------+------------------+
| Id   | User                 | Host                | db | Command | Time | State | Info             |
+------+----------------------+---------------------+----+---------+------+-------+------------------+
| 1908 | unauthenticated user | 192.168.1.103:46046 |    | Connect |      | login |                  |
| 1909 | unauthenticated user | 192.168.1.103:46047 |    | Connect |      | login |                  |
| 1910 | unauthenticated user | 192.168.1.103:46048 |    | Connect |      | login |                  |
| 1914 | unauthenticated user | 192.168.1.103:46049 |    | Connect |      | login |                  |
| 1946 | unauthenticated user | 192.168.1.103:46076 |    | Connect |      | login |                  |
| 1954 | unauthenticated user | 192.168.1.103:46078 |    | Connect |      | login |                  |
| 2015 | root                 | localhost           |    | Query   | 0    |       | show processlist |
+------+----------------------+---------------------+----+---------+------+-------+------------------+

Possible duplicate of [Bulk or mass killing misbehaving MySQL queries](http://serverfault.com/questions/11357/bulk-or-mass-killing-misbehaving-mysql-queries) — kenorb, Aug 02 '16 at 10:28

score 4 · Answer 1 · edited Apr 13 '17 at 12:14

4

Looks like this might be covered at Bulk or mass killing misbehaving MySQL queries

edited Apr 13 '17 at 12:14

Community

1

answered Aug 17 '09 at 03:39

Yancy

89
1
6

then shouldn't you mark this as a dupe? – chicks Aug 02 '16 at 10:40
@chicks: This answer is seven years old ... – Sven Aug 02 '16 at 10:58
I should have noticed that the user was dormant. Sorry Sven. Since kenorb linked to the dupe in the comments it seems safe to delete this answer -- unless there is some guideline about preserving things over a certain age. – chicks Aug 02 '16 at 11:09

score 2 · Answer 2 · answered May 16 '12 at 10:29

Stop/start of MySQL instance is not an answer. In most cases it can take quite a lot of time and impact your production. You should just KILL such queries.

Two interesting articles:

How to selectively kill queries in MySQL?

and:

Why do threads sometimes stay in ‘killed’ state in MySQL?

score 0 · Answer 3 · edited Jan 05 '13 at 11:17

0

You can achieve this with a shell script:

#!/bin/bash

# Get the processlist and save it in a file
mysqladmin -u root -p"your password" processlist > fullproce

# Get the process ids of the processes in the "Sleep" state and save them in id.
# Adjust the grep to match the processes you want to kill.
cat fullproce |grep Sleep |awk -F " " '{print $2}' > id

for todos_id in `cat ./id`
do
  # Kill each id identified earlier.
  mysqladmin -u root -p"your password" KILL $todos_id ;
done

# Remove files
rm  fullproce
rm id

edited Jan 05 '13 at 11:17

Ladadadada

26,337
7
59
90

answered Jan 05 '13 at 01:09

Rafael Fonseca

1

1

ahhh....english please – mdpc Jan 05 '13 at 03:10

score 0 · Answer 4 · answered Aug 17 '09 at 03:33

0

Quickest way is just to restart MySQL. Otherwise you'll need to kill off each running process one by one (there's no killall in mysqladmin).

answered Aug 17 '09 at 03:33

womble

96,255
29
175
230

score -1 · Answer 5 · answered Aug 02 '16 at 11:24

-1

This shell command may help to kill all sleep processes:

mysqladmin proc | grep Sleep | awk '{print $2}' | xargs -L1 mysqladmin kill

answered Aug 02 '16 at 11:24

kenorb

6,499
2
46
54

How to kill all MySQL processes in processlist?

5 Answers5