Mysql ID Duplication issue in Replication

Question

I tried implementing MySQL replication with 3 systems.

In my setup I have pairs as follows

                (Master  ,Slave   )
                (System_1,System_2)
                (System_1,System_3)

                (System_2,System_1)
                (System_2,System_3)

                (System_3,System_1)
                (System_3,System_2)

So, every system is master and slave of every other system in this setup.

Every system is having local db as well.

Now in simple case everything is working fine. But in testing i faces an issue which is...

Say at some moment System_3 goes out of connectivity not at this moment all 3 system having some entries till id=3 (primary key and unique).

System_1

id	Value
1	abc
2	xyz
3	pqr

System_2

id	Value
1	abc
2	xyz
3	pqr

System_3

id	Value
1	abc
2	xyz
3	pqr

Now say when System_3 is offline it made some entry that will be going with id 4 in it table locally, in the mean time other two systems namely System_1, System_2 which are online, made some entry that will also go with id 4 in their respective table with replication.

Now say System_3 came online and it will read bin log file of other system and try to insert changes made at other system for replication at id 4. But boom System_3 already have value at id 4. Here replication got stop.

My question is how to solve this problem.

Answer 1

This is a common problem in master-master replication scenarios. One way to deal with it is to give every system a auto_increment_increment of the number of systems.

In combination with auto_increment_offset you can avoid duplicate keys.

This way you can make sure that system_1 only gives ids 3, 6, 9, ... and system_2 2, 5, 8, ... and system_3 1, 4, 7, ...