[Gllug] Tape backups

[C. Cooke]

On Tue, Feb 17, 2009 at 11:29:53AM +0000, John Edwards wrote:
> On Tue, Feb 17, 2009 at 05:06:29AM +0000, Jose Luis Martinez wrote:
> > On Mon, Feb 16, 2009 at 11:28 PM, Gordon Joly <gordon.joly at pobox.com> wrote:
> >> At 22:20 +0000 16/2/09, James Hawtin wrote:
> >>>
> >>>Do you make use the of lvm snapshot facility to minimise the ammount of time
> >>>your filesystem is unavailable?
> >>>
> >>> James
> >>
> >> And stop mysql?
> >>
> >> Gordo
> > 
> > Pardon my MySQL ignorance, is there no way to backup  the database
> > while it is live? (I have not done serious backup work for a while, I
> > have got the impression that Sybase and Oracle can be backed up on the
> > fly, am I mistaken?).
> 
> Yes, there is mysqldump. You can run it just before the backup
> is made.
> 
> I don't know what Gordo's problem with MySQL and backups is.
> I only guess I can make is that he thinks there is a problem
> with making the MySQL backup just before taking a snapshot
> of the filesystem using lvm.
> 
> Large or busy MySQL databases could take a long time to dump,
> and so the database backup would not match the filesystem. This
> could be a problem where the database acts records data about
> files, and so the two must match and be frozen/snapshotted/backup
> together.

There are ways around that problem. I can think of three off the top of
my head:

1) use the --single-transaction option on mysqldump. This performs the
dump in - you guessed it - a single transaction. You're guaranteed that
the dump will match the DB at the point in time it starts.

2) Use LVM: create a snapshot of the partition. Mount it, then either
backup the mysql data by file copies or start a mysqld on a nonstandard
port and take a dump from that. Either way, when the daemon is started
(in the snapshot or from an eventual copy of it), it'll just do a normal
started-without-clean-shutdown init step and clean up any partial
inserts/transactions. You'll get the state of every committed
transaction at the point you take the snapshot.

3) Buy the official mysql hot copy tool, which uses proprietary hooks to
perform a live hot copy.

-- 
d=(1 0 6 0 1 0 5 5 41 5 3 12 4 5 15 1 4 -2 5 5 0 5 4 24 3 5 27 1 3 -2 1 3 6)
a=0;while :;do ((v=(c=a)+3));((x=d[d[a]]-d[d[a+1]]));d[d[a]]=$x;((a=d[d[a]]\
<0?${d[a+2]}:v));case $a in -1)read d[d[c]];a=$v;;-2)echo ${d[d[c+1]]};a=$v\
;;0)exit;;esac;done 2>&- # Charles Cooke, Sysadmin.  
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 204 bytes
Desc: Digital signature
URL: <http://mailman.lug.org.uk/pipermail/gllug/attachments/20090217/638d74f2/attachment.pgp>
-------------- next part --------------
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug