MySQL 5.7 中的 max_connections
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39976756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The max_connections in MySQL 5.7
提问by StrongYoung
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。我不是很明白这个结果，谁能帮帮我？谢谢！
采纳答案by Shadow
As MySQL documentation on max_connectionssetting 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 Tablesmakes 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 系统变量影响服务器保持打开的最大文件数。如果您增加这些值中的一个或两个，您可能会遇到操作系统对每个进程打开的文件描述符数量施加的限制。许多操作系统允许您增加打开文件限制，尽管方法因系统而异。请查阅您的操作系统文档以确定是否可以增加限制以及如何增加。
回答by WernerE
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
/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
回答by Mohsen Abasi
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
回答by sonjaya sonjaya
1.Edit mysql service file
1.编辑mysql服务文件
#sudo cat /etc/systemd/system/multi-user.target.wants/mysql.service
# MySQL systemd service file
[Unit]
Description=MySQL Community Server
After=network.target
[Install]
WantedBy=multi-user.target
[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/run/mysqld/mysqld.pid
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
##this bellow for tuneup
LimitNOFILE=infinity
LimitMEMLOCK=infinity
2.edit /etc/mysql/mysql.conf.d/mysqld.cnf max_connections = 99999
2.编辑/etc/mysql/mysql.conf.d/mysqld.cnf max_connections = 99999
回答by Dragos
Add session required pam_limits.soin /etc/pam.d/common-session(usually is not present by default).
添加session required pam_limits.so的/etc/pam.d/common-session（通常是不存在默认情况下）。
The in /etc/security/limits.confyou can add some limits:
中/etc/security/limits.conf你可以添加一些限制：
* hard nofile 8192
* soft nofile 4096
Also check using ulimit -athe open files limit.
This you can increase with ulimit -n 4096
还要检查使用ulimit -a打开文件限制。这你可以增加ulimit -n 4096
Make sure you reboot at the end.
确保最后重新启动。
