On 9/24/2012 10:52 AM, Stillman, Benjamin wrote:
> replicate-same-server-id = 0 keeps MySQL from replicating binary log entries from
> itself. For instance, here's a rough overview:
> You write to Server A.
> Server A writes that to its binary log.
> Server B reads Server A's binary log and completes the same thing.
> Because log-slave-updates is enabled, Server B writes it to its own binary log.
> Server C reads Server B's binary log and completes the same thing.
> Again, with log-slave-updates enabled, Server C writes it to its own binary log.
> Server A reads Server C's binary log.
> Here's where the issue starts. Without replicate-same-server-id = 0, Server A will
> complete the insert/update/delete as it reads it from Server C's binary log. However, this
> query originated from Server A, so it's just going to do it again. Then it's again
> replicated to Server B, Server C, and so on. This can create a loop and/or break
> replication. For instance, if you drop a table on A. It replicates across, and back to A.
> Replication will error out because when it tries to drop the same table again, it already
> doesn't exist. You need replicate-same-server-id = 0 set so that it knows not to execute
> any binary log entries with its own server ID.
Replication, by default, operates with --replicate-same-server-id=0. The
only time you need to change it to a 1 is for certain recovery
scenarios. We added this variable specifically to allow for exceptions
to the rule that every server in a replication chain (or ring) must have
their own, unique, --server-id value.
It's not required for normal operations. In fact we recommend you do not
set it at all. Each server will automatically ignore any event that
originates from a server with the same --server-id setting unless you
specifically set --replicate-same-server-id=1 .
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN