MySQL Forums
Forum List  »  Replication

Re: Replication after dump
Posted by: Rick James
Date: April 27, 2011 07:52AM

> The system will automatically switch to db 2 in the event of failure of db 1.
I hope this "automatic switch" will automatically do nothing if it cannot get to both machines. The worst thing that could happen is that the Live Master becomes inaccessible, but is still running. Then, if the auto-switch promotes the other machine, you will have two live Masters. This could lead to duplicate keys, and other messes to unravel.

To have a "dual master" setup, have binlogs enabled on _both_ machines and have each machine being a "slave" to the other. That is, each is both a Master and a Slave. But direct all writes to the currently "live" master.

With that, there are no hassles with binlogs, etc. Failover involves
* stop all writes to the live master
* set it readonly
* turn off readonly on the backup master (the "new" master)
* point all writers to the new master.
The two machines have now swapped roles (of live vs backup).

Failback is the same process, but with the roles reversed. Often, there is no reason to failback. Do you have some reason?

If one of the machines crashes and cannot be recovered, you are in a worse situation. You must
* stop all writes to the surviving machine while you take some kind of dump
* record the binlog position (SHOW MASTER STATUS)
* allow writes (they will queue up in the binlog, which has always been enabled)
* reload the other machine from the dump
* turn on replication; readonly=on; the intervening writes will flow from the binlog and eventually be applied to the new machine.

To avoid having to stop writes for the length of the dump, you should have had a third server slaved off one of the masters. Then, at the worst point in the recovery, your 3 machines are
* a live master taking all traffic
* a dead master
* an offline slave being cloned to reconstruct the dead master.

If you have more than the one slave, then
* Any slaves connected to the surviving master can continue to handle read traffic.
* Any slaves connected to the dead master should be taken offline and rebuilt. Or, you could go through the tedious process of figuring out what parameters to set on a CHANGE MASTER to point them at the live master.

Options: ReplyQuote


Subject
Views
Written By
Posted
2546
April 18, 2011 02:36PM
Re: Replication after dump
851
April 27, 2011 07:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.