rsync - Jun 2006 - 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

Micka?l,

In general one should not backup databases while they are running.

the safest way to backup any database is to switch it off (usually called 
coldmode) and to run in your case rsync.

another way is th run the normal mysql backup commands to a local disk 
location and after the mysql backup is done rsync that to a remote location

Maarten

On Friday 23 June 2006 18:02, Grignon Micka?l wrote:
> 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.
-- 
Maarten Boot
Compuware Europe B.V.
tel: +31 20 3116390 (has changed recently)
The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose it
to anyone else. If you received it in error please notify us immediately and
then destroy it.

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@lists.samba.org
[mailto:rsync-bounces+tony=servacorp.com@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