how safe is it to rsync databases?

[Julian Pace Ross]

Thanks for the insight and ideas... I feel a bit safer.
I'll update with any feedback...


On 14/04/06, Tony at servacorp.com <Tony at servacorp.com> wrote:
>
>  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