Killing all running queries on MySQL

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';