How can I increase the max_connections in MySQL?

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:

• MySQL manual explaining the max_connections variable
• StackOverflow post with a great answer about configuring max_connections
• Another StackOverflow post regarding an issue updating max_connections

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.