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

Published Date Author: , 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.

No comments as yet.

Leave Your Comment  Leave a comment

All fields marked with "*" are required.