rsync and mysql with users connected

[Tony Abernethy]

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.
-------------- next part --------------
HTML attachment scrubbed and removed