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

 

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

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 >

:)


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!


Track & Ping backs

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

About Jamie Le Souëf

Jamie Le Souef

I'm a 27 year old Freelance Front and Back end designer /developer from Melbourne, Australia. I'll put more about me in here once i get my about page done

Ajax CommentLuv Enabled 67885f39b533899c64b408034951e375

syndication & misc