List:Replication« Previous MessageNext Message »
From:Rick James Date:May 19 2006 5:50pm
Subject:RE: Heartbeat+replication
View as plain text  
There is NO safe way to use a VIP in automated failover.

Dual master has problems in auto-increment.

Here's one of many nasties:

Suppose you are inserting into a table with an autoincrement...
1.  INSERT goes, via VIP, to machine A, but replication is behind.
Autoincrement is set to 1234.
2.  Next INSERT goes to B.  (Or your failover mechanism decides that A is
down!)
3.  Another INSERT goes, this time, to B.  It looks at the table, and sets
autoincrement to 1234.
4.  First INSERT gets to B.  (In the failover case, let's say it got across
the network before the crash, but was delayed in the sql_thread.)
Replication hangs with "duplicate key".

> -----Original Message-----
> From: Kishore Jalleda [mailto:kjalleda@stripped] 
> Sent: Wednesday, May 17, 2006 7:33 PM
> To: Leo
> Cc: replication@stripped
> Subject: Re: Heartbeat+replication
> 
> You are right!!!, Multi-Master if done in the right way would exactly
> do what you are looking for.........
> 
> Kishore Jalleda
> 
> On 5/17/06, Leo <llei@stripped> wrote:
> > Hi, Jalleda
> >
> > Thanks for your nice webpage and documentation, it's quite helpful.
> >
> > The reason I choose master/master rather than master/slave 
> is that in
> > master/slave replication, once the master dies, the slave will take
> > over the master, but the configuration for the slave machine doesn't
> > change, it still act as a slave and waiting for the bin-log from
> > master. When the master comes up, it's a bit troublesome to transfer
> > the new data from slave to master (have to change the configuration
> > several times). For master/master replication, we won't worry about
> > this, just bring up the master, and it will auto get teh new data
> > from the "slave". Am I correct or I miss something?
> >
> > Thanks a lot!
> >
> > Regards,
> >
> > Leo
> >
> >
> >
> > At 11:43 PM 5/17/2006, Kishore Jalleda wrote:
> > >The setup we have use in production is two Servers in a typical
> > >Master/Slave setup with a VIP Floating between them using
> > >Heartbeat+MON, with some customs scripts taking care of things like
> > >replication consistency, sanity, lag, etc.., along with 
> MON for layer
> > >7 health checks on MySQL, and it works like a champ, the 
> slaves (Load
> > >Balanced) would connect to that VIP and get updates, in case the
> > >Master went down the Slave (The new Master) would assume 
> that VIP and
> > >serves the other group of slaves..anyway I have a How-To 
> on my website
> > >for MySQL failover with HB+MON, it may be helpful, check it out
> > >http://kjalleda.googlepages.com/mysqlfailover
> > >
> > >Kishore Jalleda
> > >
> > >On 5/16/06, Leo <llei@stripped> wrote:
> > >>Thank Mattias, I will configure this and have a try.
> > >>
> > >>Thank James also, for our case, the two mysql servers are close to
> > >>each other, I assume that the network should not be a 
> problem, maybe
> > >>it's a issue in the future.
> > >>
> > >>Regards,
> > >>
> > >>Leo
> > >>
> > >>
> > >>At 07:54 PM 5/15/2006, you wrote:
> > >> >Leo wrote:
> > >> >>Hi, All
> > >> >>
> > >> >> From some posts, I get a feeling that a heartbeat +
> > >> >> Master<--->Master replication is a reasonable way to 
> do fail-over
> > >> >> replication. Here I have some queries about this setup:
> > >> >>
> > >> >>1. If the server with the IP (resource configured in
> heartbeat)
> > >> >>dies, the other server will take the IP, this is what 
> we want. But
> > >> >>if the mysql process in the server dies, and the 
> server is still
> > >> >>on, will the other server notice it and take over? or 
> we have to
> > >> >>use some other packages to monitor it (for example, mon)?
> > >> >No, this will not be noticed by heartbeat. Yes, you 
> have to use some
> > >> >other package for this, for example mon/monit or write your own
> > >> >script and use it with crontab or something like that.
> > >> >
> > >> >>2. What are the things I need to keep in mind to 
> implement this? I
> > >> >>saw some issues like auto-increment, data integrity, if I only
> > >> >>write to one server at one time, should I worry about this?
> > >> >If feel that you are resonable safe if you only write 
> to one server
> > >> >at a time but to be shore if things by some reason goes wrong
> > >> >anyway, ie we still have a connection open to the "old" 
> server after
> > >> >a failover, I would still use 
> auto-increment-increment/offset in my
> > >> >mysql-config.
> > >> >
> > >> >Like this in my.cnf:
> > >> >Server A:
> > >> >---
> > >> >[mysqld]
> > >> >server-id = 1
> > >> >auto_increment_increment = 2
> > >> >auto_increment_offset = 1
> > >> >max_allowed_packet=128M
> > >> >
> > >> >#master replication
> > >> >log-bin=mysql-bin
> > >> >
> > >> >#slave replication
> > >> >master-host=bint
> > >> >master-port=3306
> > >> >master-user=replication-user
> > >> >master-password=replication-passwd
> > >> >master-connect-retry=60
> > >> >relay-log=a-relay-bin
> > >> >report-host=A
> > >> >---
> > >> >
> > >> >Server B:
> > >> >---
> > >> >[mysqld]
> > >> >server-id = 2
> > >> >auto_increment_increment = 2
> > >> >auto_increment_offset = 2
> > >> >max_allowed_packet=128M
> > >> >
> > >> >#master replication
> > >> >log-bin=mysql-bin
> > >> >
> > >> >#slave replication
> > >> >master-host=aint
> > >> >master-port=3306
> > >> >master-user=replication-user
> > >> >master-password=replication-passwd
> > >> >master-connect-retry=60
> > >> >relay-log=b-relay-bin
> > >> >report-host=B
> > >> >---
> > >> >
> > >> >Please have a look at:
> > >> 
> >http://dev.mysql.com/doc/refman/5.0/en/replication-auto-incre
> ment.html
> > >> >
> > >> >>3. If these two servers are only linked through the 
> network cables
> > >> >>(no serial ports connection or crosscable), will the 
> performance of
> > >> >>the server be affected by the "ping" packages? or it's 
> better to
> > >> >>have an additional connection?
> > >> >No, the ping packages will not affect your performance.
> > >> >The serial-link cables are for extra 
> heartbeat-cluster-communication
> > >> >redundancy. It is a good thing that the cluster-nodes still can
> > >> >communicate there status with each other even when you for some
> > >> >reason have a network failure or what ever. Please see:
> > >> 
> >http://www.linux-ha.org/FAQ#head-4316d9f4d060352961a20a22340f
> 070b5da39818
> > >> >
> > >> >>
> > >> >>4. Is it stable to configure like this? Does any one have some
> > >> >>successful stories about using this in production environment?
> > >> >Yes, we use a circular-multi-master setup similar to this in
> > >> >production environment.
> > >> >
> > >> >Regars,
> > >> >Mattias
> > >> >>
> > >> >>Thanks a lot!
> > >> >>
> > >> >>Regards,
> > >> >>Leo
> > >> >>
> > >> >>
> > >> >
> > >> >
> > >> >--
> > >> >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
> > >>
> > >
> > >--
> > >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
> >
> >
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    
> http://lists.mysql.com/replication?unsub=1
> 
> 
> 

Thread
Heartbeat+replicationLeo15 May
  • Re: Heartbeat+replicationMattias Andersson15 May
    • Re: Heartbeat+replicationLeo16 May
      • Re: Heartbeat+replicationKishore Jalleda17 May
        • Re: Heartbeat+replicationLeo18 May
          • Re: Heartbeat+replicationKishore Jalleda18 May
            • RE: Heartbeat+replicationRick James19 May
              • Re: Heartbeat+replicationKishore Jalleda19 May
              • RE: Heartbeat+replicationLeo22 May
                • Re: Heartbeat+replicationMattias Andersson22 May
                  • Re: Heartbeat+replicationLeo22 May
                    • Re: Heartbeat+replicationMattias Andersson22 May
                      • Re: Heartbeat+replicationLeo24 May
                        • RE: Heartbeat+replicationRick James24 May
                          • Re: Heartbeat+replicationMattias Andersson24 May
                        • Re: Heartbeat+replicationMattias Andersson24 May
  • RE: Heartbeat+replicationRick James15 May