rsync and mysql with users connected

[JamesDR]

Tony Abernethy wrote:
> With the usual caveats of "Your Mileage May Vary", this may give you an 
> idea of how far you can push things and get away with it.
> In practice, I back up production MySQL databases when they are running. 
> Not that big a deal.
>  
> Some notes on improving the odds (of getting away with it):
> One logical rsync is two rsyncs back-to-back.
> The first one to "do all the work"
> The second one to catch anything that changed during the first.
> This still leaves a window of expsure, but it is fairly small.
>  
> If the rsync is over internet (or WAN) links,
> a local rsync to a staging area and the long slow rsync from something 
> "stable".
>  
> This is with straight MyISAM tables. I would expect trouble from 
> anything with "transactional integrity".
> With MyISAM, things are simple enough so that even if you catch 
> somehting in mid-something, its actually pretty hard to trash the table. 
> That's hard, not impossible. Actually with staged backups, it's pretty 
> well impossible to actually do yourself in.
> The only time I've gotten bit (that I'm aware of) is the .MYD and the 
> .MYI out of sync and adding a new record and getting bit because the 
> autoincrement record already existed. Actually more funny than serious.
>  
> If you have only the primary and one backup (just one backup seems a 
> recipe for disaster, anyway), you will need to stop the database and do 
> the rsync. (And what do you do when the primary fails DURING the rsync?)
>  
> Backing up from Windows, my guess is that you will have better luck 
> doing an smb mount and doing the rsync from something unixy to something 
> unixy. With windows, a file opened normally will have EVERYTHING ELSE 
> locked out of doing anything with that file. With things unixy, it is 
> considered kosher to delete a file while someone else is writing it, the 
> standard sequence of an update is to ./configure; make; make install. 
> Then to shut down the old running program and bring up the new program. 
> The smb mount and unix rsync will NOT overcome the Windows semantics, 
> but should at least do as much as can be done. Windows likes to go part 
> way in a random order and the die at the first sign of trouble.
>  
> 
>     -----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 *Grignon Mickaël
>     *Sent:* Friday, June 23, 2006 11:02 AM
>     *To:* rsync
>     *Subject:* rsync and mysql with users connected
> 
>     Hi,
>      
>     I want to apologise for my bad english (I'm french).
>      
>     So I need to Back-up a lot of datas from a place to an another by
>     the way of rsync with ssh naturally.
>      
>     A good question from my boss was :
>     What happened when the mysql database is used by a user during the
>     Backup ?
>     Is the system stop the Backup or is it transparency for rsync ?
>      
>     Sorry if my question is stupid but I haven't already finish the
>     reading of all the documents treating rsync.
>      
>     Thanks in advance to your answers and comprehension.
>      
>     Oh yes, I forgot an important fact : The Backup is done between SUSE
>     10.0 and Mandrake 10.0 so Linux envirronement. Some datas froms
>     Windows will be Backuped too. I think we will use a ntfs partition.
>     Some advises about that or all is OK and though that don't cause any
>     problems ?
>      
>     Mickaël.
> 

I see no one has considered dumping to a file and backing up that...
We do that here because we use innodb heavily. We dump using the 
extended insert option (see the mysql manual for more info) then rsync 
this file. We first sort the records, dump then rsync. Has been working 
well for years.

-- 
Thanks,
James