On 10/27/2011 13:22, Marcus Bointon wrote:
> On 27 Oct 2011, at 18:47, Ricardo Freitas wrote:
>> Remember the Master_2 is empty. When the record is replicated to the Master_2,
> the id of the pk passed on is 177 or does the Master 2 ignore this factor and just inserts
> the record with a autoinc value of 1?
>> From what I learned from these precious exchange of e-mails, it will just ignore
> wherever the autoinc value is and save the autoinc value from the Master 1.
>> Is this correct? If so, I can finally understand why this duplicate
> inconsistencies occur, otherwise I still don't understand:)
> You shouldn't be starting replication on an empty server. The current autoinc value
> is effectively part of the table definition (notice it's in the output of 'show create
> table') so it travels with the table. So when you import this table it may have the
> current autoinc of 177, and the primary might assign 178 as the next pk, but your slave
> (with a bigger offset) might assign 179, thus you won't get a clash.
> Normal practice is to take a snapshot at a known binlog position, restore that
> snapshot on the new slave, start replication from the point that you got to in the binlog
> and let it catch up via replication. When it's in sync, (and no writes are going to the
> second master), you can close the replication loop by setting up replication in the other
> direction. Percona's Xtrabackup provides a really effective way of doing this with no
> downtime or locks.
The other thing to remember is that the auto_increment value to assign
to the new row is actually part of the replication stream. If it wasn't,
there would be no way to have a duplicate entry conflict. The receiving
server would have simply assigned the next available number.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN