MySQL Error “Too many connections” and how to resolve it

Uncategorized / May 9, 2022 / ashishpsk / 0

The maximum number of connections threads allowed for the server is contained in the system variable max_connections. The default value is 151. To see the value to which this variable is set, run the following SQL command:

$ mysql –u root –p
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

To change the value of the system variable max_connections, the –max_connections option can be used. To change this variable temporarily while the server is running, enter the following SQL statement:

$ mysql –u root –p
mysql> SET GLOBAL max_connections = 512;

When the MySQL server daemon (mysqld) is restarted the above value will set back to the default value of 151. To make changes permanent use the below method.

Now login to MySQL, the too many connection error fixed. This method does not require server restart. After MySQL server restart, the max_connection variable value again roll back to previous value. In order to make the max_connection value persistent, modify the value in the configuration file.

Stop the MySQL server:
Service mysql stop
Edit the configuration file my.cnf.
vi /etc/my.cnf
Find the variable max_connections under mysqld section.
[mysql]
max_connections = 100
Set into higher value and save the file.
Start the server.
Service mysqld start

Note: use systemctl manager to stop and start the service if, service command not working.

ashishpsk