Increasing Max Number of Connections in MySQL

Thought of blogging this so I won’t waste time googling when I need it next time.

MySQL Server Version: 5.5.46
OS: U
buntu 14.04.2

To view the current maximum number of connections, you can use the following command after login to MySQL from the console.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.08 sec)

There are two ways to increase the max number of connections.

1. MySQL command line – Doesn’t require to restart MySQL server. Will affect until the next MySQL restart.

mysql> SET GLOBAL max_connections = 500;
Query OK, 0 rows affected (0.00 sec)

2. Editing my.cnf – Requires a MySQL restart for the changes to get affected.

Open my.conf file using a text editor.

$ sudo vim /etc/mysql/my.cnf

Search for max_connections entry in the file, uncomment (or add the entry if not found) it and set a desired value.

max_connections        = 500

Save the file and restart MySQL service.

$ sudo service mysql stop
$ sudo service mysql start

Reference
[1] – https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html
[2] – http://www.electrictoolbox.com/update-max-connections-mysql/

Advertisements