MySQL has a kill command which lets you kill a specific query or connection. This is useful it situations where you’ve accidentally issued a really complex query, or an application created a persistent connection and forgot to close it.
Killing lots of connections can be tedious though. Say an application has created lots of persistent connections and not closed any of them, you’d have to go through the following steps:
mysql> SHOW FULL PROCESSLIST; /* shows all connections */
mysql> KILL <process_id>; /* for EVERY process you want to kill */
To make this process much simpler I wrote a little bash script that will kill all connections for a given MySQL user.
#!/bin/bash
# Kill all MySQL connections for a given user
# Ben Dowling - Apr 2009
# www.coderholic.com
user="root" # MySQL super user
pass="" # Above user's password
kill_user="myapp" # User who you want to kill all the connections for
# Get the connection IDs for all connections from the user $kill_user
processList=$(mysql -u$user -p$pass -s -e "SHOW FULL PROCESSLIST" | grep $kill_user | awk '{print $1}')
# Loop through all the connection IDs and kill the connection
for id in $processList
do
result=$(mysql -u$user -p$pass -s -e "KILL $id")
echo $result
done
The MySQL superuser account name and password, along with the user you want to kill all the connection for, are embedded within the script. It would be quite simple to pass these in as command line arguments if you wanted to though.
