Detailed introduction to the method of setting the number of mysql connections (Too many connections)

The following editor will bring you an articlemysqlHow to set the number of connections (Too many connections). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

During the use of mysql, I found that the number of connections exceeded~~~~

[root@linux-node1 ~]# mysql -u glance -h 192.168.1.17 -p
Enter password:
ERROR 1040 (08004): Too many connections

The solution is to modify the number of mysql connections under centos7 Method:

1) Temporary modification

MariaDB [(none)]> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 214 |
+-----------------+-------+
1 row in 
set
 (0.00 sec)
MariaDB [(none)]> set GLOBAL max_connections=1000; 
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.00 sec)

2) Permanent modification:

Configure/etc/my.cnf
[mysqld]Add a new line with the following parameters:

max_connections=1000

Restart the mariadb service and check the maximum number of connections to the mariadb database again. You can see the maximum number of connections. The number of connections is 214, not the 1000 we set.

MariaDB [(none)]> show variables like 'max_connections'; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| max_connections | 214 | 
+-----------------+-------+

This is because mariadb has a default limit on the number of open files. You can increase the number of open files by configuring /usr/lib/systemd/system/mariadb.service.

Configuration/usr/lib/systemd/system/mariadb.service

[Service]Add two new lines of the following parameters:

LimitNO
FILE
=10000
LimitNPROC=10000

Reload the system service and restart the mariadb service

systemctl --system daemon-reload 
systemctl restart mariadb.service

Check the maximum number of connections to the mariadb database again, you can see that the maximum number of connections is already 1000

MariaDB [(none)]> show variables like 'max_connections'; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| max_connections | 1000 | 
+-----------------+-------+

The above is the detailed content of Detailed introduction to the method of setting the number of mysql connections (Too many connections). For more information, please follow other related articles on the PHP Chinese website!