Kill All MySQL Queries With State

April 25, 2016 1 min 17 s

If you’ve ever been the victim of a DDoS attack, you know that one of the first things in your stack to buckle is almost always MySQL. Here’s a quick and dirty way to clear up a congested MySQL server without fully restarting the server.

for PROC_TO_KILL in `mysql -h hostname -u user -p'password' -A --skip-column-names -e"SELECT ID FROM information_schema.processlist WHERE STATE = 'StateYouWantToKill' AND USER = 'QueryUser'" | grep -v "statistics" | awk '{print $1}'` ; do mysql -h hostname -u user -p'password' -A --skip-column-names -e"KILL QUERY ${PROC_TO_KILL}" ; done

This line of code should be run from the command line, replacing the following values:

hostname - the hostname of your server, EX: db.example.com
user - Your MySQL username
password - Your MySQL password
StateYouWantToKill - When you run SHOW PROCESSLIST in your MySQL query browser application, you’ll see a set of results with a column named State which represents the current state that a request is in. This column can have any number of values from blank to init or Creating sort index and so on. Typically you can identify a bottleneck in here by some query running for an extended period of time - I usually kill all of those to give MySQL a break from an attack.
QueryUser - This would be the user that your application uses to execute queries.

Obviously this won’t fix a DDOS attack, they cannot be truly fixed without serious investment in services designed to protect against them and your best bet is to ride it out. At best, this will give your application a little breathing room so it doesn’t flood your MySQL server so much that it locks up and you potentially lose data.