Kill All MySQL Queries With State

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:

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.

© 2014 - 2023 · Home ·