MySQL: Killing connections massively
Every so often I run into situation when I need to kill a lot of connections on MySQL server - for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:
mysql> SELECT concat(\'KILL \',id,\';\') FROM information_schema.processlist WHERE user=\'root\';
+------------------------+
| concat(\'KILL \',id,\';\')
+------------------------+
| KILL 3101;
| KILL 2946;
+------------------------+
2 rows IN SET (0.00 sec)
mysql> SELECT concat(\'KILL \',id,\';\') FROM information_schema.processlist WHERE user=\'root\' INTO OUTFILE \'/tmp/a.txt\';
Query OK, 2 rows affected (0.00 sec)
mysql> source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)