Are both Masters writeable? If so, you have even worse problems. If not, then consider switching to one master with 2 slaves and use MHA for failover, etc. With that, you can seamlessly and nearly instantly switch to any slave. It does not involve a "restore", but gets you alive very quickly by switching to another machine.
For your situation: Master (local), another Slave somewhere else but nearby, Slave in South Africa. Then, during failover, prefer the nearby slave. After failing over, worry about 'fixing' the dead Master at your leisure. What is the purpose of the restore? 1. Recovery from dead Master? -- MHA may be best 2. Recovery to some previous point-in-time? -- much of the discussion has centered around this obscure use 3. Building staging/qa/dev/etc machine that matches the master at some point in time? There are different solutions to each of these. #3 is probably best done by having a slave hanging off the Master, then disconnecting it when you want it. If you muck with the data, it will need a long restore; if not, it is a matter of reconnecting and letting replication catch up. > -----Original Message----- > From: machiel.richa...@gmail.com [mailto:machiel.richa...@gmail.com] > Sent: Thursday, November 01, 2012 8:54 AM > To: Reindl Harald; mysql@lists.mysql.com > Subject: Re: Mysql backup for large databases > > 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 server 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 <h.rei...@thelounge.net> > Date: Thu, 01 Nov 2012 16:49:45 > To: mysql@lists.mysql.com<mysql@lists.mysql.com> > 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 = 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=0 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) > > > > On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald <h.rei...@thelounge.net > <mailto:h.rei...@thelounge.net>> 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 >
