Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




6.15. Auto-Increment in Multiple-Master Replication

When multiple servers are configured as replication masters, special steps must be taken to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows.

The auto_increment_increment and auto_increment_offset system variables help to accommodate multiple-master replication with AUTO_INCREMENT columns. Each of these variables has a default and minimum value of 1, and a maximum value of 65,535.

These two variables effect AUTO_INCREMENT column behavior as follows:

  • auto_increment_increment controls the increment between successive AUTO_INCREMENT values.

  • auto_increment_offset determines the starting point for AUTO_INCREMENT column values.

By choosing non-conflicting values for these variables on different masters, servers in a multiple-master configuration will not use conflicting AUTO_INCREMENT values when inserting new rows into the same table. To set up N master servers, set the variables like this:

  • Set auto_increment_increment to N on each master.

  • Set each of the N masters to have a different auto_increment_offset, using the values 1, 2, …, N.

For additional information about auto_increment_increment and auto_increment_offset, see Section 5.2.2, “Server System Variables”.

  Published under the terms of the GNU General Public License Design by Interspire