[lug] mysqldump versus snapshot versus..

[steve at badcheese.com]

The only thing tricky about doing DB backups is getting a good, solid 
snapshot of the DB.  This generally means 'locking' the tables as you dump 
them.

The mysqldump command is what I use, however, locking the tables on a 
production machine is a no-no, so there are two ways around this.  If you 
have only one machine and this is a small operation, change the mysql 
table type to innodb (can be done with an ALTER TABLE command) - gives you 
larger tables, no locking issues, but you lose the ability to do a quick 
SELECT COUNT(*) operation.  The other way is to use replication to 
replicate the DB to a "read-only" machine where you can perform long 
operations on.  We do this where I work where locking the tables only 
pauses the replication and not production.  Good for data warehouse-type 
stuff too since those types of queries are usually long-running as well, 
however this requires a second machine.

Or, you can just use replication as your backup - this allows for 
"instantaneous" restores.  If your DB dies (HD failure or something), just 
bring the OS back up, copy the DB to the master, sync and your back in 
business again - also good for keeping any data in the DB since your 
'nightly' backups so you don't lose 1/2 day of data.

I think that if you use LVM snapshots, you'll also have to lock the tables 
before and unlock after the snapshot, but it may be so fast, that 
locking/unlocking may not be an issue for you.

- Steve

On Wed, 1 Aug 2007, dio2002 at indra.com wrote:

> Date: Wed, 1 Aug 2007 01:49:28 -0600 (MDT)
> From: dio2002 at indra.com
> Reply-To: "Boulder (Colorado) Linux Users Group -- General Mailing List"
>     <lug at lug.boulder.co.us>
> To: lug at lug.boulder.co.us
> Subject: [lug] mysqldump versus snapshot versus..
> 
> i'm trying to find the best method to backup my mysql databases.
>
> 1) mysqldump - it's quick and easy and works well on the restore side.
> 2) i've heard about lvm snapshots but have not read enough on them.
> 3) straight backup the mysql db directories in the filesystem.
> 4) other?
>
> i would think using technique number 3 really requires a shutdown of the
> db while copying the files to make consistent backups.  i've been using
> technique number 1 mysqldump withOUT halting the db and all examples i see
> online never halt the db either.  that leaves technique 2 lvm snapshots.
> yet i recall reading somewhere that although they're advertised as "live"
> (meaning you don't have to stop your db) that that is a bunch of crap and
> you actually should.
>
> so does anybody know the answer to the question, do you need to quiess
> (know i didn't spell that right :-)) your mysql db when doing a backup,
> using either msyqldump, snapshots or otherwise?
>
> anybody have a preferred bombproof method or cron script for automating
> mysql backups?
>
> thanks
>
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: lug.boulder.co.us port=6667 channel=#colug
>

-- 
EMAIL: (h) steve at badcheese.com  WEB: http://badcheese.com/~steve