backing up a MySQL database

[thisisit3]

I've been looking for a reliable way to backup my databases (MySQL). So far i haven't found a suitable solution that works on really big databases (>1GB).

Most solutions dump the entire database (mysqldump, mysqlhotcopy, etc) into a file, which is not ideal for really big databases. In some cases you may end up taking up your entire hosting space (eg, you have 10gb of hosting space with a 6gb sql database, its rather obvious that you can't dump it on the remaining 4gb unless you compress it on the fly, which still isn't a good approach).

So i'm asking if anyone knows of a good way to backup big databases in an efficient way. Maybe someone from Jag can offer some advice?

Thank you.

[secondv]

This may or may not work for you, http://www.ozerov.de/bigdump.php , it's "staggered" backup - for large db's.. a PHP script.

[Ron]

I do compress my backups on the fly as they are full insert statement backups, but they still wind up taking about 1/5 to 1/4 of the space of the original. In fact it is rather efficiently done with a pipe taking advantage of dual CPUs
time mysqldump -u usaname -ppasswerd -a --all-databases |gzip -9 >backupfilename.sql.gz
250mb db into 60mb .gz in about 1 minute 30 seconds.

[Ron]

On your VPS you could always just quiesce the db and copy/compress the ISAM files

[thisisit3]

Some of my databases are well over 1GB in size and mysqldump takes a rather long time to complete. I also did pipe compression but that didn't help much (150MB per 1GB compression).

Copying the /var/lib/mysql files is ok if you FLUSH the database first, but that only works for MyISAM tables and not InnoDB. Since i'm running mixture of both this solution is not for me.

The above posted script isn't very efficient to run under Bacula, so currently my only option is to execute mysqldump manually.

The best method is to use the Binary Log, which can do incremental backups. Unfortunately its rather complex so i've been avoiding it

[Ron]

You're only saving 150MB on a 1GB database using gzip -9? Are you storing binaries into MySQL, or is the innodb format already compressed?

[Ron]

There are all sorts of things you can do to go to the next step -- I wouldn't recommend a log based incremental approach first unless they have gotten a TON more reliable and less complicated than they were 7 or 10 years ago.

What is the level of effort acceptable for this... is it a time-sensitive mission-critical app, or just "I wanna back it up every week" kind of thing?

[Gwaihir]

MySQL's binairy log is probably the way to go. It was specifically made for this problem. I guess it's just a price to pay (in effort / skill level needed) for going to an "enterprise" size DB.

[Ron]

I don't understand the recommendation to go with the log approach. I back up 250MB with full creates and full insert statements compressed to a 4.something:1 ratio in well under 2 minutes, and I restore it (after decompression) in about the same amount of time, on my PC with a SATA 150 disk. When I loaded a million rows into my database on the server it took less than a minute.

Without a strong need for it, I don't understand why someone would go with a much more complicated approach, especially on the restore where every file and the entrire process has to be perfect to accommodate an incremental-based restore. You're still going to need periodic complete backups unless you want to multiply your risk by, say 300 dumps. There may be issues about incomplete transactions, file corruptions and who knows what else. I think that the complexity itself makes this solution less valuable.

All of this is why I asked what the level of effort required was.

Maybe all of my experience is dated on this. That's certainly possible.

Happy monthend! I did a month end rollover on my local copies of my sites last night. It now takes 50 minutes to copy my site disk to disk on separate spinldes and controllers. Sigh.

[lamnk]

Try backup service ? I recall some 10gb rsync backup offers on WHT for about $3-5 per month. Or use Amazon S3 ?

[Gwaihir]

I don't understand the recommendation to go with the log approach. I back up 250MB with full creates and full insert statements compressed to a 4.something:1 ratio in well under 2 minutes, and I restore it (after decompression) in about the same amount of time, on my PC with a SATA 150 disk. When I loaded a million rows into my database on the server it took less than a minute.

Well yes, but his story seems to be rather different already, squeezing to 850MB which isn't small enough to his liking. I wouldn't easily step over to a binary log based approach, I'd probably just get more diskspace for my account, even if it means keeping a few gigs free for it.

However, I got the impression he wants to know what will be next, once the 'mysqldump style' way of working does run out. IMHO then the binary log method is the sensible 'generic' step. Not overly much point trying to find yet more tricky middle ways, unless you're talking about one specific application you maintain yourself and can build the tricks into.

There are all sorts of things you can do to go to the next step -- I wouldn't recommend a log based incremental approach first unless they have gotten a TON more reliable and less complicated than they were 7 or 10 years ago.

Yes, that's a completely different ballgame now. 7 or 10 years ago on MySQL? WOW! I'm not sure what predecessor you're refering to then, as the binary log was introduced in MySQL 4.1.3 and you probably want a 5.x for it because of improvements since introduction.

[Ron]

You're probably right.

Not MySQL specifically, just log-based restores in general. I've worked with them in Oracle and with Sybase and with..., I dunno, rdb maybe postgres or ingres or whatever. I had not met one that was a robust solution (i.e. some aspect didn't fail frequently)

Again, a full backup will be required peridoically anyway. I think incrementals were used mainly for intra-day restoration; that is after a regular full nightly backup, transactions performed later that day were ummmm available through the log.

If this is serious stuff, perhaps a mirrored approach would be good. Perhaps a nightly breaking of the mirror for backup could be done, so that the mirror disk(s) can get slammed for backup while the primary disk can continue on.

Anyway, it could be a whole forum devoted to backup strategies and tools.

[Gwaihir]

If this is serious stuff, perhaps a mirrored approach would be good. Perhaps a nightly breaking of the mirror for backup could be done, so that the mirror disk(s) can get slammed for backup while the primary disk can continue on.

You raise another good point there: the backing up of such large databases tends to throw the load too high / block tables for too long for the production machine. That makes that a very good strategy. With a master-slave setup the relation doesn't even have to be actually 'broken'. The slave just starts lagging behind for a while when it is slammed with the backup process. IIRC someone person posted here a while back about getting a VPS specifically to run such a slave-for-backup.

Doesn't change the space need though; you'd still need space (in the VPS account then) to make the compressed full backup of the slave database. But the same goes for any backing up of accounts; I suppose a larger account / server simply shouldn't be filled with production stuffs for more than 2/3.

[Ron]

The idea Wim is that the backup is a point in time, that doesn't work well with an active slave.

One of the things a backup protects against is soft corruption -- the app screws up the db or a developer drops a table... ;D

[Vin DSL]

I've been looking for a reliable way to backup my databases (MySQL). So far i haven't found a suitable solution that works on really big databases (>1GB).

So i'm asking if anyone knows of a good way to backup big databases in an efficient way. Maybe someone from Jag can offer some advice?

Thank you.

Personally, I use phpMyAdmin (my own install, not the cPanel version)...

There are zillions of ways to dump your dbs using PMA!

Perhaps you should try backing-up each table individually