MySQL 5.7中的max_connections

I met a problem, the value of max_connction in MySQL is 214 after I set it 1000 via edit the my.cnf, just like below:

我遇到了一个问题，在我通过编辑my.cnf设置1000之后，MySQL中max_connction的值是214，如下所示：

hadoop@node1:~$ mysql -V
mysql  Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using  EditLine wrapper

MySQL version: 5.7

MySQL版本：5.7

OS version : ubuntu 16.04LTS

操作系统版本：ubuntu 16.04LTS

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

As we can see, the variable value of max_connections is 151. Then , I edit the configuration file of MySQL.

我们可以看到，max_connections的变量值是151.然后，我编辑MySQL的配置文件。

yang2@node1:~$ sudo vi /etc/mysql/my.cnf
[mysqld]

character-set-server=utf8
collation-server=utf8_general_ci
max_connections=1000

Restart MySQL service after save the configraion.

保存混淆后重启MySQL服务。

yang2@node1:~$ service mysql restart
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to restart 'mysql.service'.
Multiple identities can be used for authentication:
 1.  yangqiang,,, (yang2)
 2.  ,,, (hadoop)
Choose identity to authenticate as (1-2): 1
Password: 
==== AUTHENTICATION COMPLETE ===
yang2@node1:~$

Now, we guess the max_connection is 1000, really?

现在，我们猜max_connection是1000，真的吗？

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

It is 214. I do not really understand this result, who can help me? thx!

这是214.我真的不明白这个结果，谁可以帮助我？谢谢！

4 个解决方案

#1

As MySQL documentation on max_connections setting says:

关于max_connections设置的MySQL文档说：

Increasing this value increases the number of file descriptors that mysqld requires. If the required number of descriptors are not available, the server reduces the value of max_connections.

增加此值会增加mysqld所需的文件描述符数。如果所需的描述符数量不可用，则服务器会减小max_connections的值。

This means that probably your MySQL server does not have enough resources to maintain the required number of descriptors.

这意味着您的MySQL服务器可能没有足够的资源来维护所需数量的描述符。

MySQL documentation on How MySQL Opens and Closes Tables makes it clear that:

有关MySQL如何打开和关闭表的MySQL文档清楚地表明：

The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems permit you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so.

table_open_cache和max_connections系统变量会影响服务器保持打开的最大文件数。如果增加这些值中的一个或两个，则可能会遇到操作系统对每个进程的打开文件描述符数量施加的限制。许多操作系统允许您增加开放文件限制，尽管该方法因系统而异。请参阅操作系统文档以确定是否可以增加限制以及如何增加限制。

#2

You may set the value manually, e.g.

您可以手动设置值，例如

set global max_connections=500;

however, after a restart of MySQL the value is reset to 214.

但是，重新启动MySQL后，该值将重置为214。

The solution depends on the (version of) OS and the MySQL version. With Ubuntu 16.04 and MySQL >= 5.7.7 following works:

解决方案取决于（版本）操作系统和MySQL版本。随着Ubuntu 16.04和MySQL> = 5.7.7以下工作：

systemctl edit mysql

systemctl编辑mysql

Enter

输入

[Service]
LimitNOFILE=8000

save, this will create a new file

保存，这将创建一个新文件

/etc/systemd/system/mysql.service.d/override.conf

and restart the server:

并重新启动服务器：

systemctl daemon-reload
systemctl restart mysql

For other environments: Can not increase max_open_files for Mysql max-connections in Ubuntu 15

对于其他环境：无法在Ubuntu 15中为Mysql max-connections增加max_open_files

#3

Add session required pam_limits.so in /etc/pam.d/common-session (usually is not present by default).

在/etc/pam.d/common-session中添加会话所需的pam_limits.so（默认情况下通常不存在）。

The in /etc/security/limits.conf you can add some limits:

在/etc/security/limits.conf中你可以添加一些限制：

*   hard    nofile  8192
*   soft    nofile  4096

Also check using ulimit -a the open files limit. This you can increase with ulimit -n 4096

还要检查使用ulimit -a打开文件限制。这可以通过ulimit -n 4096增加

Make sure you reboot at the end.

确保最后重新启动。

#4

Follow the following steps:

请按照以下步骤操作：

cp /lib/systemd/system/mysql.service /etc/systemd/system/
echo -e "\r\nLimitNOFILE=infinity" >> /etc/systemd/system/mysql.service
echo "LimitMEMLOCK=infinity" >> /etc/systemd/system/mysql.service
sudo systemctl daemon-reload
sudo service mysql restart

And change or add he following line into file /etc/mysql/mysql.conf.d/mysqld.cnf :

并将以下行更改或添加到文件/etc/mysql/mysql.conf.d/mysqld.cnf中：

[mysqld]
max_connections=110

Just this! @Mahdi_Mohammadi

只是这个！ @Mahdi_Mohammadi

#1