Linux command to kill all MySQL sessions from a specific User?

Question

How can i kill all sessions from a user in mysql using a linux command line command?
I have a user that is using all on my database connections and insted of killing one by one i want to make a script to kill them all !

Answer 1

Try this :

mysql -uuser -ppassword -e 'show processlist' | grep USER_NAME | awk {'print "kill "$1";"'}| mysql -uuser -ppassword

The command will list the processes running in the database and return a kill 'query id'; command and pipe it into mysql again.
Be carefull with this command as there is no rollback form it :)

Answer 2

You may also use common_schema's kilall() procedure from within your MySQL server. Use:

call killall('evil_user');

Disclosure, I am author of this tool.