Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 |
+------+----------------------+---------------------+----+---------+------+-------+------------------+
share|improve this question
    
Any reason you can't just stop and restart mysqld? –  John Gardeniers Aug 17 '09 at 3:35

4 Answers 4

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?

share|improve this answer

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

share|improve this answer

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
share|improve this answer
1  
ahhh....english please –  mdpc Jan 5 '13 at 3:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.