I found this looking up the feasibility of backing up mySQL while it's running...
Does Zimbra use myISAM or InnoDB?
RE: rsync and mysql with users connected
Tony Abernethy
Fri, 23 Jun 2006 10:47:01 -0700
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.  |