无法在 Ubuntu 15 中为 Mysql 最大连接增加 max_open_files

Can not increase max_open_files for Mysql max-connections in Ubuntu 15(无法在 Ubuntu 15 中为 Mysql 最大连接增加 max_open_files)

问题描述

我正在运行这个版本的 Mysql

I am running this version of Mysql

Ver 14.14 Distrib 5.6.24, for debian-linux-gnu (x86_64)

在这个版本的 Ubuntu 上

On this version of Ubuntu

Distributor ID: Ubuntu
Description:    Ubuntu 15.04
Release:    15.04
Codename:   vivid

这是我为Mysql设置的配置:

This is the config I set for Mysql:

key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
innodb_buffer_pool_size=20G
innodb_log_buffer_size=16M
tmp_table_size = 32M
max_heap_table_size = 32M
open-files-limit=4510
max_connections=500
myisam-recover         = BACKUP
query_cache_type=1
query_cache_limit   = 32M
query_cache_size        = 32M

这些是我在启动 MYSQL 时不断收到的警告:

These are the warnings I keep getting when starting MYSQL:

2015-06-17 17:28:53 26720 [Warning] Buffered warning: Could not increase number 
of max_open_files to more than 1024 (request: 4510)

2015-06-17 17:28:53 26720 [Warning] Buffered warning: Changed limits: max_connections: 
214 (requested 500)

2015-06-17 17:28:53 26720 [Warning] Buffered warning: Changed limits: table_open_cache: 
400 (requested 2000)

我已经尝试过这些步骤:

I already tried these steps:

1) 将此添加到 /etc/security/limits.conf

mysql           soft    nofile          65535
mysql           hard    no file          65535

2) 将此添加到 /etc/pam.d/common-auth 和 /etc/pam.d/commom-session

2) Adding this to /etc/pam.d/common-auth and /etc/pam.d/commom-session

session required pam_limits.so

3) 将此添加到 /etc/mysql/mysql.conf.d/mysqld.cnf

open-files-limit=4510 or open_files_limit=4510

这些都没有奏效，我仍然无法将 mysql 最大连接数提高到 500.

None of these have worked and I am still not able to raise the mysql max connections to 500.

此时我真的很感谢您的帮助.

I'd really appreciate some help at this point.

非常感谢.

推荐答案

Ubuntu 在 15.04 版本中已从 Upstart 迁移到 Systemd，并且不再遵守/etc/security/limits.conf 中对系统服务的限制.这些限制现在仅适用于用户会话.

Ubuntu has moved from Upstart to Systemd in version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.

MySQL 服务的限制在 Systemd 配置文件中定义，您应该将其从其默认位置复制到/etc/systemd 中，然后编辑副本.

The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.

sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice

在文件底部添加以下几行:

Add the following lines to the bottom of the file:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

您还可以设置数字限制，例如 LimitNOFILE=4510.

You could also set a numeric limit, eg LimitNOFILE=4510.

现在重新加载 Systemd 配置:

Now reload the Systemd configuration with:

sudo systemctl daemon-reload

重启 MySQL，它现在应该遵守 max_connections 指令.

Restart MySQL and it should now obey the max_connections directive.

在升级到 15.04 后，我也遇到了完全停止 MySQL 的问题.如果这对您有影响(您会知道，因为执行 service mysql stop 或 service mysql restart 时需要 300 秒才能超时)，然后将以下行添加到相同的/etc/systemd/system/mysql.service 文件为我修复了它:

I also had problems stopping MySQL cleanly after upgrading to 15.04. If this affects you (you'll know because it will take 300 seconds to time out when you do service mysql stop or service mysql restart) then adding the following line to the same /etc/systemd/system/mysql.service file fixed it for me:

ExecStop=/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown

后一个问题似乎已在 16.04 中修复并且不再需要此行，因此在您进行发行版升级之前，您需要停止 MySQL 并从配置中删除 ExecStop 行文件.

This latter problem seems to have been fixed by 16.04 and this line is no longer required, so before you do a distribution upgrade you'll want to stop MySQL and remove the ExecStop line from the config file.

这篇关于无法在 Ubuntu 15 中为 Mysql 最大连接增加 max_open_files的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！