how safe is it to rsync databases?

[Tony at ServaCorp.com]

Caveat: Your Mileage may vary etc. etc.
Daily and more often than daily.

If you make just one backup, I'd have to advise against it.
If you make several (and can stand living "dangerously"), it can be very
effective.

Basic strategy for running a backup from a live server
1) mysqladmin ... flush-tables        #-- you do not want stuff hanging in
the buffers
2) rsync                   #-- 1st rsync  does almost all of the work
3) mysqladmin ... flush-tables        #-- we do it again
4) rsync                   #-- 2nd rsync    --- this one should go very fast

Your exposure to stuff moving is what can get written to disk from #3 to the
end of #4
Step #4 is much much faster (less exposure to stuff moving) than step#2.

(You could actually make it "safe" by locking tables before #3 and removing
the lock after #4)

This is probably much safer if you rsync the format (.fmt) and data (.MYD)
and rebuild the indexes (.MYI).
Me, I rsync everything and expect to get away with it (and almost always do)

Caveat: This is MyISAM tables on MySQL. Only.
Anything that allows readers and writers to not conflict probably requires
some special handling
which cannot be done by rsync itself.
If you view MySQL as a SQL-looking front-end to a file system, you should
survive nicely.
  -----Original Message-----
  From: rsync-bounces+tony=servacorp.com at lists.samba.org
[mailto:rsync-bounces+tony=servacorp.com at lists.samba.org]On Behalf Of Julian
Pace Ross
  Sent: Friday, April 14, 2006 9:35 AM
  To: rsync at lists.samba.org
  Subject: Re: how safe is it to rsync databases?


  I'm interested to hear feedback on this, since I was intending to backup a
mySQL database 'on the fly' daily...
  Anyone?



  On 09/04/06, Veronica Hill <veronica.hill at optusnet.com.au> wrote:
    Hi people!

           I've been hunting around the web for an answer to this question
for a
    couple of days now.  I run the IT for a small company and i don't have
    a lot of experience with the type of C-ISAM database application that
    is used by my company, I deal with the general IT issues and call in
    specialists when required.  But I need to backup this 10GB database to
    a disaster recovery server on the other side of the continent.  So my
    first reaction was that if the database was shutdown then rsync would
    be a great solution!  But some opinions on the net muddy the issue.

    Tridge's PHD thesis mentions that people use rsync for replicating
    their databases to backup servers.  i would have thought that that was
    OK if the DBM was shutdown during the backup......

    But some people on the net like this:
    http://www.sanitarium.net/golug/rsync_backups.html
    Say:
    Why/When wouldn't you want to use rsync for backups?

    Databases: Rsync is a file level backup so it is not suitable for
    databases. If your primary data is databases then you should look
    somewhere else. If you have databases but they are not your primary
    data then there is a procedure below to integrate a database backup
    into the rsync backups.

    What pointers can people give me?

    Thanks, Bye, Rony.

    --
    To unsubscribe or change options:
https://lists.samba.org/mailman/listinfo/rsync
    Before posting, read: http://www.catb.org/~esr/faqs/smart-questions.html



-------------- next part --------------
HTML attachment scrubbed and removed