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 2.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
andauto_increment_offset
are intended for use with source-to-source replication, and can be used to control the operation ofAUTO_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 ofauto_increment_increment
orauto_increment_offset
to a noninteger value produces an error, and the actual value of the variable remains unchanged.Noteauto_increment_increment
is also supported for use withNDB
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 theAUTO_INCREMENT
column value. Consider the following, assuming that these statements are executed during the same session as the example given in the description forauto_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 ofauto_increment_increment
, the value ofauto_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 ofAUTO_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 theAUTO_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
andauto_increment_offset
generate the series 5 +N
× 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in thecol
column prior to theINSERT
is 31, and the next available value in theAUTO_INCREMENT
series is 35, so the inserted values forcol
begin at that point and the results are as shown for theSELECT
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 affectsAUTO_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 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
.Noteauto_increment_offset
is also supported for use withNDB
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
orOFF
(or 1 or 0), respectively. The default isOFF
.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
Minimum Value 0
Maximum Value 4294967295
Unit milliseconds 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
Minimum Value 0
Maximum Value 4294967295
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.