[ILUG] Dual Master MySQL replication ?

[Nick Murtagh]

Vincent Cunniffe wrote:
> 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.

Yes, this is non-trivial. Going back to my RAID-1 analogy, can you
imagine if that was implemented similarly? "We'll let some applications
write to one disk, and some other applications write to the other disk,
and fingers crossed we'll be able to synchronise the disks later without 
any conflicts!" :)

> 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 think you're referring to my post, but I was suggesting completely
bypassing MySQL's builtin replication and replacing it with a reliable
fault tolerant messaging system that uses one or more MySQL servers
as backends. Obviously not a simple system to build. But you will either
have to do something like this or, as you suggest, write some
application level "magic" that will probably not handle all corner
cases.

Or get a database designed for use in a cluster.


Nick