MySQL max_connctions+1

connectionsmax-connectionsMySQLmysql-5.6

I experienced an incident that my connections are full. Even I as an administrator I cannot log to MySQL due to "too many connections.". Does anybody knows how to setup this max_connections+1? I use version 5.6.14. Thank you very much.

https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

Best Answer

Here is your basic problem: You most likely have many users in mysql.user other than root@localhost with the SUPER privilege. Please note the MySQL Documentation in your question:

mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.

You need to run this

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user
WHERE super_priv='Y' and user<>'root';

You need to take away the SUPER privilege from those users.

The fastest way to revoke the SUPER privilege from those users is to login to mysql as root@localhost and execute the following:

UPDATE mysql.user SET super_priv='N' WHERE
WHERE super_priv='Y' and user<>'root';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!

NOTE #1 : I have discussed this before on Jul 06, 2012 in How to resolve too many connections and fatal error in mysql running on vps

NOTE #2 : If you have other mysql users doing database backups and cronjobs, you need to make sure those users have all needs privileges for their respective operations after removing the SUPER privilege.

NOTE #3 : If you do not use other users to connect to mysql, you need to create extra users that do no have all privileges, but still have enough privileges for needed operations.

Related Question