Avoiding auto_increment collision with High Availability MySQL

If you followed my previous post about high availability MySQL your application now has one less single point of failure. That's good, but as Graeme points out there's a possibility of data collision if the replication process fails.

If replication has stopped and a query inserts into db-01 while a second query inserts into db-02 then the value of any auto_increment columns will be the same. When you get replication running again this will cause a problem.

To avoid this situation we can use auto-increment-increment and auto-increment-offset. These variables affect the way that MySQL generates the next value in an auto-incrementing series.

# On db-01, in /etc/mysql/my.cnf
auto-increment-increment = 10
auto-increment-offset = 1

# On db-02, in /etc/mysql/my.cnf
auto-increment-increment = 10
auto-increment-offset = 2

Restart MySQL on both boxes and you should now be safe from this threat of data collision.

Love me!

If you've found this article useful I'd appreciate beer and / or recommendations at Working With Rails.