Mysqldump Lock Table error while running script

debianMySQL

I have written a shell script to backup mysql all databases. The script works without any issues except this error "mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES"

Please help with this problem.

Thanks in advance.

Best Answer

Errors like this usually pop up on large databases when your filesystem has reached it's open files limit. Usually MySQL adopts this value from your OS, so it depends how much it is. You can check it with the query: SHOW VARIABLES LIKE 'open_files_limit', which could return something like this:

mysql> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+

Depending on the storage engine you use, a single table can use up to 2 or 3 files on your disk (like .FRM, .MYI and .MYD) so if your db has more than 512 or 341 tables, it chokes.

Now since you tagged your question with debian, I assume this is on a debian server. You can raise the limit with this command:

ulimit -n XXXX

Where XXXX is the new limit, like 2048 (don't go too extreme!). If this doesn't work or isn't saved permanently, you may need to edit the /etc/security/limits.conf file.

Also see this page and this one for references to this issue.

Related Topic