Replication Auto Increment

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:

Update the my.ini File

To implement this strategy, add the following lines to the [mysqld] section of the my.ini file on each replication server during One-Way Replication or Daisy Chain Replication (step 5).

log-bin=mysql-bin
server-id=X
log-slave-updates
replicate-do-db=opendental
relay-log=server-relay-bin
skip-name-resolve
sync-binlog=1
auto_increment_increment=Y
auto_increment_offset=Z
#If using InnoDB, remove the hashtag for the line below
#innodb-flush-log-at-trx-commit=1
#If using MariaDB, remove the hashtag for the line below
#expire_logs_days=30


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: Add Server.