From: Karen Abgarian Date: November 1 2012 6:44pm Subject: Re: Mysql backup for large databases List-Archive: http://lists.mysql.com/mysql/228571 Message-Id: <8244258C-ADBF-44BB-844F-CB9CFE96CBED@apple.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi,=20 For doing backups on the primary database, I know nothing better than = have your tables in InnoDB and use Innobackup (or MySQL Enterprise = backup). This, however, still has the possibility of hanging as it is = using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables. = One may want to script it to kill the backup if the wait exceeds some = threshold. The backup taken this way has incremental backups feature = which may reduce the impact. =20 For offloading the backups to a replica, there exist more options = because the replica can be frozen and/or shut down. For an InnoDB = database, it has to be shut down for taking a consistent backup. If it = is not, it will result in cute little inconsistencies unless a DBA is = one lucky guy and always wins playing roulette. =20 Combining the two, I like the idea of doing EM backup on a replica and = having all tables in InnoDB. =20 After a backup has been taken, it will eventually need to be restored = unless someone just likes taking them. For this reason, it will have = to be brought to the recovered system. Unless somebody knows in = advance when the database would need to be recovered (f.e. it is known = that a bad guy always corrupts it on Monday mornings), the backup will = need to be available for restore always. These considerations usually = imply things like shared filesystems between primary and replica, = rejecting backups for recoveries across datacenters and the like. =20 Backing up binary logs allows providing continuous coverage for recovery = instead of discrete. =20 Cheers Karen=20 On 01.11.2012, at 8:53, machiel.richards@stripped wrote: > Well, the biggest problem we have to answer for the clients is the = following: > 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 = minimize downtime. >=20 > The one client is running master - master replication with master = server in usa, and slave in south africa. They need master backup to be = done in the states. >=20 >=20 > Sent via my BlackBerry from Vodacom - let your email find you! >=20 > -----Original Message----- > From: Reindl Harald > Date: Thu, 01 Nov 2012 16:49:45=20 > To: 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 = or 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 = once a week)=20 >>=20 >> On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald = > wrote: >>=20 >>> Why do you need downtime? >>=20 >> because mysqld has many buffers in memory and there >> is no atomic "flush buffers in daemon and freeze backend FS" >>=20 >> 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 >>=20 >> 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 >=20 >=20 > = =E2=80=B9=C2=A2=C3=92=C3=92=E2=80=B9=C2=A4=E2=97=8A=EF=A3=BF5=14=C3=82=04v= V=C3=A6W&=16=C3=82=04=C3=96=16=CB=86=C3=86=CB=86=C3=A6r=04=C3=86=CB=9C7@=E2= =80=B9=C2=A4f=C3=B7"=06=C3=86=CB=9C7B=06=17&6=E2=88=AB=CB=9CfW3=C2=A2=06=CE= =A9GG=03=C2=A2=C3=B2=C3=B6=C3=86=CB=9C7G2=C3=A6=E2=97=8A=CB=9C7=16=C3=82=C3= =A66=C3=B6=C3=92=C3=B6=E2=97=8A=CB=9C7=16=C3=80=E2=80=B9=C2=A5F=C3=B2=07V=C3= =A77V'67&=CB=86&S=C2=A2=02=02=02=06=CE=A9GG=03=C2=A2=C3=B2=C3=B6=C3=86=CB=9C= 7G2=C3=A6=E2=97=8A=CB=9C7=16=C3=82=C3=A66=C3=B6=C3=92=C3=B6=E2=97=8A=CB=9C= 7=16=C3=80=E2=80=B9 =E2=80=B9