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 max_connections.

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 /etc/my.cnf).

Under the [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. 