From: Manuel Arostegui Date: November 2 2012 7:55am Subject: Re: Mysql backup for large databases List-Archive: http://lists.mysql.com/mysql/228572 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=000e0cdfd33645120004cd7e7970 --000e0cdfd33645120004cd7e7970 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, Just one more suggestion to do full backups in large databases: - 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. 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. 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. And lastly, but probably most important....test your backups periodically!! Hope this helps Manuel. 2012/11/1 Karen Abgarian > Hi, > > For doing backups on the primary database, I know nothing better than hav= e > 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 wan= t > 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 th= e > impact. > > 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 wi= ll > result in cute little inconsistencies unless a DBA is one lucky guy and > always wins playing roulette. > > Combining the two, I like the idea of doing EM backup on a replica and > having all tables in InnoDB. > > 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 wh= en > 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 fo= r > recoveries across datacenters and the like. > > Backing up binary logs allows providing continuous coverage for recovery > instead of discrete. > > Cheers > Karen > > > 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. > > > > 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 in > the states. > > > > > > Sent via my BlackBerry from Vodacom - let your email find you! > > > > -----Original Message----- > > From: Reindl Harald > > Date: Thu, 01 Nov 2012 16:49:45 > > To: mysql@stripped > > Subject: Re: Mysql backup for large databases > > > > good luck > > > > 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 > > > > 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) > >> > >> On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald 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 > > > > > > =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=9C7= G2=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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > > --=20 Manuel Ar=C3=B3stegui Systems Team tuenti.com --000e0cdfd33645120004cd7e7970--