Home User Manual Discussion Forum Search

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.

  • auto_increment_increment: Controls the increment between successive AUTO_INCREMENT values.
  • auto_increment_offset: Determines the starting point for AUTO_INCREMENT column values.

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:

  • Change "server-id" as needed. It must be unique for each replication server.
  • Change "replicate-do-db" to the name of the database to replicate.
  • Change "relay-log" to include the name of the server (e.g. relay-log=LocalComputerIPAddressorName-relay-bin)
  • Change "auto_increment_increment" to equal the total number of servers (e.g. 3).
  • Change "auto_increment_offset" as needed, using a unique value on each server that doesn't exceed the auto_increment_increment value (e.g. 1, 2, or 3).

auto-increment example if you have 3 servers:

  • server 1: auto_increment_increment = 3, auto_increment_offset = 1
  • server 2: auto_increment_increment = 3, auto_increment_offset = 2
  • server 3: auto_increment_increment = 3, auto_increment_offset = 3

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.

 

Open Dental Software 1-503-363-5432