List:General Discussion« Previous MessageNext Message »
From:Todd Lyons Date:August 31 2010 11:39pm
Subject:Re: master-slave replication sync problems.
View as plain text  
On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine <norman@stripped> wrote:
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:

As one other stated, it should not lose position when you stop and
start the master or slave in a controlled manner.  And also stated was
that the way you were doing it was causing a loss of data, and you'll
need to use maatkit to fix it, or if you can afford some downtime,
stop mysql on both and copy the whole database(s) from master to slave
(and reconfigure replication of course).

On the slave, I'd be curious what the result was when you did only a
"STOP SLAVE;" and then "START SLAVE;" because in my estimation,
chances are it would have picked right up where it was supposed to.

Your issue is likely that your slave has a large "slave_net_timeout"
value (default is 3600 seconds IIRC).  That means your slave will take
1 hour to finally give up on the replication connection (that the
master closed on it), close the connection, and then reopen it.  Lower
that setting to something smaller, I've gone as low as 30 seconds, but
I would only do that on a reliable network.  Try lowering it to 300 (5
minutes) on your slave.  When you restart the master, the slave should
timeout after only 5 minutes and then resume replication.

-- 
Regards...      Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm.  -- Marcus Aurealius
Thread
master-slave replication sync problems.Norman Khine26 Aug
  • Re: master-slave replication sync problems.Ananda Kumar26 Aug
  • Re: master-slave replication sync problems.a.smith26 Aug
    • Re: master-slave replication sync problems.Ananda Kumar26 Aug
    • Re: master-slave replication sync problems.Norman Khine26 Aug
      • Re: master-slave replication sync problems.Ananda Kumar26 Aug
      • Re: master-slave replication sync problems.a.smith26 Aug
        • Re: master-slave replication sync problems.Ananda Kumar26 Aug
          • Re: master-slave replication sync problems.a.smith26 Aug
  • Re: master-slave replication sync problems.jitendra ranjan26 Aug
    • Re: master-slave replication sync problems.a.smith26 Aug
  • RE: master-slave replication sync problems.Daevid Vincent26 Aug
    • Re: master-slave replication sync problems.MySQL)27 Aug
  • Re: master-slave replication sync problems.Todd Lyons1 Sep