0

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.

3 Answers 3

1

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.

1
  • Hmm, just noticed this is a 2 year old question, no idea why it was on the front page. Hope this still helps other people, though.
    – Oldskool
    Oct 17, 2012 at 8:42
0

Do you back it up with mysqldump -A? You dont happen to have an old drupal installation script in mysql data directory?

2
  • with this option (--all-databases) Oct 15, 2010 at 6:58
  • ls -l mysqls data dir please
    – 3molo
    Oct 18, 2010 at 8:10
0

Try the -f option with mysqldump

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .