MySQL error 1040 “Too many connections” & Increase max connections
If you have encountered the error “Too many connections” while trying to connect to a MySQL Server, that means it reached the maximum number of connections, or all available permitted are in use by other clients and your connection attempts will get rejected.
That number of connections is defined via the
max_connections system variable. To open for more connections, you can set a higher value for
To see the current value of
max_connections, run this command:
SHOW VARIABLES LIKE "max_connections";
By default, it’s set to 151. But MySQL actually allows up to
max_connections + 1, which is 151 + 1 for the default setting. The extra connection can be used by the user with
SUPER privilege only.
To increase the
max_connections value, let’s say 500, run this command:
SET GLOBAL max_connections = 500;
The command takes effect right after you execute it, but it only applies to the current session. If you want it to be permanent until you re-adjust it the next time, you have to edit the configuration file
my.cnf (normally it’s stored in
[mysqld] section add the following line:
max_connections = 500
Then restart the MySQL server to take effect.
One thing to keep in mind that there is no hard limit to setting up maximum
max_connections value, but increasing it will require more RAM to run. The number of maximum connections permitted has to be calculated based on how much RAM you have and how much is used per connection. In many cases, if you run out of usable disc space on your server partition or drive, MySQL might also return this error.
The maximum number can be estimated by the formula:
max.connection=(available RAM-global buffers)/thread buffers
So increase it with caution.
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
Download TablePlus for Mac. It’s free anyway!
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.