How to Increase max_connections in MySQL without restarting MySQL service.

How to Increase max_connections in MySQL without restarting MySQL service.

If mysql connection reaches to it maximum (max) limit then you can see errors like “too many connections”. You can increase the connection limit without restart 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 |
+——————-+
| 500 |
+——————-+

max_connections is a GLOBAL variable. we can increase it on the fly without restarting mysqld service.

Use following command to increase max connection.

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

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

mysql> select @@max_connections;
+——————-+
| @@max_connections |
+——————-+
| 600 |
+——————-+

Note: It is important that you edit your /etc/my.cnf to add max_connections = 600 otherwise when you restart mysqld service in future, It will complain again after it hits the old max_connections limit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s