List:Replication« Previous MessageNext Message »
From:Mike Diehl Date:April 7 2010 6:43pm
Subject:Re: Circular replication?
View as plain text  
On Wednesday 07 April 2010 11:24:34 am Marcus Bointon wrote:
> On 7 Apr 2010, at 18:27, Mike Diehl wrote:
> > A split read/write scheme will affect reliability if one of the write
> > servers happends to be down...  As I'm understanding it, Mysql's
> > replication would survive this scenario.  What I'd be getting is the
> > ability to do local reads/writes and Mysql would simply propogate my
> > writes.  For the most part, data latency won't be a big deal for my
> > applications unless we're talking HOURS. (and we're not, unless something
> > is BAD wrong.)
> OK, I can see a problem here; a mysql instance can only be a slave to one
> master. A 'normal' mode of circular replication would be A<->B, treating A
> as the active master (i.e no writes normally go to B, but it will all still
> work if you do). If you have A->B->C->A (C being offsite) and C dies, no
> writes to B will make it back to A. That's a pretty fragile setup. You
> could do active failover to take C out of the loop and repoint B at A,
> however, that's likely to cause data loss if C ever comes back.

Ya, that's extremely fragile and I can't believe I missed that.  Thank you.  
Now I get to reconsider my entire design! ;)

I'm leaning toward:  A<->B->C  Thanks for the suggestion.

Then I point my local applications to A/B and just take the hit.  I can use C 
to perform backups without killing A/B.  This would let me do by backups in 
the daytime, what a thought!

Now, if B dies, my backups will get behind until I either re-peer A->C, or 
bring B back up.  I can live with that.

C could also serve as a hot stand-by incase the co-lo that houses A/B falls 

Am I missing anything?

> Replication latency becomes a problem if you do any long transactions. If
> you have a transaction that takes 10 minutes on A, it won't show up on C
> until 30 minutes later (assuming equal server performance).

I don't have any update transactions that take more than a few seconds, and I 
don't read the resulting data for several minutes.  So I'm OK here.

I am worried about things like:

update worklist set work_sequence=random() where...

I'd LIKE this to work as expected.  I'd rather not have to loop over the 
dataset and set individual records...  My research, so far, hasn't revealed 
how this would be handled.

> That's the one - mmm needs to be able to handle floating IPs and emit arp
> packets, which only really makes sense on a LAN.

If this were possible at all, it would be a routing nightmare.  My co-lo 
provider probably wouldn't allow/support it.


Take care and have fun,
Mike Diehl.
Circular replication?Mike Diehl7 Apr
  • Re: Circular replication?Marcus Bointon7 Apr
    • Re: Circular replication?Mike Diehl7 Apr
      • Re: Circular replication?Marcus Bointon7 Apr
        • Re: Circular replication?Mike Diehl7 Apr
      • Re: Circular replication?Andrew Garner7 Apr
        • Re: Circular replication?Max Schubert7 Apr
          • Re: Circular replication?Mike Diehl7 Apr
            • Re: Circular replication?Max Schubert7 Apr
  • Re: Circular replication?Ben Mildren7 Apr