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