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