how to killall mysql processlist

Question

I want to kill all mysql process in 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 | +------+----------------------+---------------------+----+---------+------+-------+------------------+

Answer 1

Looks like this might be covered at http://serverfault.com/questions/11357/bulk-or-mass-killing-misbehaving-mysql-queries

Answer 2

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

Answer 3

Any reason you can't just stop and restart mysqld?

Answer 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?