Jamie Le Souef

Today I’ve run into a bit of an issue with one of the MySQL databases from one of the sites that I manage. I’m used to going about my merry way with phpMyAdmin and it’s trusty (?) export table feature but that is no longer an option with this Database.

It’s a Wordpress Mu (Multi-user) site that has well over 5,000 MySQL Database tables in it. PhpMyAdmin just could not handle an export of that size and timed out on me over and over again.

It’s time for some command line action!

So I loged into my server via SSH and entered in the mysqldump command only to be greeted by

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

Great!

So after some searching I found that this is a bug in MySQL and to get around it and dump (not Drop!) a large database I needed to use the following in my mysqldump command : –lock-tables=false.

So my final MySQL command looked like this

# mysqldump --add-drop-table -h 127.0.0.1 -u MyUsername -pMyPassword MyDatabase --lock-tables=false | gzip > backup.bak.sql.gz

I hope this helps someone else.

If there is a problem with the way I’m going about this please let me know of a better solution!

This entry was posted on Monday, September 15th, 2008 at 11:02 am and is filed under Linux. You can follow any responses to this entry through the RSS 2.0 feed.You can leave a response, or trackback from your own site.

There are 10 Responses to “ Tip : Backing up a large MySQL Database (errno: 24) ”

1

Jamie Le Souef Says

For some reason I can not get this post to include the > symbal.. it’s just wants to write the html & gt; (minus the space between the & and g)

So it’s supposed to be a >

:)

September 19th, 2008 at 2:26 pm

2

Adam Says

Thanks, That was the exact issue I was facing also. I’m now able to back up my mySQL database of ~2000 tables.

November 5th, 2008 at 3:06 am

3

Jamie Le Souef Says

@Adam -
I’m glad the post helped you out mate. It was a life saver to find that solution!

November 5th, 2008 at 9:56 am

4

Jorge Says

Thank you very much!!!!

I kept finding the tip of changing the open-files-limit parameter but that didn’t work with me.

With this option you saved my database :)

March 24th, 2009 at 9:42 pm

5

Jamie Le Souef Says

Glad it helped Jorge.

March 25th, 2009 at 4:27 pm

6

Manoj Says

Hi,
thank you for putting light on this issue.
could you please tell me how to overcome this issue during select .
I am getting this error frequently during select .
I am using MySql 5.2 with MyISAM

April 13th, 2009 at 9:53 pm

7

Jamie Le Souef Says

Hi Manoj,

I’m not sure how to fix that one I’m sorry. I’ll keep an eye out for it tho!

April 15th, 2009 at 5:33 pm

9

Phil Says

Hello,
I ran into this problem too and setting lock-tables to false is one way but it does not explain what is really causing the problem.

This particular error mostly occurs when it reaches the open file limit set by the system and by the mysql instance.

So if you want to fix this issue you must increase the system limit in /etc/security/limits.conf & in your mysql my.cnf with the variable
open-files-limit = 65536

65536 is the actual maximum number you can use so you can lower this number to fit your needs. Usually (if I’m correct) you should set this to a number that is a bit more then twice the number of tables in the db that is causing the problem.

Hope this helps

April 21st, 2010 at 4:33 am

10

Jamie Le Souef Says

Awesome comment Phil! Thanks for that. I’ll be sure to give it a try

May 4th, 2010 at 8:28 pm

Leave a Reply