Summarize two ways to modify the maximum number of connections in MySQL_MySQL

Question

When using MySQL database, we often encounter such a problem, which is "Can not connect to MySQL server. Too many connections"-mysql 1040 error. This is because the number of connections accessing MySQL that have not yet been released has reached MySQL's upper limit. Usually, the default maximum number of connections for MySQL is 100, and the maximum number can reach 16384.

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

Solution 1: Through command

You can set the maximum number of connections to 100 through the set GLOBAL max_connections=100; command. This method takes effect immediately and does not require restarting the mysql service. As shown in the picture below:

It should be noted that you need a mysql account with root permissions to operate, otherwise an error of "1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation" will be reported.

At the same time, as can be seen from the above figure, set the minimum value of max_connections to 1.

After the settings were completed, when I connected through another terminal, the following error message was reported: 1040-Too many connections.

Solution 2: Modify my.cnf

Open the mysql configuration filevim /etc/my.cnf, add the line max_connections=100 (if any, just modify the value directly), and then restart the service:/etc/init.d/mysqld restart, Effective at this time.

Difference:

1. By modifying the configuration file, you need to restart the service; if you modify it with a command, it will take effect immediately.

2. Modify the configuration file to make it more stable and reliable. Because if there is max_connections=100 in the configuration file, and then use the command to modify it, once the mysql service is restarted, the number of connections specified in the configuration file will prevail again.

Summary:

When modifying the maximum number of connections, there is a question - is the larger the value, the better, or how high should it be set? The size of this parameter should be considered based on many factors, such as the number of thread libraries supported by the platform used (windows can only support up to 2048), server configuration (especially memory size), and the resources occupied by each connection (memory and load) How much, the response time required by the system, etc. This parameter can be modified in the global or session scope. The increase in the number of connections will bring about many chain reactions, and the negative impacts caused by this need to be avoided in practice. I hope this article will be helpful to everyone using mysql.