mysqldump errno: 24 Can’t open file when using LOCK TABLES
When backing up a MySQL database using mysqldump command, I got the following error.
root@server12:~# mysqldump --opt DB_NAME > DB_NAME.sql mysqldump: Got error: 1016: Can't open file: './DB_NAME/TABLE_NAME.frm' (errno: 24) when using LOCK TABLES root@server12:~#
The error is due to open_files_limit
mysql> show variables like 'open_files_limit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 2558 | +------------------+-------+ 1 row in set (0.00 sec) mysql>
You need to edit mysql configuration file and increase value of open_files_limit.
Another way to fix the error is to use –lock-tables=false option when taking MySQL backup.
Example
mysqldump --opt DB_NAME --lock-tables=false > DB_NAME.sql
If you are using systemd, you may need to edit the service file. Identify which service file you are using with command
systemctl list-unit-files | egrep 'mysql|mariadb'
Now edit the service file with
systemctl edit mysqld
In the above command replace mysqld with the name of your service. This can be mariadb or mysql depending on your OS. You will get an editor, in the editor, paste the following, save and exit the editor.
[Service] LimitNOFILE=infinity LimitMEMLOCK=infinity
Restart MySQL
systemctl restart mysqld
To change open_file_limit on MariaDB, see MariaDB Change Open Files Limit
Back to MySQL Backup