List:Replication« Previous MessageNext Message »
From:Moon's Father Date:March 21 2008 1:59am
Subject:Re: Basic questions on slave servers
View as plain text  
You should add read-only in your slave's configuration file.

On Fri, Mar 21, 2008 at 5:04 AM, Rick James <rjames@stripped> wrote:

> below...
>
> > -----Original Message-----
> > From: Eric Frazier [mailto:efrazier@stripped]
> > Sent: Thursday, March 20, 2008 1:02 PM
> > To: Rick James
> > Cc: Andy Smith; replication@stripped
> > Subject: Re: Basic questions on slave servers
> >
> > Rick James wrote:
> > > * Write to only one of the Masters -- this eliminates various
> > > auto-increment issues, etc.  Also, writing to both masters
> > buys nothing,
> > > certainly not write-scalability since all writes have to be
> > performed on
> > > both machines.
> > >
> > It does indeed buy you something. If your masters are in
> > "geographicly
> > diverse locations"  Take 90ms latency to a webserver vs .2ms over the
> > course of a few million connections.
>
> In one case, we chose to send the API request to the colo where the
> master is.
>
> Usually the one or two INSERTs can afford the 70ms delay we see between
> coasts.
>
> > > * Do not attempt to deploy automatic failover -- there can
> > be partial
> > > network outages that will fool the automatic thingie into thinking a
> > > Master is down.  You can end up with 2 masters being
> > written to; this
> > > will hopelessly corrupt the data.
> > >
> > Little bit of a bold statement. Given your failover script,
> > what caused
> > the problem exactly? How could you have fixed things to
> > prevent the dual
> > write? How was a dual write even possible according to your DB scheme?
>
> More details on the bad case:
> * Colo where Master is goes dark, but it is actually because of a
> network glitch.
> * Clients within that colo were happily writing to the Master.
> * Failover script (not automated), alert us that the "master seems to be
> dead".
> * Manual fishing around discovered what the case really was, so we held
> off on failing over.
>
> Alas, that led to a "partial outage" for a couple hours while the
> network was repaired.  But that was better than having two writable
> masters.
>
> Note: I could not reach the Master to determine its state, nor to shut
> it down.
>
> > > * If you scale reads by having slaves, some should hang off
> > each Master.
> > > This way, half your slaves are still up-to-date if one
> > (either) master
> > > dies.
> > >
> > Good advice I think.
> >
> > > * There are rare cases where "one" item in the replication stream
> > > between the Masters will get lost in a hard crash.
> > >
> > > * Put your Masters in two geographically separate places -- why have
> > > dual-master if a power failure (earthquake, etc) can still
> > bring down
> > > the whole system?
> > >
> > > * To be doubly safe, set the backup master to read_only.
> > >
> >
> > What about sync_binlog=1 ?
>
> New feature; haven't tried it yet.  But with our Masters 70ms apart, and
> avg of 150 updates/sec, it might not be viable?
>
> > > * Replication items will not "loop" -- the server_id of the original
> > > sender is noticed, thereby allowing the items to be tossed.
> > >
> > No one should think of using autoinc keys in tables unless
> > they want to
> > run into larger problems as they scale, at least not with
> > MySQL. Having
> > said that, auto_increment_increment, auto_increment_offset   allow
> > screwed up DBs to still work with master - master. I use
> > those because
> > those choices are not always mine.
>
> Yeah, I leave those tricks as a last resort.
>
> > Thanks,
> >
> > Eric
> >
> > >
> > >> -----Original Message-----
> > >> From: Andy Smith [mailto:a.smith@stripped]
> > >> Sent: Thursday, March 20, 2008 10:52 AM
> > >> To: replication@stripped
> > >> Subject: Re: Basic questions on slave servers
> > >>
> > >> Hi Martin,
> > >>
> > >>   ok thanks, so the next questions that come to mind are:
> > >>
> > >> You say in the past its not been recommended, that implies
> > >> that the situation has now changed and that
> > >> this is seen as a stable configuration (production ready or
> > >> however u want to call it). Would that be correct?
> > >>
> > >> Good to hear your working on some complete documentation! In
> > >> the mean time, can you point me at any
> > >> useful information regarding this?? How it handles
> > >> replication WRT writes to multiple hosts etc?
> > >>
> > >> If I just need HA and in practice I am never going to be
> > >> doing writes to both masters at the same time is this
> > >> already a fairly robust solution for my needs? Ie perhaps I
> > >> will have a floating IP which Ill move from one system
> > >> to the other in the event of any issues on the primary master,
> > >>
> > >> thanks for your help! Andy.
> > >> There are two reasons for this:
> > >>
> > >> 1) It's not something that in the past we have recommended as a
> > >> solution because of the potential problems (overwriting
> > IDs, getting
> > >> DBs out of sync, etc).
> > >>
> > >> 2) We did have, incomplete, information and advice in the manual,
> > >> which was removed to be part of a new topology chapter. This new
> > >> chapter is currently being written :)
> > >>
> > >>
> > >
> > >
> >
> >
> > --
> > Eric Frazier
> > Holistis.com
> > 216 Main Street, Suite 312
> > Bathurst, NB, E2A 1A8
> > Canada
> > Phone  : +1 506 546 8292  ext 3303
> > Fax    : +1 506 546 8092
> >
> >
> > --
> > MySQL Replication Mailing List
> > For list archives: http://lists.mysql.com/replication
> > To unsubscribe:
> > http://lists.mysql.com/replication?unsub=1
> >
> >
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/replication?unsub=1
>
>


-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Thread
Basic questions on slave serversAndy Smith18 Mar
  • Re: Basic questions on slave serversChris.Leeworthy18 Mar
    • Re: Basic questions on slave serversEd W23 Mar
      • Re: Basic questions on slave serversArthur Fuller23 Mar
        • Re: Basic questions on slave serversEd W23 Mar
  • RE: Basic questions on slave serversEdward F. Pauley II18 Mar
Re: Basic questions on slave serversAndy Smith18 Mar
  • RE: Basic questions on slave serversRick James19 Mar
Re: Basic questions on slave serversAndy Smith20 Mar
  • Re: Basic questions on slave serversMartin MC Brown20 Mar
Re: Basic questions on slave serversAndy Smith20 Mar
  • Re: Basic questions on slave serversMarcus Bointon20 Mar
  • RE: Basic questions on slave serversRick James20 Mar
    • Re: Basic questions on slave serversEric Frazier20 Mar
      • RE: Basic questions on slave serversRick James20 Mar
        • Re: Basic questions on slave serversMoon's Father21 Mar
Re: Basic questions on slave serversAndy Smith21 Mar