From: Reindl Harald
Date: January 25 2011 2:44pm
Subject: Re: InnoDB and rsync
List-Archive: http://lists.mysql.com/mysql/224214
Message-Id: <4D3EE1BF.7080603@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigEA82419C3F86107259ABBB91"

--------------enigEA82419C3F86107259ABBB91
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 25.01.2011 15:00, schrieb Robinson, Eric:
>> your whole solution is crippled because why in the world are=20
>> you killing your salves and reinit them without any reason daily?
>=20
> There is a very good reason: it is the phenomenon of row drift. The
> master and slave can appear to be in good sync, but often it is not
> actually the case.=20

There is nothing drifting and nobody cares if the files on both servers
are binary identical, the data must be consistent and it is

binlog-format =3D ROW

> For this reason, most people agree that it is not
> safe to rely on the slave server as the source for your backups.=20

sorry but these people have no plan

> My solution efficiently corrects row drift and makes sure the slaves=20
> are 100% binary replicas of the slaves

jesus christ nobody cares if they are binary replica as long
as the data is consistent and ident

> I fail to see how this is "crippled."

It is crippled because you do not understand the sense of
replication if you reinit it every day

> See my comment above. (But also we cannot stop them as long as we want
> because the slaves are used for running reports.=20

so start another slave on the machine with his own socket
for backups, i have running on all dedicated backup-servers
two instances - one is useable r/w and the other one without
tcp is the replication-slave, every hour the salve is stopped
and datadir mirrored to the r/w-instance

> Using my approach, each slave is down for about 30 seconds.=20
> The masters are not brought down at all.

and if you running a clean solution the salves are never down

> but is there really no way to put InnoDB into a state where all=20
> changes have been flushed to disk and it is safe to rsync the directory=
?=20

no, it is a database and not designed for access from external software
as long as the database is running

> Is stopping the service really the only way?=20

yes, and not only for innodb
try to copy oracle, postgresql, ms-sql :-)

if you do not stop the service you can be sure that the backup is
not useable or missing data, even if there would exist a mode
sync all to disk nobody would officially support copy datafiles
while the service is running, even with myisam nobody will do that

> And even if I stop the service, is rsync totally
> safe with InnoDB?

why not?

the server is down and you copy the whole datadir
what can be unsafe there?


--------------enigEA82419C3F86107259ABBB91
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk0+4b8ACgkQhmBjz394AnlFMACgh1DxWP5EakZnc/de0/oWafi5
ZX0AnAktulwe1uh+9i702etZs5iKVL/O
=Bzqp
-----END PGP SIGNATURE-----

--------------enigEA82419C3F86107259ABBB91--