How to Terminate Multiple MySQL Queries at Once

Sometimes, you may need to terminate multiple SQL queries simultaneously. This can be time-consuming, as the MySQL KILL command accepts only one argument at a time.

A straightforward solution is to create a script to automate this process. First, begin by listing the processes you wish to terminate.

For more information on listing processes, refer to: https://support.qualityunit.com/245419-Identifying-and-Filtering-Problematic-SQL-Queries-in-MySQL

You may wish to further refine this script to target only processes that have been running longer than a specified number of seconds.

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' AND time > 10 AND user <> 'system user' AND user <> 'replicator' ORDER BY time\G 

Once you are certain you want to terminate these processes, modify the command by replacing * with CONCAT('KILL ',id,';') and append INTO OUTFILE '/tmp/mysql_kill_queries' as shown below:

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 the following:

KILL 1111; KILL 2222; KILL 3333; ... 

Executing the Script

Now, you can execute the generated script:

Be CAREFUL when terminating MySQL processes! Only terminate processes you are certain can be safely killed and will not cause issues. In general, terminating SELECT statements is acceptable, but exercise caution when terminating INSERTs, ALTER TABLE, and similar operations.

 mysql> source /tmp/mysql_kill_queries; 

That's it—the selected processes are now terminated.

Troubleshooting: Secure File Privileges

If you encounter the following error:

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

This means your MySQL server can only write to a specific directory, as defined by the global variable secure_file_priv. You can determine the permitted output location with the following command:

 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) 

Adjust the INTO OUTFILE parameter to the path specified by secure_file_priv. In this example, update the command as follows:

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

Then execute the generated script:

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

For additional security and best practices, always verify the list of processes before executing any automated kill scripts.