« MySQL Users Conference Winds Down | Main | DBA Code of Ethics »

April 30, 2006

Limits in mysqldump?

Coming away from the MySQL Users Conference I have a short list of questions and/or ideas to research over the next few weeks.

The first is a question posed by an individual after the session on backups. She had been told that mysqldump works good for small installations but once you get above a gigabyte of data it can't handle the dump or restore process.

I've been using mysqlhotcopy for a few years now, but before that was using mysqldump on a few fairly large databases. At the time we switched over to mysqlhotcopy I think our system was around 20 gigs of data, and although the backup/restore process took a good chunk of time, it never failed because of problems with table or database size.

But since it's been awhile I thought I'd do a rudimentary check using a few gigs of data. I grabbed the Sakila sample database and dumped a bunch of data into it, enough to grow it to just over 2 gigabytes (this is all on my MacBook Pro laptop).

At 2 gigs It takes 5m 16s to dump with mysqldump and 11m 49s to restore that database from the SQL statements in that dump file. So it's definitely doable when you get over a gig of data.

How far up can you go? I've used it successfully dumping in the 20-30 gig range. It takes awhile when you get up into these amounts of data but still does the job. It seems like when you get up in this range with MyISAM tables you start looking at mysqlhotcopy as a faster alternative.

Anyone have evidence against this (or in favor)?

Posted by mike at April 30, 2006 2:35 PM