Recently I came across a MySQL error “Too Many Connections”. Though this was a first for me but as I came to know afterwards, it is a pretty common error in MySQL. The solution to this error is very easy as we only need to increase the number of mysql connections.
There are two ways using which we can resolve this error. We can either increase the number of mysql connections for the current session or we can make changes to increase the number of mysql connections permanently. In this tutorial we will discuss both the ways to resolve the MySQL error “Too Many Connections”.
Increasing mysql connections for current session
Before we make changes to increase the number of mysql connections, we will check the current number of configured mysql connections. To check it, connect to mysql from your terminal,
$ mysql –u root –p
Enter the password to connect to mysql & once connected, execute the following command,
mysql> SHOW VARIABLES LIKE “max_connections”;
This will bring the max number of connections configured on the screen as output. Its usually 151 by default & we will now increase the value to 1000. To do so , run the following command from mysql terminal only,
mysql> SET GLOBAL max_connections = 1000;
Our maximum mysql connections value has now been increased to 1000 but its for current session only. As soon as we restart the mysql service or restart the system, this value will reset to default.
Increasing mysql connections for permanently
To increase the number of mysql connections permanently, we need to edit the mysql configuration file I.e. ‘/etc/my.cnf’. Open the file,
$ sudo vim /etc/my.cnf
& make the following entry in the file,
max_connections = 1000
Now save the file & exit, than restart the mysql service to implement the changes.
$ sudo systemctl restart mysqld