Buffered warning: Changed limits: max_connections: 214 (requested 800)

I would not recommend to edit the original systemd file as it will be overwritten during updates.

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:

[Service]
  LimitNOFILE = 65535

finally reload systemd with:

systemctl daemon-reload

and restart mysqld to enable the change:

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.


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.

So the complete solution for me was as follows

append these two lines to /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)

LimitNOFILE=65535

then finally reboot and check that those error messages have disappeared from your mysql error log.

EDIT: Thanks to @SieGeL below for 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

Tags:

Mysql

Centos7