List:Replication« Previous MessageNext Message »
From:Mats Kindahl Date:February 8 2010 8:28pm
Subject:Re: Send binlogs immediately?
View as plain text  

Alper Oguz wrote:
> 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

This will assign values 1, 2, 3, ... to the column.

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

This will assign values 2, 3, 4, ... to the column.

> Is there any mistake on these values?

You seem to be mixing up auto-increment-increment and

Best wishes,
Mats Kindahl

> 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.

Mats Kindahl
Senior Software Engineer
Database Technology Group
Sun Microsystems
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