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

Question

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

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

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.

Or... in shell...

service mysql restart

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

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.