How do I remove all idle connections in MySQL without access to the command line? I need a temporary solution as one of our services isn't closing its connections properly and flooding the database with idle connections.

CodePudding user response：

You can kill multiple connections using a stored procedure such as

CREATE PROCEDURE kill_all_sleep_connections()
BEGIN
  WHILE (SELECT count(*) as _count from information_schema.processlist where Command = 'Sleep') > 10 DO
    set @c := (SELECT concat('KILL ', id, ';') as c from information_schema.processlist where Command = 'Sleep' limit 1);
    prepare stmt from @c;
    execute stmt;
  END WHILE;
END;

This will create a string like KILL 4312; for each row matching the WHERE Command = 'Sleep' condition, referencing the id. Then it EXECUTE's the string as a query, ending the connection.

Use it like this

call kill_all_sleep_connections()

You should now have less than 10 idle connections on the database instance. The WHILE is set to < 10 as a service might create connections very quickly so increase/decrease as you see fit.

You can view all the connections with

• SHOW PROCESSLIST; or SELECT * FROM information_schema.PROCESSLIST;

You can kill a single connection by referencing the id in those results using KILL

• KILL 4212;

A proper solution is to close each connection in each service once it is done querying the database.