[GRLUG] Backing up MySQL

[Adam Tauno Williams]

On Fri, 2011-09-23 at 22:07 +0000, scott.tanner at comcast.net wrote:
> I've read in the high-performance mysql book  that dumping / loading
> CSV files are faster.   But in general, backing up at the file-system
> level is a lot quicker

Anything block-level [or "page" level] is going to be quicker.  This is
why commercial databases like Informix and DB2 offer their own
[excellent] page level backup tools;  these typically allow you to do
page/block level backups to a streaming device and still get some
respect for transactions and other issues.

XFS also has supported freeze/thaw for numerous years.

> If the DB resides on top of LVM, you can use the lv tools to make a
> snapshot and copy the files from there.  I can tar/gz a 40 GB DB in
> about two hours ( 5 x 15K RPM SCSI disks though).

+1  Linux LVM has supported snapshots for a very long time.  It works
and it's fast.  There shouldn't be any recet server deployed that isn't
using LVM.

> We use replication slaves heavily, backing up the DB and the
>  replication logs just in case we need to roll-back a big mistake! 

+1 remembering that "replication" is not "backup".



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.