MySQL Configuration for Active/Active Deployments

If you are inserting to the same table at the same time at two or more different sites, and using bi-directional or active/active replication, then special care must be taken to avoid primary key conflicts. Either the auto-increment keys on each need to be offset so they do not conflict, or the application needs to be be able to generate unique keys taking multiple sites into account.

important

The following configuration is required if your application is relying upon the MySQL-native auto-increment primary key feature.

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 my.cnf on all servers:

# for all servers at site 1

auto-increment-increment = 10
auto-increment-offset = 1

# for all servers at site 2

auto-increment-increment = 10
auto-increment-offset = 2

# for all servers at site 3

auto-increment-increment = 10
auto-increment-offset = 3