A couple of my items. Click to view more. I think you need something like this
 

 

10

2008-09-15

Tip : Backing up a large MySQL Database (errno: 24)

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 >

:)


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.


3

Jamie Le Souef Says

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


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 :)


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


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!


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


10

Jamie Le Souef Says

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


Leave a Reply

 

recent projects

My Boy (1)

Title : My Boy (1)
Media: Canon EOS 350D DSLR, Adobe Lightroom, Photoshop
Description : A day out in the park with my world.

Tasmania Corrective Services Badge

Title : Tasmania Corrective Services Badge
Media: Adobe Illustrator, Stitch
Description : Badge for the Canine Drug Detection Unit

 

Webb Martin Consulting Website Concept 1

Title : Webb Martin Consulting Website Concept 1
Media: Blog/Website, flash, css, xhtml, php
Description : Website deisgn for Webb Martin Consulting

Webb Martin Website Design Concept

Title : Webb Martin Website Design Concept
Media: Wordpress, CSS, xhtml, php
Description : Concept website design for Webb Martin.

 

 

site tags

 

meta