How to Increase max_connections in mysql without restarting mysqld service? global-variable

By default in mysql database server max_connections is set to 100. This value indicates how many maximum concurrent connections mysql server can handle. If mysql reaches to it maximum (max) limit then you can see errors like “too many connections“. I assume that you have enough hardware resources (Mainly RAM )to handle more connections, here with this article I will share a TIP to increase max_connections in mysql.

As we know my.cnf is default configuration file for mysqld service and by default it is located in /etc directory unless and until you have changed it.

To find out how many max_connections are allowed currently on your mysql server use following command from mysql prompt.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 100                |
+-------------------+
1 row in set (0.00 sec)

max_connections is a GLOBAL variable. we can increase it on the fly without restarting mysqld service.
To do so use following command.

mysql> set global max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

Now, If you check again you will see that limit of max_connections is increased.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 200               |
+-------------------+
1 row in set (0.00 sec)

Note:
It is important that you edit your /etc/my.cnf to add max_connections = 200 otherwise when you restart mysqld service in future, It will complain again after it hits the old max_connections limit.
Mysql server is throwing “too many connections” error ?
How to increase number of concurrent mysql connections without restarting mysqld service?
errrr… mysql-server not allowing any more connections ?
Quick how to on increasing max_connections in mysql. restart not required?

 

Leave a Reply

Your email address will not be published. Required fields are marked *