Many developers will encounter the abnormal situation of “MySQL: ERROR 1040: Too many connections”. One of the reasons for this situation is that the traffic volume is Too high and MySQL server cannot stand it. At this time, we should consider increasing the pressure of reading from the server. Another reason is that the max_connections value in the MySQL configuration file is too small.
view mysql maximum number of connections:
mysql> show variables like ‘%max_connections%’;
view server response maximum number of connections :
mysql> show global status like ‘Max_used_connections’;
you can see that the maximum number of connections that the server responds to is 2, far below the maximum number of connections allowed by the mysql server.
the ideal setting range for mysql server’s maximum connection value is that the maximum connection value of the server response accounts for more than 10% of the maximum connection value of the server. If the value is less than 10%, it indicates that the maximum connection limit of mysql server is set too high.
Max_used_connections/max_connections *100% = 2/151 *100% ≈ 1%
we can see that the percentage is much lower than 10% (because this is a local test server, the result value is not of great reference significance, you can set the upper limit of connection number according to the actual situation).
sets the maximum connection value:
mysql> set GLOBAL max_connections=256;
The number of connections
exceeds the value set by MySQL and is related to both max_connections and wait_timeout. The larger the value of wait_TIMEOUT, the longer the idle wait for the connection, resulting in a larger number of current connections.
wait_timeout had great disadvantages the default value is 28800 (8 hours), the amount of SLEEP on its embodiment is the MySQL process does not release in time, a drag on system performance, but also can’t take this refers to the set is too small, or you may encounter “ MySQL has gone away ” issues such as, generally speaking, I think the wait_timeout set to 10 is a good choice, but in some cases may also be a problem, For example, if you have a CRON script where the interval between two SQL queries is greater than 10 seconds, then this setting is problematic (of course, this is not a problem that cannot be solved, you can use mysql_ping every now and then in the program to let the server know that you are alive and recalcate wait_timeout)
mysql> show global variables like ‘wait_timeout’;
mysql> set global wait_timeout=10;
production server database can not be randomly restarted, we have to find a way to manually release some useless connections.
mysql> Show the processlist. You can get all MySQL connections to this server
you can see a list of MySQL data connections, and each will have a process ID number (in the first column of the above table). We just type the command:
mysql> kill 1180421;
where 1180421 is the process number found and killed in the process list
to prevent logins from occurring when too many connections occurs , the mysql manual has the following instructions:
mysqld actually allows 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.
therefore, must be only
gives root the SUPER privileges , and all database connected accounts cannot give SUPER privileges. The previously mentioned failure to log in after reporting an error is due to the root user configured directly by our application
view all users of the current database :
select user,host,password from mysql.user;
gives super PRIVILEGES (both super and ALL PRIVILEGES available) TO the user:
GRANT super ON *.* TO ‘mysql’@’localhost’;
GRANT super ON *.
GRANT ALL PRIVILEGES ON *.* TO ‘mysql’@’localhost’;
user’s super PRIVILEGES (both super and ALL PRIVILEGES are available) :
REVOKE super ON *.* FROM ‘mysql’@’localhost’;
REVOKE super ON *.
REVOKE ALL PRIVILEGES ON *.* FROM ‘mysql’@’localhost’;
view the permissions granted to users
SHOW GRANTS FOR ‘mysql’@’localhost’;
