How to Resolve Too Many Connections Error in MySql or MariaDB

Many of us have faced the issue of Too Many Connections error in MySQL/MariaDB with different errror codes like specified below:

(08004/1040): Too many connections

(HY000/1040): Too many connections

The above errors appear when all MySql/MariaDB connections allowed are currently being used up and there are more connections requests than allowed. Though this may also be case of poor development, if the database connections are not closed properly, but this tutorial assumes that the the development related optimisations have been applied properly and still this above issue is being faced.

To resolve this error, we need to increase the connections limit via the max_connections varibale of MySql/MariaDB. For this we will follow 3 different steps as below:

  1. Check the current max_connections value
  2. Change the max_connections value
  3. Update the Global values

1. Check the current max_connections value

In order to check the value please log in to MySql using the terminal as below:
  mysql -u root -p
Once logged in, check the variables using the command:

SHOW variables;

The result lists out all the variables being used. You can scroll upwards to check the valur of variable max_connections. You can exit using quit; command.

2. Change the max_connections value

In order to change the value please open up the terminal, and edit the configuration file (the availability may differ on different OS installations):

sudo nano /etc/my.cnf

Once the file is open please enter the max connections value directly the first line

[mysql]

And add the number of connections that you want to specify as max (I am adding 1000 for example)

max_connections=1000

Please save the file and exit the file (Ctrl+O, Ctrl+X).

3. Update the Global values

Please update the global values of max_connections using MySql / MariaDB by logging in via terminal:

mysql -u root -p

You can the set the global value of using the command below (I am using 1000 for example):

SET GLOBAL max_connections=1000;

Thereafter you can quit database using the command below:

quit;

That is all you would require to resolve the Too Many Connections error of MySql / MariaDB. You can further monitor the performance using further Sql command or by using MySql Workbench community edition.