Mass killing of MySQL processes

Sometimes you need to kill multiple SQL queries at once. This may be time consuming, since the MYSQL kill command can have only one argument.

A simple hack is to create a script that will do it for us. First we start by listing the processes we wish to kill.

This is already outlined here https://support.qualityunit.com/245419-Show-processlist-without-sleep-command

You may want to fine-tune this script, if you want to kill processes that run longer than x seconds. 

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' and time > 10 and user <> 'system user' and user <> 'replicator' order by time\G

Now when you are certain to kill those processes, issue the same command, but replace * with concat('KILL ',id,';') and appned into outfile '/tmp/mysql_kill_queries'

SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE command != 'Sleep' and time > 10 and user <> 'system user' and user <> 'replicator' ORDER BY time INTO OUTFILE '/tmp/mysql_kill_queries'

This will create a file /tmp/mysql_kill_queries with content similar to this:

kill 1111;

kill 2222;

kill 3333;

...

Now we execute the script.

Be CAREFUL killing MYSQL processes! Only kill the ones you know for certain can be killed and wont couse trouble. In general killing SELECTs is ok, but watch out when killing INSERTs, ALERT TABLE and so on.
mysql> source /tmp/mysql_kill_queries;

That's it. the processes are now killed.


If you see this error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Thant means your MYSQL server can only write to specific path, which is set in the global variable secure_file_priv. You can see the location like this:

mysql> show global variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0,01 sec)

Change the INTO OUTFILE paramener to whatever is secure_file_priv set to . In this example I would change it to 

... INTO OUTFILE '/var/lib/mysql-files/mysql_kill_queries'

And execute the generated script:

mysql> source /var/lib/mysql-files/mysql_kill_queries;