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