Fixing mysqldump: Got error: 1016: Can’t open file & mysqldump: Got error: 23

When doing exports of large databases using mysqldump, it is common to get errors that are along the lines of:

mysqldump: Got error: 1016: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24) when using LOCK TABLES

or maybe

mysqldump: Got error: 23: Out of resources when opening file ‘./databasename/tablename.MYD’ (Errcode: 24) when using LOCK TABLES

Both come from the same cause and don’t worry – your database is not currupt..

The first action that mysqldump takes is to lock all of the tables so that the database cannot go out of sync from the beginning to the end of the export.  Of course, that means that your users can’t make changes while  doing the dump. And that’s just one more reason you need to be careful using mysqldump.

The solution to this problem is simple add –skip-lock-tables to your command line.

At that point the tables won’t be locked, and the export will run much faster. There is a slight downside in that people can use the table as it is being exported and you could potentially get an update written as you are exporting that table. But it is unlikely that you’ll hit any real world problems when doing this, especially with things like blog exports.