9

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?

7

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
  • This is much more direct for MySQL 5.5. +1 !!! – RolandoMySQLDBA Oct 31 '11 at 19:39
1

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
  • It would be good to add some brief explanation for this code. – RLF Dec 12 '14 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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