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:

• MySQL Users: https://dev.mysql.com/doc/refman/5.6/en/replication-options-source.html
• MariaDB Users: https://mariadb.com/kb/en/replication-and-binary-log-system-variables

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:

• 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).
• Change innodb-flush-log-at-trx-commit=1 if all tables are all InnoDB by removing the # and the comment after the value.
• Change expire_logs_days to increase or decrease the number of days after which the binary log can be automatically removed. Removals take place when the server starts up, when the binary log is flushed, when the next binary log is created after the previous one reaches the maximum size, or when running PURGE BINARY LOGS.

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