2.4.2 Replication Source Options and Variables

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 2³² − 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”.

Startup Options for Replication Source Servers

The following list describes startup options for controlling replication source servers. Replication-related system variables are discussed later in this section.

• --show-slave-auth-info

  Property	Value
  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.

System Variables Used on Replication Sources

The following system variables are used to control replication source servers:

• auto_increment_increment

  Property	Value
  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.

  Note

  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 4.1.1, “Replication and AUTO_INCREMENT”.

• auto_increment_offset

  Property	Value
  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.

  Note

  auto_increment_offset is also supported for use with NDB tables.

• rpl_semi_sync_master_enabled

  Property	Value
  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.

• rpl_semi_sync_master_timeout

  Property	Value
  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

  Property	Value
  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

  Property	Value
  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.