Summarize Mysql two ways to modify the maximum number of connections _mysql

Problem

When using the MySQL database, there is often a problem with the Can not connect to MySQL server. Too many connections -mysql 1040 error, because the number of connections that have not been released to MySQL has reached the upper limit of MySQL. Typically, MySQL's maximum number of connections defaults to 100, up to 16384.

There are two common ways to modify the maximum number of connections.

Solution One: by command

The set GLOBAL max_connections=100; maximum number of connections can be set to 100 by command, which takes effect immediately and does not require a restart of the MySQL service. As shown in the following illustration:

It should be noted that the root permissions of the MySQL account to operate, otherwise will be reported “1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation” errors.

At the same time, from the above figure also can be seen, set max_connections minimum value is 1.

When the setup is complete and I connect through another terminal, I quote the following error message: 1040-Too many connections .

Workaround Two: Modify MY.CNF

Open the MySQL configuration file vim /etc/my.cnf , add max_connections=100一行 (if any, directly modify the value can), and then restart ：/etc/init.d/mysqld restart the service, this time effective.

Difference:

1. By modifying the configuration file, the service needs to be restarted, and the order is changed to take effect immediately.

2. The way to modify the configuration file, more stable and reliable. Because if the configuration file has max_connections=100 , and then use the command to modify, once the MySQL service is restarted, the number of connections specified in the configuration file will be reset.

Summarize:

When you modify the maximum number of connections, there is a question--is the value greater, the better, or how large is the appropriate? The size of this parameter should be considered in many factors, such as the number of line threading supported by the platform (Windows can only support to 2048), server configuration (especially memory size), the amount of resources (memory and load) each connection consumes, the response time required by the system, and so on. You can modify this parameter in the global or session scope. The increase in the number of connections will result in many ripple effects, which need to be avoided in practice. I hope this article will help you to use MySQL.