mysql - 缓冲警告 : Changed limits: max_connections: 214 (requested 800)

Question

每次我重新启动 MySQL 时都会收到以下警告:

[Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)

所以我必须更改 max_connections 变量:

set global max_connections = 800;

但是 /etc/my.cf 设置了 max_connections:

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections = 800

我也修改了mysqld.service:

# Start main service
ExecStart=/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf

我还将 Centos 的最大打开文件数从 1024 更新到 2000:

ulimit -Sa | grep "open files"
open files                      (-n) 2000

但问题依然存在。

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)

我尝试了所有 Google 解决方案，但结果总是一样...

MySQL 运行在 CentOS Linux 版本 7.1.1503(核心)上。

谢谢!

Answer 1

刚刚花了一两个小时在 CentOS 7 上使用 MySQL 5.6.26 遇到完全相同的问题，这是我的解决方案。除了提高最大打开文件(对于 mysql 用户)，看起来你已经完成了，你需要在你的mysql.service 定义文件。

所以对我来说完整的解决方案如下

将这两行添加到/etc/security/limits.conf

mysql hard nofile 65535
mysql soft nofile 65535

将此行附加到/usr/lib/systemd/system/mysqld.service(在 [service] 部分中)

LimitNOFILE=65535

然后最后重新启动并检查这些错误消息是否已从您的 mysql 错误日志中消失。

编辑:感谢@SieGeL below提醒我添加，如果您直接更新“.service”文件，您的编辑将在升级时丢失。要在升级后保留编辑，请通过在/etc/systemd/system/mysqld.service.d 中创建额外的 conf 文件来使用 systemd 覆盖