List:Replication« Previous MessageNext Message »
From:Alper Oguz Date:February 8 2010 3:01pm
Subject:Re: Send binlogs immediately?
View as plain text  
Hello again and thank you everyone for replies. I was away for a while.

I found the problem partially, but not solution yet. Sometimes I've lot of 
traffic on DB processes and most of them are INSERT and UPDATE (%60). Maybe, I 
was made a mistake in auto-increment variables.

Because, when Heartbeat stops eth0:0 and Tomcat on server1 and starts them on 
server2 for give resources to server2, previous operation (on Tomcat) on 
server1 is interrupting and retrying by the other servers. They connecting to 
my IP again for the same operation. But, sometimes Mysql on server2 trying to 
add a row which already wrote via replication (it's a application issue). And 
giving error on replication for duplicate entry.

But, I've some doubts that's a auto-increment problem. There's only one column 
in the table defined as unique and it's ID column (auto inc.).

I was thought to send binlogs to server2 on my first message for prevent 
duplicate errors. But I know that isn't a solution if a mistake exists on 
configuration.

My auto-increment values like this:

server1:
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 1
auto-increment-offset = 1

server2:
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 1
auto-increment-offset = 2

Is there any mistake on these values?

Alper


Shawn Green wrote On 29-01-2010 15:13:
> I think you misunderstand the replication system that MySQL users. There
> are two separate processes on each slave: the SLAVE IO thread and the
> SLAVE SQL thread.
>
> The SLAVE IO thread is responsible for connecting to the MASTER and
> copying the available contents of the master's binary logs into the
> slave's relay logs. This happens continuously while the thread is
> running and is limited only by the speeds of the network between the
> MASTER and SLAVE and the storage system for the relay logs on the SLAVE
> itself.
>
> The SLAVE SQL thread then steps through the contents of the relay logs,
> one statement at a time, to repeat the sequence of changes that happened
> to the data on the MASTER to the same data on the SLAVE.
>
> There must always be some physical difference between the two systems
> (the MASTER and all of its SLAVEs) due to the sequencing of these
> physical events:
>
> 1) A statement (or row delta) is not written to the MASTER's binary log
> until after that statement completes and is committed to the table.
>
> 2) Concurrent statements on the MASTER are serialized by the binary log.
>
> 3) The SLAVE must stream the binary logs to it's local relay logs
>
> 3) the SLAVE must re-execute or re-apply the same changes to its copy of
> the data.
>
> You can see how much backlog any one slave may have by checking the
> Seconds_Behind_Master value from the SHOW SLAVE STATUS report.
>
Thread
Send binlogs immediately?Alper Oguz29 Jan
  • Re: Send binlogs immediately?Shawn Green29 Jan
    • Re: Send binlogs immediately?Alper Oguz8 Feb
      • Re: Send binlogs immediately?Mats Kindahl8 Feb
        • Re: Send binlogs immediately?Alper Oguz9 Feb
          • Re: Send binlogs immediately?Maxim Veksler9 Feb
  • Re: Send binlogs immediately?Tyler Poland29 Jan
    • Re: Send binlogs immediately?Rick James29 Jan