How To Avoid Auto-Increment Collisions During Multi-Master Replication

Author: erics, Posted September 25th, 2013 at 1:52:57am

If you are inserting to the same table at the same time at two or more different sites, and using bi-directional or multi-master replication, then the auto-increment keys need to be offset so they do not conflict or the application needs to be be able to generate it’s own unique keys.

Use the auto-increment-increment and auto-increment-offset variables to affect the way that MySQL generates the next value in an auto-increment field.

For example, edit /etc/mysql/my.cnf on all servers:

# site 1
auto-increment-increment = 10
auto-increment-offset = 1

# site 2
auto-increment-increment = 10
auto-increment-offset = 2

# site 3
auto-increment-increment = 10
auto-increment-offset = 3

Restart MySQL on all servers.

Categories: How-To's, Technology Tags: Auto, Auto-Increment, Avoid, Collision, Increment, Master, Master-Master, mysql, Offset, Replication