Published on 2014-06-30 by John Collins. Please follow me on Twitter for more:
On occasion you might need to kill all currently running queries against your MySQL server, without having to restart the service.
I do a lot of work on ETL (Extract Transform Load) applications, where you may have very long-running queries hanging around locking resources required by your data loader, that you will want to kill before starting a new data load.
Another scenario is where you have an application that is misbehaving by issuing many queries that are impacting other users of the MySQL server: so long as each application is connecting using a different MySQL user account (this should be a given), you can kill the queries for just that user.
MySQL greater than version 5.1, it will store the process ID of each client connection running a query in the information_schema database. We can query this database, then build up a series of KILL statements dynamically to kill each running query. Here is the main statement:
mysql> SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') FROM information_schema.processlist WHERE USER <> 'system user' INTO OUTFILE '/tmp/killqueries.sql';
One you run that, check the contents of the /tmp/killqueries.sql in another terminal you should see something like this:
-bash-4.1$ cat /tmp/killqueries.sql KILL QUERY 7; KILL QUERY 6;
Back at your MySQL prompt, you can now run that script directly:
mysql> SOURCE /tmp/killqueries.sql
Finally, if you only want to kill the queries belonging to a specific MySQL user account, you can modify the original query like so:
mysql> SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') FROM information_schema.processlist WHERE USER = 'baduser' INTO OUTFILE '/tmp/killqueries.sql';