From: Karen Abgarian <abvk Date: November 2 2012 4:36pm Subject: Re: Mysql backup for large databases List-Archive: http://lists.mysql.com/mysql/228573 Message-Id: <56D53402-FF50-4EA0-86A1-9119E25D0809@apple.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi... Quite often, they create a replica for the purposes of switching to it = immediately if the master completely fails. I like the idea of taking = backups on that replica, and not create another replica just for doing = backups. =20 One reason why I like it is that if I am to recover a database, I will = want to do the following: a) restore the database backup; b) apply = binary logs starting from the exact position as of which that backup is = a snapshot of my database. If I have to use the binary logs taken on = another database (f.e. backups are taken on a replica and binary logs on = a master), then I will need to match the contents of one database binary = logs with the time the backup was taken, which will make my recovery = approximate and error prone. =46rom this, it kind of follows that I will = want to set log-slave-updates and, for recovery, use backups of a = replica and the binary logs generated by that replica. =20 If the backups are on the third system, and I also have another replica = for failovers, I will not only use an additional server and storage, but = also my recovery plan will become messy. On a failure of a primary, if = I will be switching to a replica, I will have to make it a master to the = replica I am taking backups on, which can be done, well, approximately. = If I do not want to deal with manually finding a position to = synchronize them together, I will end up destroying a second replica for = backups and rebuilding it from the replica I just switched to, for which = in turn I will have to take another backup. On a failure of primary in = which I will be restoring the database from the replica I designated for = backups, I will also have to restore the replica I use for failover = protection. =20 All this tells me that it will be substantially simpler if the replica = for backups and replica for failovers are the same thing. =20 Peace Karen On 02.11.2012, at 0:55, Manuel Arostegui wrote: > Hello, >=20 > Just one more suggestion to do full backups in large databases: >=20 > - Dedicated slave (either physical machine, a disk cabinet using iscsi > connections from a machine just with a bunch of RAM etc) > - Get the slave delayed a certain time (ie: 1 hour, 2 hours...depends = on > your workload) using Percona Toolkit ( > http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) > - Make sure that slave has (*log*-*slave*-*updates) *in my.cnf. > - Make sure the master keeps the necessary logs for a point in time > recovery if needed. >=20 > If something goes wrong you have a full copy of your database, just = delayed > a few hours. If you want to recover from a drop database (or any big = fail > executed from MySQL CLI) in the master, you can just sync up your = delayed > slave to that specific statment - go thru your master binlog, locate = the > "drop database" statement and set START SLAVE SQL_THREAD UNTIL > master_log_file =3D 'mysql-bin.XXX', master_log_pos=3DXXX; to the = position > right before the bad one. That way you'd have your database as it was = just > before the wrong statement execution. >=20 > As Rick said - if you're thinking about doing snapshots, make sure you = stop > your mysql daemon in the delayed slave to make sure everything is = committed > to disk. Otherwise can end up having a corrupted DB which won't boot = when > you need it. >=20 > And lastly, but probably most important....test your backups = periodically!! >=20 > Hope this helps > Manuel. >=20 >=20 > 2012/11/1 Karen Abgarian >=20 >> 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 >>=20 >> On 01.11.2012, at 8:53, machiel.richards@stripped wrote: >>=20 >>> 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 >>> 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 >>>> On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald = > h.reindl@stripped>> 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 =C3=82 = vV=C3=A6W& =C3=82 =C3=96 =CB=86=C3=86=CB=86=C3=A6r =C3=86=CB=9C7@=E2=80=B9= =C2=A4f=C3=B7" =C3=86=CB=9C7B &6=E2=88=AB=CB=9CfW3=C2=A2 =CE=A9GG = =C2=A2=C3=B2=C3=B6=C3=86=CB=9C7G2=C3=A6=E2=97=8A=CB=9C7 >> =C3=82=C3=A66=C3=B6=C3=92=C3=B6=E2=97=8A=CB=9C7 =C3=80=E2=80=B9=C2=A5F=C3= =B2 V=C3=A77V'67&=CB=86&S=C2=A2 =CE=A9GG =C2=A2=C3=B2=C3=B6=C3=86=CB=9C= 7G2=C3=A6=E2=97=8A=CB=9C7 =C3=82=C3=A66=C3=B6=C3=92=C3=B6=E2=97=8A=CB=9C7 = =C3=80=E2=80=B9 =E2=80=B9 >>=20 >>=20 >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >>=20 >>=20 >=20 >=20 > --=20 > Manuel Ar=C3=B3stegui > Systems Team > tuenti.com