ERROR 1040 (HY000): Too many connections in MySQL

Error:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
Enter password: *********
ERROR 1040 (HY000): Too many connections

Solution:

Check the max_connection parameter setting in MySQL

mysql> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 250   |
+-----------------+-------+
1 row in set (0.01 sec)

Change the current value at right time without restart the DB.


mysql> SET GLOBAL max_connections = 500;
Query OK, 0 rows affected (0.00 sec)

Change the max_connections parameter in Configuration file for persistent changes

Note: 
1. We need to make change persistent by modify the configuration file.
2. If we increase max_connections value then we need more RAM to run.
Formula for calculate is max.connection=(available RAM-global buffers)/thread buffers  

--For persistent changes modify the configuration file:
1. Open the my.ini file on windows
2. update the max_connections to 500
3. Restart the mysql services.

--My.ini file parameter example:
# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=500 



Verify the max Connection.

mysql> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.01 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.