Home User Manual Discussion Forum Search

Replication: Setting Auto Increment / Off Set 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 use 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: http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_increment

Steps
To implement this strategy you will change the ini file (step 5).

  • Set auto_increment_increment to N. (N=number of servers)
  • Set each of the N servers to have a different auto_increment_offset (1, 2, 3, using the values 1, 2, ... , N.

Add the following lines to the "my.ini" file (C:\Program Files (x86)\MySQL\MySQL Server 5.5):
log-bin=mysql-bin
server-id=2
log-slave-updates
replicate-do-db=opendental
relay-log=server-relay-bin
skip-name-resolve

auto_increment_increment=N
auto_increment_offset=1


Change the "server-id" as needed. The server ID must be unique for each replication server.
Change the "replicate-do-db" to the name of the database to replicate.
Change the "relay-log" value to include the name of the server. ex) relay-log=SlaveComputer-relay-bin

Change auto_increment_increment to equal the total number of servers (e.g. 3).
Change auto_increment_offset to a different value on each server, but don't exceed the N value (e.g. 1, 2, 3).

Example:
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

If you add another server, change the auto_increment_increment on all servers. See Adding a New Server. It is best to alter the session table on all slaves to make the next id be greater than the id allocated by any of the slaves to date.

 

Open Dental Software 1-503-363-5432