MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
This section describes the server options and system variables
that you can use on replication source 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 source and each replica, you must set the
server_id
system variable 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
source server or replica. Example: server-id=3
.
For options used on the source for controlling binary logging, see Section 17.1.4.4, “Binary Log Options and Variables”.
The following list describes startup options for controlling replication source servers. Replication-related system variables are discussed later in this section.
Command-Line Format | --show-slave-auth-info[={OFF|ON}] |
---|---|
Type | Boolean |
Default Value | OFF |
Display replica user names and passwords in the output of
SHOW SLAVE HOSTS
on the
replication source server for replicas started with the
--report-user
and
--report-password
options.
The following system variables are used to control replication source servers:
Command-Line Format | --auto-increment-increment=# |
---|---|
System Variable | auto_increment_increment |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value | 65535 |
auto_increment_increment
and auto_increment_offset
are intended for use with source-to-source 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_increment
is also supported for use with
NDB
tables.
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)
When the value of
auto_increment_offset
is greater than that of
auto_increment_increment
,
the value of
auto_increment_offset
is ignored.
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:
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 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.
The default value of
auto_increment_increment
is
1. See
Section 17.4.1.1, “Replication and AUTO_INCREMENT”.
Command-Line Format | --auto-increment-offset=# |
---|---|
System Variable | auto_increment_offset |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 1 |
Maximum Value | 65535 |
This variable has a default value of 1. For more
information, see the description for
auto_increment_increment
.
auto_increment_offset
is also supported
for use with NDB
tables.
Command-Line Format | --rpl-semi-sync-master-enabled[={OFF|ON}] |
---|---|
System Variable | rpl_semi_sync_master_enabled |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Controls whether semisynchronous replication is enabled on
the source. To enable or disable the plugin, set this
variable to ON
or OFF
(or 1 or 0), respectively. The default is
OFF
.
This variable is available only if the source-side semisynchronous replication plugin is installed.
Command-Line Format | --rpl-semi-sync-master-timeout=# |
---|---|
System Variable | rpl_semi_sync_master_timeout |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 10000 |
A value in milliseconds that controls how long the source waits on a commit for acknowledgment from a replica before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).
This variable is available only if the source-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_trace_level
Command-Line Format | --rpl-semi-sync-master-trace-level=# |
---|---|
System Variable | rpl_semi_sync_master_trace_level |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 32 |
The semisynchronous replication debug trace level on the source. Four levels are defined:
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
This variable is available only if the source-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_wait_no_slave
Command-Line Format | --rpl-semi-sync-master-wait-no-slave[={OFF|ON}] |
---|---|
System Variable | rpl_semi_sync_master_wait_no_slave |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
With semisynchronous replication, for each transaction, the source waits until timeout for acknowledgment of receipt from some semisynchronous replica. If no response occurs during this period, the source reverts to normal replication. This variable controls whether the source waits for the timeout to expire before reverting to normal replication even if the replica count drops to zero during the timeout period.
If the value is ON
(the default), it is
permissible for the replica count to drop to zero during the
timeout period (for example, if replicas disconnect). The
source still waits for the timeout, so as long as some
replica reconnects and acknowledges the transaction within
the timeout interval, semisynchronous replication continues.
If the value is OFF
, the source reverts
to normal replication if the replica count drops to zero
during the timeout period.
This variable is available only if the source-side semisynchronous replication plugin is installed.