How can I disconnect clients from MySQL?

Question

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?

Answer 1

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

Answer 2

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