How to deal with mysqldump error 23: out of resources when opening file

So earlier today I was doing a mysql dump of a large database. And I got this error:

A quick google reveals that it’s because the number of files that MySQL is permitted to open has been exceeded.

So I counted how many files our database has:

The result is 8350 files.

Then checked the limit by executing this in phpmyadmin:

It gives me a result of 1024, so I opened /etc/my.cnf and added

Unfortunately this didn’t do the job!

Some further digging landed me on this stackexchange post: http://dba.stackexchange.com/questions/86987/mysql-open-files-limit-cannot-change-this-variable

Looks like the issue is systemd related.

Edit /usr/lib/systemd/system/mysqld.service  and add

Then run systemctl daemon-reload and  systemctl restart mysql.service .

Now with all that sorted, finally, the real deal:

 

Leave a Reply

Your email address will not be published. Required fields are marked *