Sometimes MySQL server may give “Too many connections” error message when you try to connect to a MySQL database. Here’s how to increase max connections in MySQL to fix this problem.
What causes MySQL Too Many Connections?
If you get “Too many connections” error message while connecting to MySQL database it means all available connections have been used by the various clients and your MySQL Server cannot open any new connections until any of the existing connections is closed.
Bonus Read : How to Store UTF8 Characters in MySQL
How Many Connections can MySQL handle?
By default, MySQL 5.5+ can handle up to 151 connections. This number is stored in server variable called max_connections. You can update max_connections variable to increase maximum supported connections in MySQL, provided your server has enough RAM to support the increased connections.
Bonus Read : How to Rank Over Partition in MySQL
How to Increase Max Connections in MySQL
Here are the steps to increase max connections in MySQL.
1. Check the default max connections
Log into MySQL command line tool and run the following command to get the current default max connections supported by your database server.
mysql> show variables like "max_connections";
You will see the following output.
+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 100 | +-----------------+-------+
Bonus Read : How to Change MySQL Timezone in Query
2. Increase Max Connections
There are two ways to increase MySQL connections. Here’s the command to update max connections to 200 via MySQL command line tool without restart.
mysql> set global max_connections = 200;
The above command will increase max connections without restart but once the server is restarted, the changes will be gone. This method is useful to increase available connections in a production system such as a website/app, without having to restart your database server.
To permanently increase max connections, open my.cnf file,
$ sudo vi /etc/my.cnf
Depending on your Linux distribution and type of installation, my.cnf file may be located at any of the following locations.
- /etc/my.cnf
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- ~/.my.cnf
Add the following line under [mysqld] section.
max_connections = 200
Now if you restart MySQL server, the changes will persist.
3. Restart MySQL Server
Restart MySQL Server to apply changes
$ sudo service mysql restart
Hopefully, the above tutorial will help you increase max connections in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it for free!
