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

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

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;

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

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)

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

The following worked great for me:

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

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

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.