How can I increase the max_connections in MySQL?

When trying to connect to my database error: error connecting: Error: ER_CON_COUNT_ERROR: Too many connections.

How can I increase max_connections?

3 Replies

From what I know, if you would like to persistently have this setting for your MySQL databases, you would modify the max_connection setting in your MySQL configuration file. The location of this file may vary depending on the version of MySQL:

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /etc/mysql/mysql.conf.d/mysqld.cnf

You could also determine where the location of the configuration file is by running mysqld --help --verbose. That command will output something along these lines:

Default options are read from the following files in the given order:
/etc/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

In the configuration file, you'll want to find the [mysqld] section. Under that section, you'll want to insert/modify the line to max_connections = $defaultvalue. You would set $defaultvalue as the max connection desired.

Using this method would require you to restart your MySQL server by running the command: /etc/init.d/mysql restart. If that doesn't work, try running sudo systemctl restart mysqld

If you would like to increase the max connections temporarily, you would have to modify a variable within MySQL. You should be able to achieve this by doing the following:

  • Access your MySQL command line tool

  • Command: show variables like "max_connections";

  • This will return an output as such:

    Variable_name Value
    max_connections 100
  • If you have the proper permissions, change the variable by running the command: set global max_connections = $DesiredValue;. You would insert the desired max connections value in the place of $DesiredValue.

Changes using this method will take effect immediately, but will be reset to default values once MySQL is restarted.

Additional Resources:

Thank you, can you tell me what's the maximum connections that I can set? or is unlimited?

The maximum value you can set max_connections to is 100000. The minimum value is 1, while the default value is 151. Do keep in mind that setting a higher value may have affect the performance of your Linode.

While I cannot provide the optimal setting, these questions may help you determine what value would be best:

  1. How many users are using the application?
  2. How many applications connect to the database?
  3. What is the load and how queries are given?

It is best to consider the maximum amount of connections you have had in the past before setting this number, so you’ll have a buffer between that upper number and the max_connections value.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct