Check and Update max_connections value to fix MySQL Error: Too many connections

As a database administrator some time I have noticed on issue MySQL Error: Too many connections on database server. Which indicates that all available connections are in use by other clients. It happens when server reach the limit of max_connections. Most of time I have noticed this happens when either bugs in applications not closing connections properly or due to wrong design.

In this tutorial I am going to explain how we can fix max_connections (MySQL Error: Too many connections) MySQL Error  updating the max_connections value in MySQL. I assume you have enough RAM on server to handle the increased number.

Check Current max_connections Value

You can check the current max_connections setting following below SQL command. Value are saved with variable named max_connections. Login to MySQL and run the below command.

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 66 |
+-----------------+-------+
1 row in set (0.01 sec)

As you can see above output the max_connections value is configured to 66.

Increase and Update max_connections Value

Before increasing the value I assume you have enough RAM and resources to handle the increased number and more queries. We can change the setting to e.g 150 with below command without restarting the MySQL server. This will work immediately but it will be reset to its previous value e.g to 66 after restarting MySQL server. To set this value permanently you will need to update the my.cnf configuration file.

mysql> SET GLOBAL max_connections = 150;

Edit my.cnf file to update max_connections value

In CentOS, RHEL and Fedora you can find the mysql configuration file at /etc/my.cnf location. In other distros
it may be on different locations. Update the /etc/my.cnf file and add the below setting under the [mysqld] section.

max_connections = 150

After changing the value restart the MySQL server and check the value again.

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 150 |
+-----------------+-------+
1 row in set (0.01 sec)

Now this time you can see the value is set to 150.

Note : Increasing the max_connections value according to your server RAM and resources to handle the more MySQL queries.

I hope this article will be helpful to fix MySQL Error: Too many connections in MySQL . Read our another article Basic Mysql Commands For Database Administrator and Step-by-step mysql 5.6 server installation on centos 6.x and redhat 6.x. If you have any queries and problem please comment in comment section or you can also ask your question.