MySQL 如何在mysql中永久设置最大连接数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23977095/
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
How to set max connections in mysql permanently?
提问by Intrepid Web Studio
I need some help to set MAX connections value permanently in MySql. I have tried but I can't find a permanent solution. Now, I am using temporary solution by changing in command prompt like in this article.
我需要一些帮助才能在 MySql 中永久设置 MAX 连接值。我试过了，但我找不到永久的解决方案。现在，我通过更改命令提示符来使用临时解决方案，如本文所述。
回答by Nav
Since this question shows up on the search results when people search for a solution, and now that the solution has changed, I felt it necessary to post an answer.
Ubuntu 15.04+ no longer respectsthe limits set in /etc/security/limits.conf. So if you set max connections and you don't see any effect, have a look at the log file at sudo vim /var/log/mysql/error.logand you'll see Changed limits: max_connections: 214 (requested 5000).
由于这个问题在人们搜索解决方案时出现在搜索结果中，现在解决方案已经改变，我觉得有必要发布一个答案。
Ubuntu的15.04+不再尊重中设定的限制/etc/security/limits.conf。因此，如果您设置了最大连接数但没有看到任何效果，请查看日志文件，sudo vim /var/log/mysql/error.log您会看到Changed limits: max_connections: 214 (requested 5000).
Solution:
Copy the limits for mysql from the systemd config file to /etc/systemdusing:
解决方案：
将 mysql 的限制从 systemd 配置文件复制到/etc/systemd使用：
sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service
Then add the following lines to the bottom of the file:
然后将以下行添加到文件底部：
LimitNOFILE=infinity
LimitMEMLOCK=infinity
You could also use a finite number instead of infinity.
您也可以使用有限数而不是无穷大。
Now reload systemd config:
现在重新加载 systemd 配置：
sudo systemctl daemon-reload
回答by Intrepid Web Studio
Thank you Very much i inserted the code into the MY.ini file and it solved the problem.
非常感谢我将代码插入到 MY.ini 文件中，它解决了问题。
in c:/xampp/mysql/bin/my.ini file under the [mysqld] section i inserted the following line
在 [mysqld] 部分下的 c:/xampp/mysql/bin/my.ini 文件中，我插入了以下行
**
**
max_connections = 250
最大连接数 = 250
** Later restart the xampp server to take effect.
**稍后重启xampp服务器生效。
if we give set-variable=max_connections=250 Mysql server is not starting.
如果我们给 set-variable=max_connections=250 Mysql 服务器没有启动。
Once again thank you very much.
再次非常感谢你。
回答by Yazan
you can set that in my.cnf, Mysql Doc
你可以在 my.cnf, Mysql Doc 中设置
I quote
我引用
You can increase this value in main config file (e.g., /etc/my.cnf or /etc/mysql/my.cnf) using this syntax:
[mysqld]
set-variable=max_connections=250
i think you need to restart mysql after changes to take effect.
我认为您需要在更改生效后重新启动 mysql。
回答by Curious Mind
I had the same problem using Ubuntu 16.04. Had to do it this way:
我在使用 Ubuntu 16.04 时遇到了同样的问题。不得不这样做：
sudo vim /etc/systemd/system/mysql.service
Added the lines at the bottom of the file:
在文件底部添加了以下几行：
LimitNOFILE=infinity
LimitMEMLOCK=infinity
Reloaded systemd config:
重新加载 systemd 配置：
sudo systemctl daemon-reload
Increased the number of files the system could open:
增加系统可以打开的文件数：
ulimit -n 4096
Edited my.cnf like this:
像这样编辑 my.cnf：
vi /etc/mysql/mysql.conf.d/mysqld.cnf
Looked for the max_connections and changed it′s value to 20000.
查找 max_connections 并将其值更改为 20000。
To make sure the settings become permanent performed a mysql service restart:
为确保设置永久执行，请重启 mysql 服务：
sudo service mysql restart
Went to phpmyadmin and checked the max_connections global variable using:
前往 phpmyadmin 并使用以下命令检查 max_connections 全局变量：
SHOW VARIABLES like '%max_connections';
It worked - Now when the daemon restarts I have the same max_connections that I had before.
它有效 - 现在当守护程序重新启动时，我拥有与以前相同的 max_connections。
