Difference between revisions of "How to solve MySQL max user connections error"

Latest revision as of 05:14, 16 January 2021

How to solve MySQL max connections/user connections error

If clients encounter Too many connections errors when attempting to connect to the mysqld server, all available connections are in use by other clients.

The permitted number of connections is controlled by the max_connections system variable. The default value is 151 to improve performance when MySQL is used with the Apache Web server. To support more connections, set max_connections to a larger value

Checking the max_connections

You can run the following command to check the current value of max_connections

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

This shows the current value, you can check the current number of connections by running:

MariaDB [(none)]> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 146   |
+-------------------+-------+
1 row in set (0.02 sec)

You can increase this value in two ways:

Without restarting mysql (temp change)

 MariaDB [(none)]> SET GLOBAL max_connections = 300;
 Query OK, 0 rows affected (0.00 sec)

Checking the new value:

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.00 sec)

Restarting Mysql via my.cnf (permanent change)

Or set this parameter in my.cnf that located at /etc/my.cnf

vi /etc/my.cnf
max_connections = 300

Then restart mysql, this is a permanent change to the max_connections at this point