Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need an efficient way to disconnect all clients with a given username from MySQL. I thought about changing the users password but I think that is only checked when the connection is made.

Ideas?

share|improve this question

2 Answers 2

You could use "SQL to SQL" method below (just pass in extra connection options to mysql client as needed):

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';" | mysql -vv

Note: This works with MySQL 5.1 and 5.5. This would have to be implemented differently for older MySQL versions as information_schema does not have the processlist table.

Options used:

-N means that you do not want to get column names back.
-B puts it into batch mode, so that you do not get MySQL's table layout.
-e executes the following statement.
-v controls the verbosity, could be used up to three times.

Explanation of how it works:

First the KILL statements are generated along with IDs.

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';"

Sample output:

KILL 1061;
KILL 1059;
KILL 1057;

Then those statements are executed.

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';" | mysql -vv

Sample output:

--------------
KILL 1061
--------------

Query OK, 0 rows affected

--------------
KILL 1059
--------------

Query OK, 0 rows affected

--------------
KILL 1057
--------------

Query OK, 0 rows affected
share|improve this answer
    
This is much more direct for MySQL 5.5. +1 !!! –  RolandoMySQLDBA Oct 31 '11 at 19:39

On linux you can use something like that.

My approach is very simple. In the first step we send 'show processlist' to our database. The result is a list with all connected users. In the next step we use the good old grep command to filter the usernames. With awk we generate the 'kill command'. In the last step we send all kill commands to mysql. Everything have to be concatenated with the | symbol.

mysql -uroot -e 'show processlist' | grep username | awk {'print "kill "$1";"'}| mysql -uroot
share|improve this answer
    
It would be good to add some brief explanation for this code. –  RLF Dec 12 at 14:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.