Backp fails with mysql "too many files open" error

Hi I hope you can please help. I've been struggling to get a Full backup of my server recently. I keep getting errors such as this:

SQL <tt>select user.user,user.authentication_string from user,db where db.user &#61; user.user and (db.db &#61; &#39;data&#39; or db.db &#61; &#39;data&#39;)</tt> failed : <tt>Out of resources when opening file &#39;./mysql/user.MYD&#39; (Errcode: 24 - Too many open files)</tt> at ../web-lib-funcs.pl line 142

Any help appreciated to get this fired up again and working. :)

Status: 
Active

Comments

Howdy -- do you have the file "/lib/systemd/system/mysql.service" on your server?

If so, can you paste in it's contents?

Hi thanks for the reply. Here are the file contents:

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

Much appreciated for the help :)

Okay, here is what I'd try.

Somewhere within the "Service" section there, add the following two lines:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

Then, restart the MySQL service, and see if things work properly after that.

I got that info from this bug report here, where some folks were seeing similar issues are you are:

https://bugs.launchpad.net/ubuntu/+source/mysql-5.6/+bug/1434758

ok thanks I'll give it a try and let you know in a few days if the backup finishes. Thank you!

Hi just to report back that this now seems to have fixed the problem for me. The backup is now completing successfully. Thanks again!

That's fantastic, thanks for the heads up!