Avoiding auto_increment Collision with High Availability MySQL

If you followed my previous post about [high availability MySQL](http://barkingiguana.com/2008/07/07/high-availability-mysql-on-ubuntu-804), your application now has one less single point of failure. That's good. But as [Graeme](http://woss.name/) [pointed out](http://barkingiguana.com/2008/07/07/high-availability-mysql-on-ubuntu-804#c000014), there's a subtle data collision risk if replication breaks. Here's the scenario: replication has stopped, and both db-01 and db-02 receive inserts at roughly the same time. Any `auto_increment` columns will generate the same values on both nodes independently. When replication resumes, those colliding IDs will cause failures. The fix is straightforward. MySQL provides two configuration variables -- `auto-increment-increment` and `auto-increment-offset` -- that control how the next value in an auto-incrementing series is generated. 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 ``` With an increment of 10, db-01 will generate IDs like 1, 11, 21, 31... while db-02 generates 2, 12, 22, 32. They'll never step on each other's toes, even if replication falls behind. And by choosing an increment of 10 rather than 2, you leave room to add more nodes to the cluster later without reconfiguring. Restart MySQL on both boxes and you're protected.