List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:October 27 2011 5:22pm
Subject:Re: Master-Master -> duplicate entry
View as plain text  
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/



Thread
Master-Master -> duplicate entryRicardo Freitas26 Oct
  • Re: Master-Master -> duplicate entrynavid@navid.it26 Oct
    • Re: Master-Master -> duplicate entryRicardo26 Oct
  • Re: Master-Master -> duplicate entryRick James27 Oct
    • Re: Master-Master -> duplicate entryRicardo27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryJohan De Meersman27 Oct
          • Re: Master-Master -> duplicate entryRicardo27 Oct
          • Re: Master-Master -> duplicate entryRicardo27 Oct
          • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
            • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
              • Re: Master-Master -> duplicate entrynavid@navid.it27 Oct
              • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                  • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                    • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                    • Re: Master-Master -> duplicate entryRicardo27 Oct
                      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                        • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                          • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                            • Re: Master-Master -> duplicate entry (UUID lecture)Rick James27 Oct
            • Re: Master-Master -> duplicate entryJohan De Meersman28 Oct
        • Re: Master-Master -> duplicate entryRicardo27 Oct
  • Re: Master-Master -> duplicate entrylist27 Oct
    • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryMySQL)27 Oct
    • Re: Master-Master -> duplicate entryRick James27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryRick James27 Oct
          • Re: Master-Master -> duplicate entryJohan De Meersman28 Oct
            • Re: Master-Master -> duplicate entryRick James28 Oct
              • Re: Master-Master -> duplicate entryMats Kindahl31 Oct
      • Re: Master-Master -> duplicate entrylist27 Oct