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.
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 184.108.40.206, “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.