Avoiding Primary Key Collision

In order to deploy MySQL replication in master-master setup, one has to adjust the auto increment value to avoid primary key collision for INSERT between two or more replicating Primaries. This allows the primary key value on Primaries to interleave each other and prevent the same auto increment number being used twice on either of the node. This behavior must be configured manually, depending on the number of Primaries in the replication setup. The value of auto_increment_increment equals to the number of replicating Primaries and the auto_increment_offset must be unique between them. For example, the following lines should exist inside the corresponding my.cnf.

In a Primary - Replica setup, this variable does not need to be set.

Primary 1

log-slave-updates
auto_increment_increment=2
auto_increment_offset=1

Primary 2

log-slave-updates
auto_increment_increment=2
auto_increment_offset=2