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.
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 |
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
|
1 |
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
|
1 |
systemctl list-unit-files | egrep 'mysql|mariadb' |
Now edit the service file with
|
1 |
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.
|
1 2 3 |
[Service] LimitNOFILE=infinity LimitMEMLOCK=infinity |
Restart MySQL
|
1 |
systemctl restart mysqld |
To change open_file_limit on MariaDB, see MariaDB Change Open Files Limit
Back to MySQL Backup