Barking Iguana

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.