Article:
  Advanced MySQL Replication Techniques
Subject:   Existing Database & New PK System
Date:   2009-06-28 18:02:01
From:   ChuckPets
I am trying to confirm my understanding that an existing db with many tables should be no problem for this implementation.


Say an existing db is moved to this system, how will the sequencing go ? Say the db has been stopped, replication set up as suggested on 2 or more servers, then re-started. 2 existing tables, last PK on table1 = 3374, last PK on table2 = 6737. Server ID = 10 (auto_increment = 10, offset = 1); Server ID 20 (auto_increment = 10, offset = 2).


My understanding is that next insert on Server ID 10 into table1 will result in a PK of 3385, second insert will have PK = 3395, and so on.


The next insert on Server ID 10 into table2 will result in a PK of 6748, second insert will have PK = 6758, and so on.


And thet next insert on Server ID 20 into table1 will result in a PK of 3386, second insert will have PK = 3396, and so on.


The next insert on Server ID 20 into table2 will result in a PK of 6749, second insert will have PK = 6759, and so on.


And so the 2 servers should never encounter a attempted PK duplication error, no matter what the starting last PK was ?


And so long as these 2 system variables don't change, the individual table AutoInc settings won't interfere ?


Thanks for your time.


Chuck