[GRLUG] Backing up MySQL

[scott.tanner at comcast.net]

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. 

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

We use replication slaves heavily, backing up the DB and the replication logs just in case we need to roll-back a big mistake! Makes restoring easy too - just untar over the old directory and start mysql - no need to setup a blank DB and bring in user permissions. Michael, I can send you my backup script if your interested. 

Regards, 
Scott 


----- Original Message -----
From: "L. V. Lammert" <lvl at omnitec.net> 
To: "Mailing List for LUG in greater Grand Rapids, MI area." <grlug at grlug.org> 
Sent: Friday, September 23, 2011 5:39:42 PM 
Subject: Re: [GRLUG] Backing up MySQL 

At 04:35 PM 9/23/2011, you wrote: 

>My (vague, unsophisticated) understanding is that mysqlhotcopy makes a 
>copy of the raw data files. I'll note that I don't know whether or not 
>it even works with InnoDB. 

That might be true, .. but the docs mention "files", which would seem 
to indicate ISAM. In this case, the engine is InnoDB. 

>mysqldump, on the other hand, outputs valid SQL which recreates the database. 

Yes, .. but at least it gets the entire database (ISAM *OR* InnoDB). 

>BTW, if you think mysqldump is running slow, wait until you try 
>restoring that dump. At 33 hours or so for a backup, it might take you 
>more than a week to restore, depending on the options you gave 
>mysqldump for building the output. 

No kidding! 

Lee 


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

_______________________________________________ 
grlug mailing list 
grlug at grlug.org 
http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug 

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://shinobu.grlug.org/pipermail/grlug/attachments/20110923/a54e350b/attachment-0001.html>