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.
Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/
