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?
> 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
Maintained by the ILUG website team. The aim of Linux.ie is to
support and help commercial and private users of Linux in Ireland. You can
display ILUG news in your own webpages, read backend
information to find out how. Networking services kindly provided by HEAnet, server kindly donated by
Dell. Linux is a trademark of Linus Torvalds,
used with permission. No penguins were harmed in the production or maintenance
of this highly praised website. Looking for the
Indian Linux Users' Group? Try here. If you've read all this and aren't a lawyer: you should be!