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 -p MyDatabase --lock-tables=false | bzip2 -c > backup.bak.sql.bz2

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!

Related Posts

• September 19, 2008 -- Linux Tip : Sorry, sudo must be setuid root
• January 16, 2008 -- Installing Ubuntu Server LAMP, SSH, FTP, Webmin and phpMyAdmin for a newbie
• September 15, 2008 -- Speed up your site with some htaccess mojo
• August 22, 2008 -- Linux Security : Can you SSH as root?
• July 22, 2008 -- Updating MySQL Privilege Tables

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 3 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 >

Reply - Quote

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.

Reply - Quote

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!

Reply - Quote

November 5th, 2008 at 9:56 am

Leave a Reply