Backp fails with mysql "too many files open" error [#44109]

Submitted by soydemadrid on Fri, 11/18/2016 - 02:43 Pro Licensee

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

Submitted by andreychek on Fri, 11/18/2016 - 09:19 Comment #1

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

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

Submitted by soydemadrid on Fri, 11/18/2016 - 09:24 Pro Licensee Comment #2

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 :)

Submitted by andreychek on Fri, 11/18/2016 - 09:44 Comment #3

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

Submitted by soydemadrid on Fri, 11/18/2016 - 09:48 Pro Licensee Comment #4

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

Submitted by soydemadrid on Sat, 11/26/2016 - 13:25 Pro Licensee Comment #5

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

Submitted by andreychek on Sat, 11/26/2016 - 15:05 Comment #6

That's fantastic, thanks for the heads up!