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 strong>” 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’;
- In depth analysis of mysq exceeding the number of connections error 1040 (HY000): too many connections
- ERROR 1040 (08004): Too many connections
- Using the TP framework, an error is reported sqlstate   too many connections
- PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE  Too many connections
- Appium error collection, sorting out appium errors
- mysql5.7.26：[ERR] 1118 – Row size too large (＞ 8126)
- MySQL driver compiling method of QT under windows and solutions to abnormal errors
- String index out of range: 100 error report details and Solutions
- Solutions to UCenter Info: MySQL query error app1
- MySQL local connection Error 1130_ The solution of MySQL 1130 error report when remote connection through Navicat for MySQL
- MySQL error: can’t create table ‘..’（ errno:150 ）Solutions
- MySQL: if the remote connection using navicatip fails, prompt “is not allowed to connect to this MySQL server”
- A solution to automatically convert special characters into Unicode when taking out data from MySQL and encapsulating it into JSON
- (error when importing database): MySQL 2006-mysql server has gone away
- MySQL error — multiple methods of failed to find valid data directory and MySQL setting password appear
- After installing MySQL again under Linux, the solution of ‘MySQL module failed to start’ appears
- failed to open stream: HTTP wrapper does not support writeable connections
- PHP connection to MySQL database error: call to undefined function MySQL_ connect()
- The MySQL service suddenly hangs up with the error message can’t connect to MySQL server on ‘localhost’ (10061)
- MySQL – ERROR 1146 (42S02): Table ‘mysql.user’ doesn’t exist