Killing all running queries on MySQL

A tutorial on how to kill all of the currently running queries on a MySQL server.

Looking for a new simple way to plan your day? Get organized with Five.Today

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';
Looking for a modern PHP framework to build your new project? Please consider using the Alpha Framework

John Collins

I have been writing about web technology and software development since 2001. I am the developer of the Alpha Framework for PHP, and the Five.Today personal productivity app. I love open source, technology, and economics.