FAQ
Hi,

On a master to master replication, if a table doesn't have a auto_increment column, how the server(s) prevents update statement from executing twice on same server.

For example, one update is done on server A, and another update is done on server B. But these two update statements are on both binary logs of server A and server B. When server A reads binary log from server B to replicate, two update statement are in binary log, how server A only execute one update done on server B and ignore another one that was done on server A?

Thanks,
Lisa

Search Discussions

  • Mats Kindahl at Mar 31, 2009 at 9:29 pm

    Database System wrote:
    Hi,

    On a master to master replication, if a table doesn't have a auto_increment column, how the server(s) prevents update statement from executing twice on same server.

    For example, one update is done on server A, and another update is done on server B. But these two update statements are on both binary logs of server A and server B. When server A reads binary log from server B to replicate, two update statement are in binary log, how server A only execute one update done on server B and ignore another one that was done on server A?

    Thanks,
    Lisa


    Hi Lisa,

    Each server have to have a server id assigned, so statements executed on A will
    be tagged with the server id of server A (and the same for B). When A then reads
    an event (statement) from itself, it will just skip that event and move on to
    the next one.

    Best wishes,
    Mats Kindahl


    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group, Replication team
    Sun Microsystems
  • Saravanan at Mar 31, 2009 at 9:42 pm
    Hi,
    There are two types of logging in replication. Binary log and Relay log. Binary log is the log of update statements the server updated. The second one is log that is updates received from other server.
    For example server A will not read the binary logs it updates and it will write in the relay logs that it reads from server B.
    In Master to Master setup don't enable log_slave_updates.
    Thanks,Saravanan

    --- On Wed, 4/1/09, Mats Kindahl wrote:

    From: Mats Kindahl <mats@sun.com>
    Subject: Re: Master to master replication
    To: database100@yahoo.com
    Cc: replication@lists.mysql.com
    Date: Wednesday, April 1, 2009, 3:59 AM

    Database System wrote:
    Hi,

    On a master to master replication, if a table doesn't have a auto_increment column, how the server(s) prevents update statement from executing twice on same server.

    For example, one update is done on server A, and another update is done on server B. But these two update statements are on both binary logs of server A and server B. When server A reads binary log from server B to replicate, two update statement are in binary log, how server A only execute one update done on server B and ignore another one that was done on server A?

    Thanks,
    Lisa


    Hi Lisa,

    Each server have to have a server id assigned, so statements executed on A will
    be tagged with the server id of server A (and the same for B). When A then reads
    an event (statement) from itself, it will just skip that event and move on to
    the next one.

    Best wishes,
    Mats Kindahl


    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group, Replication team
    Sun Microsystems

    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:    http://lists.mysql.com/replication?unsub=suzuki_babu@yahoo.com
  • Rick James at Apr 3, 2009 at 6:32 pm
    But, if you have Slaves hanging off both Masters, you need log_slave_updates, right?

    Mats' answer may be less efficient, but still holds.


    Rick James
    MySQL Geeks - Consulting & Review


    -----Original Message-----
    From: Saravanan
    Sent: Tuesday, March 31, 2009 2:43 PM
    To: database100@yahoo.com; Mats Kindahl
    Cc: replication@lists.mysql.com
    Subject: Re: Master to master replication

    Hi,
    There are two types of logging in replication. Binary log and
    Relay log. Binary log is the log of update statements the
    server updated. The second one is log that is updates
    received from other server.
    For example server A will not read the binary logs it updates
    and it will write in the relay logs that it reads from server B.
    In Master to Master setup don't enable log_slave_updates.
    Thanks,Saravanan

    --- On Wed, 4/1/09, Mats Kindahl wrote:

    From: Mats Kindahl <mats@sun.com>
    Subject: Re: Master to master replication
    To: database100@yahoo.com
    Cc: replication@lists.mysql.com
    Date: Wednesday, April 1, 2009, 3:59 AM

    Database System wrote:
    Hi,

    On a master to master replication, if a table doesn't have
    a auto_increment column, how the server(s) prevents update
    statement from executing twice on same server.
    For example, one update is done on server A, and another
    update is done on server B. But these two update statements
    are on both binary logs of server A and server B. When server
    A reads binary log from server B to replicate, two update
    statement are in binary log, how server A only execute one
    update done on server B and ignore another one that was done
    on server A?
    Thanks,
    Lisa


    Hi Lisa,

    Each server have to have a server id assigned, so statements
    executed on A will
    be tagged with the server id of server A (and the same for
    B). When A then reads
    an event (statement) from itself, it will just skip that
    event and move on to
    the next one.

    Best wishes,
    Mats Kindahl


    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group, Replication team
    Sun Microsystems

    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:
    http://lists.mysql.com/replication?unsub=suzuki_babu@yahoo.com
  • Mats Kindahl at Apr 3, 2009 at 7:12 pm

    Rick James wrote:
    But, if you have Slaves hanging off both Masters, you need log_slave_updates, right?
    Yes, otherwise the "slave" will not forward the events that it received from its
    "master".
    Mats' answer may be less efficient, but still holds.
    I must've misunderstood the question. I understood it as a question to how the
    servers can avoid executing queries that it has already executed.

    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group
    Sun Microsystems

    Rick James
    MySQL Geeks - Consulting & Review


    -----Original Message-----
    From: Saravanan
    Sent: Tuesday, March 31, 2009 2:43 PM
    To: database100@yahoo.com; Mats Kindahl
    Cc: replication@lists.mysql.com
    Subject: Re: Master to master replication

    Hi,
    There are two types of logging in replication. Binary log and
    Relay log. Binary log is the log of update statements the
    server updated. The second one is log that is updates
    received from other server.
    For example server A will not read the binary logs it updates
    and it will write in the relay logs that it reads from server B.
    In Master to Master setup don't enable log_slave_updates.
    Thanks,Saravanan

    --- On Wed, 4/1/09, Mats Kindahl wrote:

    From: Mats Kindahl <mats@sun.com>
    Subject: Re: Master to master replication
    To: database100@yahoo.com
    Cc: replication@lists.mysql.com
    Date: Wednesday, April 1, 2009, 3:59 AM

    Database System wrote:
    Hi,

    On a master to master replication, if a table doesn't have
    a auto_increment column, how the server(s) prevents update
    statement from executing twice on same server.
    For example, one update is done on server A, and another
    update is done on server B. But these two update statements
    are on both binary logs of server A and server B. When server
    A reads binary log from server B to replicate, two update
    statement are in binary log, how server A only execute one
    update done on server B and ignore another one that was done
    on server A?
    Thanks,
    Lisa


    Hi Lisa,

    Each server have to have a server id assigned, so statements
    executed on A will
    be tagged with the server id of server A (and the same for
    B). When A then reads
    an event (statement) from itself, it will just skip that
    event and move on to
    the next one.

    Best wishes,
    Mats Kindahl


    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group, Replication team
    Sun Microsystems

    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:
    http://lists.mysql.com/replication?unsub=suzuki_babu@yahoo.com
  • Database System at Apr 6, 2009 at 6:31 pm
    My understanding is, whether log_slave_updates enabled or not, it doesn't affect master to master replication. Anybody has different opinion?

    Lisa


    --- On Fri, 4/3/09, Rick James wrote:
    From: Rick James <rjames@yahoo-inc.com>
    Subject: RE: Master to master replication
    To: "Saravanan" <suzuki_babu@yahoo.com>, database100@yahoo.com, "Mats Kindahl" <mats@sun.com>
    Cc: replication@lists.mysql.com
    Date: Friday, April 3, 2009, 1:29 PM
    But, if you have Slaves hanging off both Masters, you need
    log_slave_updates, right?

    Mats' answer may be less efficient, but still holds.


    Rick James
    MySQL Geeks - Consulting & Review


    -----Original Message-----
    From: Saravanan
    Sent: Tuesday, March 31, 2009 2:43 PM
    To: database100@yahoo.com; Mats Kindahl
    Cc: replication@lists.mysql.com
    Subject: Re: Master to master replication

    Hi,
    There are two types of logging in replication. Binary log and
    Relay log. Binary log is the log of update statements the
    server updated. The second one is log that is updates
    received from other server.
    For example server A will not read the binary logs it updates
    and it will write in the relay logs that it reads from server B.
    In Master to Master setup don't enable
    log_slave_updates.
    Thanks,Saravanan

    --- On Wed, 4/1/09, Mats Kindahl wrote:

    From: Mats Kindahl <mats@sun.com>
    Subject: Re: Master to master replication
    To: database100@yahoo.com
    Cc: replication@lists.mysql.com
    Date: Wednesday, April 1, 2009, 3:59 AM

    Database System wrote:
    Hi,

    On a master to master replication, if a table
    doesn't have
    a auto_increment column, how the server(s) prevents update
    statement from executing twice on same server.
    For example, one update is done on server A, and
    another
    update is done on server B. But these two update
    statements
    are on both binary logs of server A and server B. When server
    A reads binary log from server B to replicate, two update
    statement are in binary log, how server A only execute one
    update done on server B and ignore another one that was done
    on server A?
    Thanks,
    Lisa


    Hi Lisa,

    Each server have to have a server id assigned, so
    statements
    executed on A will
    be tagged with the server id of server A (and the same for
    B). When A then reads
    an event (statement) from itself, it will just skip that
    event and move on to
    the next one.

    Best wishes,
    Mats Kindahl


    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group, Replication team
    Sun Microsystems

    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:
    http://lists.mysql.com/replication?unsub=suzuki_babu@yahoo.com
    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:
    http://lists.mysql.com/replication?unsub=database100@yahoo.com
  • Mats Kindahl at Apr 7, 2009 at 12:53 pm

    Database System wrote:
    My understanding is, whether log_slave_updates enabled or not, it doesn't affect master to master replication. Anybody has different opinion?
    Hi Lisa,

    It does *not* affect master-master replication in the sense that it prevents
    events from flowing between the masters (not enabling --log-slave-updates have
    been called one-stop-replication in some other circles).

    It *does* affect any slaves connecting to either of the master: that slave will
    only see updates from the master it is connected to, not updates originating
    from the other master.

    Just my few cents,
    Mats Kindahl
    Lisa


    --- On Fri, 4/3/09, Rick James wrote:
    From: Rick James <rjames@yahoo-inc.com>
    Subject: RE: Master to master replication
    To: "Saravanan" <suzuki_babu@yahoo.com>, database100@yahoo.com, "Mats Kindahl" <mats@sun.com>
    Cc: replication@lists.mysql.com
    Date: Friday, April 3, 2009, 1:29 PM
    But, if you have Slaves hanging off both Masters, you need
    log_slave_updates, right?

    Mats' answer may be less efficient, but still holds.


    Rick James
    MySQL Geeks - Consulting & Review


    -----Original Message-----
    From: Saravanan
    Sent: Tuesday, March 31, 2009 2:43 PM
    To: database100@yahoo.com; Mats Kindahl
    Cc: replication@lists.mysql.com
    Subject: Re: Master to master replication

    Hi,
    There are two types of logging in replication. Binary log and
    Relay log. Binary log is the log of update statements the
    server updated. The second one is log that is updates
    received from other server.
    For example server A will not read the binary logs it updates
    and it will write in the relay logs that it reads from server B.
    In Master to Master setup don't enable
    log_slave_updates.
    Thanks,Saravanan

    --- On Wed, 4/1/09, Mats Kindahl <mats@sun.com> wrote:
    From: Mats Kindahl <mats@sun.com>
    Subject: Re: Master to master replication
    To: database100@yahoo.com
    Cc: replication@lists.mysql.com
    Date: Wednesday, April 1, 2009, 3:59 AM

    Database System wrote:
    Hi,

    On a master to master replication, if a table
    doesn't have
    a auto_increment column, how the server(s) prevents update
    statement from executing twice on same server.
    For example, one update is done on server A, and
    another
    update is done on server B. But these two update
    statements
    are on both binary logs of server A and server B. When server
    A reads binary log from server B to replicate, two update
    statement are in binary log, how server A only execute one
    update done on server B and ignore another one that was done
    on server A?
    Thanks,
    Lisa


    Hi Lisa,

    Each server have to have a server id assigned, so
    statements
    executed on A will
    be tagged with the server id of server A (and the same for
    B). When A then reads
    an event (statement) from itself, it will just skip that
    event and move on to
    the next one.

    Best wishes,
    Mats Kindahl


    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group, Replication team
    Sun Microsystems

    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:
    http://lists.mysql.com/replication?unsub=suzuki_babu@yahoo.com
    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe:
    http://lists.mysql.com/replication?unsub=database100@yahoo.com



    --
    MySQL Replication Mailing List
    For list archives: http://lists.mysql.com/replication
    To unsubscribe: http://lists.mysql.com/replication?unsub=mats@sun.com
    --
    Mats Kindahl
    Senior Software Engineer
    Database Technology Group
    Sun Microsystems

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupreplication @
categoriesmysql
postedMar 31, '09 at 9:17p
activeApr 7, '09 at 12:53p
posts7
users4
websitemysql.com
irc#mysql

People

Translate

site design / logo © 2022 Grokbase