MySQL is a popularly used relational database manager which is used to manage the data inside the database. While working with MySQL, a limited number of connections are allowed. When the maximum limit is exceeded, the error “Too many connections” appear.
This guide will state the possible reasons for the above-stated error and the solution to fix the error.
- Reason: Maximum Connection Limit is Reached
- Solution 1: Increase Maximum Connections Limit Temporarily
- Solution 2: Increase Maximum Connections Limit Permanently
Reason: Maximum Connection Limit is Reached
The error “Too many connections” while connecting to MySQL server occurs when the maximum available connections are already in use by the user or other clients such as more queries are sent than the maximum available connections limit.
Solution 1: Increase Maximum Connections Limit Temporarily
This error can be fixed by increasing the max_connection limit of the MySQL server. We can increase the max_connections limit either temporarily for the current session only or permanently using the MySQL configuration file (/etc/mysql/my.cnf or ~/.my.cnf). To remove this error, follow these below-performed steps.
Step 1: Log in to MySQL Server
We can log in to the MySQL server as a root user without the password or with a password by using the following commands:
Note: Replace your user password with PASSWORD in the below command.
$ sudo mysql -u root -p #log in to mysql without password $ sudo mysql --user="root" --password="PASSWORD" #log in to mysql with password
When logged in to the MySQL server, a prompt will open as indicated in the output.
Step 2: Check the Maximum Connections Limit
To check the max_connections limit, run the below-written command in the MySQL prompt:
> SHOW STATUS LIKE 'max_used_connections';
The output shows that max_connectionjs limit is 151 by default.
Step 3: Increase the Maximum Connections Limit
Now, increase the MYSQL max_connections to the desired connections (in this case, 500):
> SET GLOBAL max_connections = 500;
Now, the max_connections limit is increased to 500, but it is only for the current session. Let’s get into solution 2 for further proceedings.
Solution 2: Increase Maximum Connections Limit Permanently
The max_connections limit for MySQL can be increased permanently using the MySQL configuration file (/etc/mysql/my.cnf or ~/.my.cnf). Follow the steps to apply the connection limit permanently:
Step 1: Check Maximum MySQL Connections
Let’s check the maximum available connection for the MySQL connection using any of the below commands:
#To use MySQL without password $ sudo mysql -u root -p --execute='SHOW VARIABLES LIKE "max_connections"'; #To use MySQL with password $ sudo mysql --user="root" --password="PASSWORD" --execute='SHOW VARIABLES LIKE "max_connections"';
The connection limit is 151.
Step 2: Increase MySQL Maximum Connections
Open the file “~/.my.cnf” or “/etc/mysql/my.cnf” with any editor:
$ sudo nano ~/.my.cnf
Add the below lines to increase the max_connections and max_user_connections limit to “500”:
max_connections = 500 max_user_connections = 500
Press “Ctrl + O” to save and “Ctrl + X” to exit.
Step 3: Restart the MySQL Server
Restart the MySQL server by using the below-mentioned command:
$ sudo systemctl restart mysql
The MySQL server is restarted, and changes are saved. Now, the max_connections limit is increased to “500”, and the “too many connections” error is fixed.
While connecting the MySQL server “too many connections” error can occur that shows that more queries are sent to MySQL than the available maximum connections limit. This error can be fixed by increasing the max_connections limit. To increase max_connections for the current session, use the “SET GLOBAL max_connections =<number-of-connection>;” in the MySQL server or increase the limit in the MySQL configuration file.