Replication: Setting Auto Increment / Offset Variables

When using Replication it is important to avoid data collision. MySQL provides variables to avoid conflicts: auto_increment_increment and auto_increment_offset. By choosing non-conflicting values for these variables, servers will not have conflicts when inserting new rows into the same table.

For more details: https://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html

Update the my.ini File

To implement this strategy, add the following lines to the my.ini file on each replication server during One-Way Replication Setup or Daisy Chain Replication Setup (step 5). The my.ini file is typically located in C:\Program Files (x86)\MySQL\MySQL Server 5.5.

log-bin=mysql-bin

server-id=X

log-slave-updates

replicate-do-db=opendental

relay-log=server-relay-bin

skip-name-resolve

auto_increment_increment=Y

auto_increment_offset=Z

Change the following values:

auto-increment example if you have 3 servers:

Whenever you add or remove a server for daisy chain replication, you must also change the auto_increment_increment value on all servers. See Daisy Chain Replication: Adding a New Server.