How to fix the MySQL error: Too Many Connections

Introduction to the MySQL error: Too Many Connections

When you have a web application (site, blog, forum, etc.) that is in high demand, that is, a large number of users visit, this translates into increased consumption on the server. If said web app uses a MySQL database and the queries are really many (due to bad programming of the web or by many users using the web), there is a possibility that MySQL will show this error:

mysqli_connect(): (HY000/1040): Too many connections

What does the MySQL: Too Many Connections error mean?

It means that too many requests are coming to MySQL, more than it can accept, more than it can queue or wait.

How to solve it?

Simple, we must increase the maximum limit of requests (connections) that MySQL supports.

I will give you two options to fix this problem:

1. We edit the file /etc/mysql/my.cfg:

nano /etc/mysql/my.cfg

In it we put the following under where it says [mysql]:

max_connections = 500 max_user_connections = 500

This will increase the maximum number of connections from 100 (which is the default) to 500.

We save and exit, then we restart the MySQL service and that's it. This change is permanent.

2. Another way to solve this problem is to change the maximum limit equal, but through a MySQL query.

Let's first show the current limit:

mysql --user="root" --password="PASSWORD" --execute='SHOW VARIABLES LIKE "max_connections";'

This will show us something like this:

+ ----------------- + ------- + | Variable_name | Value | + ----------------- + ------- + | max_connections | 151 | + ----------------- + ------- +

In other words, the current limit is 151 connections, well, let's raise it to 500 by means of a query:

mysql --user="root" --password="PASSWORD" --execute='SET GLOBAL max_connections = 500;'

Ready!

The problem in this way is that when the service is restarted, this configuration is lost.

To supply this detail, you can make a bash script that every X time verifies, or even add the line to the start or restart block of the daemon 😉

But then why do I want to know this 2nd option? ... well, that's what I used to say. But a month ago an Ubuntu Server ignored method No.1, so ... in extreme cases of silly OS, we have this 2nd option that works just as well 😉