[ILUG] Dual Master MySQL replication ?

[Vincent Cunniffe]

Paul Reilly wrote:

> Has anyone any experience setting up Dual Master replication
> between 2 MySQL servers? What I want is described in slide 24
> "Dual Master Replication" at
> 
> http://jeremy.zawodny.com/mysql/managing-mysql-replication.html
> 
> However the official MySQL docs suggest dual master is a no-go.
> I have slave/master working OK. Is it just a matter of doing the
> same thing again with the roles reversed, so both servers are master
> and slave at the same time?

Yes.

> In my scenario, only one server will get updates, but if it's down, then
> the second server will get the updates, and would need to update the first
> master with any changes when it came back up. And vice-versa!

That will work perfectly.

Basically, (under MySQL replication), each server has it's own serverID. 
All updates generated by that server are stamped with the serverID. Any 
connected slave databases will update and 'replay' any events which 
occur under a serverID of anything that isn't themselves. Using this 
scheme you can have 1-way, 2-way, star or chain replication, with each 
machine maintaining an up-to-the-second copy of all of the current data.

If a machine becomes unavailable for a while, and then switches back on, 
it will reconnect to the other server(s), replay all of the events which 
occurred since it was connected, and then go back to waiting on more events.

If you're only 'creating' events (i.e. performing write operations) on 
one of the servers, then this n-way scheme will work out of the box 
without issues. You can switch between which machine is master at any 
given point in time, and start generating events on that.

The problem occurs when you are generating events on multiple servers 
concurrently, and using things like autoincrement columns. In this case, 
you could simultaneously create a record on two servers, each with the 
same column ID. Each of them would then try to replicate the event of 
the other, find a matching local column ID and fail to play the event. 
At this point the replication threads on both machines halts with an 
error, and the state of the two servers begins to diverge.

This requires an application level solution with some sort of locking or 
communication to ensure that colliding data is not written to the two 
(or N) databases simultaneously. If you have sparse events you could 
just ignore the issue and rely on non-colliding events, but you *will* 
get bitten eventually.

Someone else in this thread suggested that your application connects to 
two databases simultaneously and performs the operation on both. The 
problem with this is that if the databases *are* replicating as well, 
the write operation on the second will occur after the replicated 
operation from the first, and either fail or duplicate, and if they're 
not replicating and one of the machines becomes unavailable for a while, 
it won't collect the events which occurred while it was offline. If you 
adopt this latter solution, then you would have to fully reload the 
status of the machine which comes back up (there's a one-liner for this 
in MySQL, but it might take some time to reload everything).

I realise that all of this may not be of too much use in actually 
solving your problem, but hopefully it'll be useful in deciding how to 
approach it.

Regards,

Vin