17.1.4.2 Replication Master Options and Variables

This section describes the server options and system variables
that you can use on replication master servers. You can specify
the options either on the
command line or in an
option file. You can specify
system variable values using
SET.

On the master and each slave, you must use the
server-id option to establish a
unique replication ID. For each server, you should pick a unique
positive integer in the range from 1 to
232 – 1, and each ID must be
different from every other ID in use by any other replication
master or slave. Example: server-id=3.

auto_increment_increment
and auto_increment_offset
are intended for use with master-to-master replication, and
can be used to control the operation of
AUTO_INCREMENT columns. Both variables
have global and session values, and each can assume an
integer value between 1 and 65,535 inclusive. Setting the
value of either of these two variables to 0 causes its value
to be set to 1 instead. Attempting to set the value of
either of these two variables to an integer greater than
65,535 or less than 0 causes its value to be set to 65,535
instead. Attempting to set the value of
auto_increment_increment or
auto_increment_offset to a
noninteger value produces an error, and the actual value of
the variable remains unchanged.

auto_increment_offset
determines the starting point for the
AUTO_INCREMENT column value. Consider
the following, assuming that these statements are
executed during the same session as the example given in
the description for
auto_increment_increment:

If either of these variables is changed, and then new rows
inserted into a table containing an
AUTO_INCREMENT column, the results may
seem counterintuitive because the series of
AUTO_INCREMENT values is calculated
without regard to any values already present in the column,
and the next value inserted is the least value in the series
that is greater than the maximum existing value in the
AUTO_INCREMENT column. The series is
calculated like this:

auto_increment_offset +
N ×
auto_increment_increment

where N is a positive integer
value in the series [1, 2, 3, ...]. For example:

The values shown for
auto_increment_increment
and auto_increment_offset
generate the series 5 + N ×
10, that is, [5, 15, 25, 35, 45, ...]. The highest value
present in the col column prior to the
INSERT is 31, and the next
available value in the AUTO_INCREMENT
series is 35, so the inserted values for
col begin at that point and the results
are as shown for the SELECT
query.

It is not possible to restrict the effects of these two
variables to a single table; these variables control the
behavior of all AUTO_INCREMENT columns in
all tables on the MySQL server. If the
global value of either variable is set, its effects persist
until the global value is changed or overridden by setting
the session value, or until mysqld is
restarted. If the local value is set, the new value affects
AUTO_INCREMENT columns for all tables
into which new rows are inserted by the current user for the
duration of the session, unless the values are changed
during that session.