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 2.4.4, “Binary Log Options and Variables”.
The following list describes startup options for controlling replication master servers. Replication-related system variables are discussed later in this section.
-
Property Value Command-Line Format --show-slave-auth-info
Type Boolean Default Value FALSE
Display slave user names and passwords in the output of
SHOW SLAVE HOSTS
on the master server for slaves started with the--report-user
and--report-password
options.
The following system variables are used to control replication masters:
-
Property Value 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 master-to-master 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”. -
Property Value 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. -
Property Value System Variable rpl_semi_sync_master_enabled
Scope Global Dynamic Yes Type Boolean Default Value OFF
Controls whether semisynchronous replication is enabled on the master. 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 master-side semisynchronous replication plugin is installed.
-
Property Value 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 master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).
This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_trace_level
Property Value 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 master. 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 master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_wait_no_slave
Property Value 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 master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.
If the value is
ON
(the default), it is permissible for the slave count to drop to zero during the timeout period (for example, if slaves disconnect). The master still waits for the timeout, so as long as some slave reconnects and acknowledges the transaction within the timeout interval, semisynchronous replication continues.If the value is
OFF
, the master reverts to normal replication if the slave count drops to zero during the timeout period.This variable is available only if the master-side semisynchronous replication plugin is installed.