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.
mysql> source /tmp/mysql_kill_queries;
That's it. the processes are now killed.
If you see this error:
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;