Increase max_connections in MySQL/MariaDB without restarting mysqld service

[swanwish]

Get the content from Increase max_connections in MySQL/MariaDB without restarting mysqld service

Great article from Link

In short, to update the setting:

select @@max_connections;
set global max_connections = 200;
select @@max_connections;

Done.

[swanwish]

How to update the max_connections setting in MySQL

If you are getting "too many connections" errors in MySQL you can change the max_connections setting to allow more connections, assuming you have enough RAM to handle the increased number. This post looks at how to update the max_connections setting in MySQL.

The default setting for max_connections is 100. You can see what the current setting is by running the following SQL command from the MySQL command line tool, phpMyAdmin, or a similar tool:

show variables like "max_connections";

This will return a resultset like this, showing you what your current max connections setting is:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

You can change the setting to e.g. 200 by issuing the following command without having to restart the MySQL server (obviously it will only work if the user you are logged in as has sufficient permissions to do this):

set global max_connections = 200;

This will take effect immediately, but will be forgotten the next time MySQL is restarted. To make the change permanent you need to edit the my.cnf configuration file. On CentOS, RedHat and similar distributions this is at /etc/my.cnf; other distros will store it elsewhere.

Under the [mysqld] section add the following setting:

max_connections = 200

Now when you restart MySQL the next time it will use this setting instead of the default.

Note that increasing the number of connections that can be made will increase the potential amount of RAM required for MySQL to run. Increase the max_connections setting with caution!

[swanwish]

According to the docs, it means the total number throughout history:

Connections

The number of connection attempts (successful or not) to the MySQL server.

You can see the number of active connections either through the Threads_connected status variable:

Threads_connected

The number of currently open connections.

mysql> show status where `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 4     |
+-------------------+-------+
1 row in set (0.00 sec)

... or through the show processlist command:

mysql> show processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)