From: Reindl Harald
Date: November 1 2012 4:00pm
Subject: Re: Mysql backup for large databases
List-Archive: http://lists.mysql.com/mysql/228569
Message-Id: <50929C82.8020607@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigB017C1C41657747059CA42C6"

--------------enigB017C1C41657747059CA42C6
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

as said:

use a replication slave dedicated for backups
you can even let a slave write a binlog and
sync another slave with this one

* rsync backups working with diff
* they are extremly fast after the first time
* a dedicated backup-slave has ZERO impact

i am doing rsync-backups of 1.5 TB data over a WAN
link since years each day and the real traffic is
between 2 and 5 GB each day

Am 01.11.2012 16:53, schrieb machiel.richards@stripped:
> Well, the biggest problem we have to answer for the clients is the foll=
owing:
> 1. Backup method that doesn't take long and don't impact system
> 2. Restore needs to be done on a quick as possible way in order to mini=
mize downtime.
>=20
> The one client is running master - master replication with master serve=
r in usa, and slave in south africa. They need master backup to be done i=
n the states.
>=20
> Sent via my BlackBerry from Vodacom - let your email find you!
>=20
> -----Original Message-----
> From: Reindl Harald <h.reindl@stripped>
> Date: Thu, 01 Nov 2012 16:49:45=20
> To: mysql@stripped<mysql@stripped>
> Subject: Re: Mysql backup for large databases
>=20
> good luck
>=20
> i would call snapshots on a running system much more dumb
> than "innodb_flush_log_at_trx_commit =3D 2" on systems with
> 100% stable power instead waste IOPS on shared storages
>=20
> Am 01.11.2012 16:45, schrieb Singer Wang:
>> Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=3D0 o=
r 2 and etc, you should be fine. We have been
>> using the trio: flush tables with read lock, xfs_freeze, snapshot for =
months now without any issues. And we test
>> the backups (we load the backup into a staging once a day, and dev onc=
e a week)=20
>>
>> On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald <h.reindl@stripped=
 <mailto:h.reindl@stripped>> wrote:
>>
>>     > Why do you need downtime?
>>
>>     because mysqld has many buffers in memory and there
>>     is no atomic "flush buffers in daemon and freeze backend FS"
>>
>>     short ago there was a guy on this list which had to realize
>>     this the hard way with a corrupt slave taken from a snapshot
>>
>>     that's why i would ALWAYS do master/slave what means ONE time
>>     down (rsync; stop master; rsync; start master) for a small
>>     timewindow and after that you can stop the slave, take a
>>     100% consistent backup of it's whole datadir and start
>>     the slave again which will do all transactions from the
>>     binarylog happened in the meantime


--------------enigB017C1C41657747059CA42C6
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.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://www.enigmail.net/

iEYEARECAAYFAlCSnIIACgkQhmBjz394AnmdeACgg3U0wBIAGJSn1SYZp2stxl5g
8YwAnRdVlZkPbanHHEGv+HfwMwA0iNZc
=e+6y
-----END PGP SIGNATURE-----

--------------enigB017C1C41657747059CA42C6--