How to kill MySQL connections

mysql connection
leora picture leora · Feb 8, 2011 · Viewed 171.7k times · Source

I'm building a website with MySQL. I'm using TOAD for MySQL and suddenly I can't connect to the database as I'm getting an error:

"Too many connections"

Is there any way in Toad for MySQL to view existing connections to be able to kill them or simple close all connections all together?

Answer

Konerak picture Konerak · Feb 8, 2011

No, there is no built-in MySQL command for that. There are various tools and scripts that support it, you can kill some connections manually or restart the server (but that will be slower).

Use SHOW PROCESSLIST to view all connections, and KILL the process ID's you want to kill.

You could edit the timeout setting to have the MySQL daemon kill the inactive processes itself, or raise the connection count. You can even limit the amount of connections per username, so that if the process keeps misbehaving, the only affected process is the process itself and no other clients on your database get locked out.

If you can't connect yourself anymore to the server, you should know that MySQL always reserves 1 extra connection for a user with the SUPER privilege. Unless your offending process is for some reason using a username with that privilege...

Then after you can access your database again, you should fix the process (website) that's spawning that many connections.

Related questions

MySQL show status - active or total connections?

When I run show status like 'Con%' it shows the number of connections, which is 9972 and constantly growing. Is this an active number of connections or connections made in total?

Read More
How to grant remote access permissions to mysql server for user?

If I do SHOW GRANTS in my mysql database I get GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'some_characters' WITH GRANT OPTION If I am not mistaken, root@localhost means that user root can access the …

Read More
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

The following code: Class.forName("com.mysql.jdbc.Driver"); Connection m_connection = DriverManager.getConnection("jdbc:mysql://localhost","root","root"); Throws this exception on getConnection(): java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) at com.mysql.jdbc.…

Read More