MySQL 缓冲警告：更改限制：max_connections：214（请求 800）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32760202/
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
Buffered warning: Changed limits: max_connections: 214 (requested 800)
提问by Valerio Paoletti
Everytime I restart MySQL I have this warning:
每次我重新启动 MySQL 时，我都会收到以下警告：
[Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)
[警告] 缓冲警告：更改限制：max_connections：214（请求 800）
So I have to change max_connectionsvariable:
所以我必须改变max_connections变量：
set global max_connections = 800;
But /etc/my.cfhas max_connectionsset:
但/etc/my.cf已经max_connections设置：
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections = 800
I also modified mysqld.service:
我还修改了mysqld.service：
# Start main service
ExecStart=/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
I've also updated max open files of Centos from 1024 to 2000:
我还将 Centos 的最大打开文件从 1024 更新到 2000：
ulimit -Sa | grep "open files"
open files (-n) 2000
But the problem persists.
但问题仍然存在。
Mysql log start:
mysql日志启动：
150924 13:15:04 mysqld_safe mysqld from pid file
/var/run/mysqld/mysqld.pid ended
150924 13:15:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-09-24 13:15:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-09-24 13:15:04 0 [Note] /usr/sbin/mysqld (mysqld 5.6.25) starting as process 29997 ...
2015-09-24 13:15:04 29997 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
2015-09-24 13:15:04 29997 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)
2015-09-24 13:15:04 29997 [Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 2000)
2015-09-24 13:15:04 29997 [Note] Plugin 'FEDERATED' is disabled.
2015-09-24 13:15:04 29997 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-24 13:15:04 29997 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-24 13:15:04 29997 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-24 13:15:04 29997 [Note] InnoDB: Memory barrier is not used
2015-09-24 13:15:04 29997 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-09-24 13:15:04 29997 [Note] InnoDB: Using Linux native AIO
2015-09-24 13:15:04 29997 [Note] InnoDB: Using CPU crc32 instructions
2015-09-24 13:15:04 29997 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-09-24 13:15:04 29997 [Note] InnoDB: Completed initialization of buffer pool
2015-09-24 13:15:04 29997 [Note] InnoDB: Highest supported file format is Barracuda.
2015-09-24 13:15:04 29997 [Note] InnoDB: 128 rollback segment(s) are active.
2015-09-24 13:15:04 29997 [Note] InnoDB: Waiting for purge to start
2015-09-24 13:15:04 29997 [Note] InnoDB: 5.6.25 started; log sequence number 24337684937
2015-09-24 13:15:04 29997 [Note] Server hostname (bind-address): '*'; port: 3306
2015-09-24 13:15:04 29997 [Note] IPv6 is available.
2015-09-24 13:15:04 29997 [Note] - '::' resolves to '::';
2015-09-24 13:15:04 29997 [Note] Server socket created on IP: '::'.
2015-09-24 13:15:04 29997 [Note] Event Scheduler: Loaded 0 events
2015-09-24 13:15:04 29997 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
I tried all Google solutions, but the result is always the same...
我尝试了所有谷歌解决方案，但结果总是一样......
MySQL is running on CentOS Linux release 7.1.1503 (Core).
MySQL 在 CentOS Linux 版本 7.1.1503（核心）上运行。
Thanks!
谢谢！
回答by MNB
Having just spent an hour or two facing the exact same problem on CentOS 7 with MySQL 5.6.26, here's my solution. In addition to raising the max open files (for the mysql user), which it looks like you've already done, you need to add "LimitNOFILE=65535" (or similar depending on how high a limit you want to set) to your mysql.service definition file.
刚刚花了一两个小时在带有 MySQL 5.6.26 的 CentOS 7 上面临完全相同的问题，这是我的解决方案。除了提高最大打开文件（对于 mysql 用户），这看起来你已经完成了，你需要添加“LimitNOFILE=65535”（或类似的，取决于你想设置的限制有多高）到你的mysql.service 定义文件。
So the complete solution for me was as follows
所以对我来说完整的解决方案如下
append these two lines to /etc/security/limits.conf
将这两行附加到 /etc/security/limits.conf
mysql hard nofile 65535
mysql soft nofile 65535
append this line to /usr/lib/systemd/system/mysqld.service (in the [service] section)
将此行附加到 /usr/lib/systemd/system/mysqld.service （在 [service] 部分）
LimitNOFILE=65535
then finally reboot and check that those error messages have disappeared from your mysql error log.
然后最后重新启动并检查这些错误消息是否已从您的 mysql 错误日志中消失。
EDIT: Thanks to @SieGeL belowfor reminding me to add, if you update the ".service" file directly your edits will be lost on upgrade. To keep edits after an upgrade use a systemd override by creating an additional conf file in /etc/systemd/system/mysqld.service.d
编辑：感谢下面的@SieGeL提醒我添加，如果您直接更新“.service”文件，您的编辑将在升级时丢失。要在升级后保留编辑，请通过在 /etc/systemd/system/mysqld.service.d 中创建额外的 conf 文件来使用 systemd 覆盖
回答by SieGeL
I would not recommend to edit the original systemd file as it will be overwritten during updates.
我不建议编辑原始 systemd 文件，因为它会在更新过程中被覆盖。
To modify the limits, do the following:
要修改限制，请执行以下操作：
mkdir /etc/systemd/system/mysqld.service.d
Inside that directory, create new file limits.conf and add the following to that file:
在该目录中，创建新文件 limits.conf 并将以下内容添加到该文件中：
[Service]
LimitNOFILE = 65535
finally reload systemd with:
最后重新加载 systemd：
systemctl daemon-reload
and restart mysqld to enable the change:
并重新启动 mysqld 以启用更改：
systemctl restart mysqld
Now validate that the change was successful by using the following query:
现在使用以下查询验证更改是否成功：
mysql> show variables like '%file%';
You should find a line like this:
你应该找到这样的一行：
| open_files_limit | 65535
That's it, this way your changes survive MySQL updates.
就是这样，这样您的更改在 MySQL 更新后仍然有效。
