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
.
For options used on the master for controlling binary logging, see Section 16.1.3.4, “Binary Log Options and Variables”.
Command-Line Format | --auto_increment_increment[=#] |
|
Config-File Format | auto_increment_increment |
|
Option Sets Variable | Yes, auto_increment_increment
|
|
Variable Name | auto_increment_increment |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1 |
|
Range | 1-65535 |
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 gives rise to an error, and the actual value
of the variable remains unchanged.
auto_increment_increment
is
supported for use with NDB
tables beginning with MySQL 5.1.20, MySQL Cluster NDB 6.2.5,
and MySQL Cluster NDB 6.3.2. Previously, setting it when
using MySQL Cluster tables or MySQL Cluster Replication
produced unpredictable results.
These two variables affect AUTO_INCREMENT
column behavior as follows:
auto_increment_increment
controls the interval between successive column values.
For example:
mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql>CREATE TABLE autoinc1
->(col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec) mysql>SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec) mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec) mysql>INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>SELECT col FROM autoinc1;
+-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | +-----+ 4 rows in set (0.00 sec)
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
:
mysql>SET @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec) mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 5 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql>CREATE TABLE autoinc2
->(col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec) mysql>INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>SELECT col FROM autoinc2;
+-----+ | col | +-----+ | 5 | | 15 | | 25 | | 35 | +-----+ 4 rows in set (0.02 sec)
If the value of
auto_increment_offset
is
greater than that of
auto_increment_increment
,
the value of
auto_increment_offset
is
ignored.
Should one or both of these variables be 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. In other words, the
series is calculated like so:
auto_increment_offset +
N
× auto_increment_increment
where N
is a positive integer value
in the series [1, 2, 3, ...]. For example:
mysql>SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 5 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql>SELECT col FROM autoinc1;
+-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | +-----+ 4 rows in set (0.00 sec) mysql>INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>SELECT col FROM autoinc1;
+-----+ | col | +-----+ | 1 | | 11 | | 21 | | 31 | | 35 | | 45 | | 55 | | 65 | +-----+ 8 rows in set (0.00 sec)
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 greatest 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 confine the effects of these two
variables to a single table, and thus they do not take the
place of the sequences offered by some other database
management systems; 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.
The default value of
auto_increment_increment
is
1. See Section 16.4.1.1, “Replication and AUTO_INCREMENT
”.
Command-Line Format | --auto_increment_offset[=#] |
|
Config-File Format | auto_increment_offset |
|
Option Sets Variable | Yes, auto_increment_offset
|
|
Variable Name | auto_increment_offset |
|
Variable Scope | Both | |
Dynamic Variable | Yes | |
Permitted Values | ||
Type | numeric |
|
Default | 1 |
|
Range | 1-65535 |
This variable has a default value of 1. For particulars, see
the description for
auto_increment_increment
.
auto_increment_offset
is
supported for use with NDB
tables beginning with MySQL 5.1.20, MySQL Cluster NDB 6.2.5,
and MySQL Cluster NDB 6.3.2. Previously, setting it when
using MySQL Cluster tables or MySQL Cluster Replication
produced unpredictable results.
User Comments
Add your own comment.