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:

mysqldump: Got error: 23: "Out of resources when opening file './xxxx/xxxx' (Errcode: 24)" when using LOCK TABLE

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:

ls /var/lib/mysql/dbname/ -l|wc -l

The result is 8350 files.

Then checked the limit by executing this in phpmyadmin:

SHOW VARIABLES LIKE 'open%'

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

[mysqld]
open_files_limit = 10000

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

LimitNOFILE=10000
LimitMEMLOCK=10000

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

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

mysqldump -u username -p dbname | gzip > ./dbexport.sql.gz