Locking problem when using mysqldump on large databases

Mysqldump will lock all the tables (in all the databases) as required by default, this will use up a lot of file descriptors and can run out.

You will get the following error message when trying to dump your MySQL database(s) using the mysqldump utility with many databases / tables.

mysqldump: Got error: 1016: Can't open file: './some_database/some_table.frm' (errno: 24) when using LOCK TABLES

To resolve this MySQL's open_files_limit setting can be increased or use mysqldump's --skip-lock-table to not lock the tables in the first place (if no other processes are writing to the databases while the dump is performed).

The SQL query below will show your server's current open files setting:

mysql> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

To increase the setting, add the following to your MySQL config file /etc/my.cnf:

# The MySQL server
[mysqld]
open_files_limit = 4096

After restarting the MySQL server the new setting should take effect.

Last updated: 14/08/2010