2.4.2 Replication Master Options and Variables

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 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 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”.

Startup Options for Replication Masters

The following list describes startup options for controlling replication master 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 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.

System Variables Used on Replication Masters

The following system variables are used to control replication masters:

• 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 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 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 master. 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 master-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 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
  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 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
  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 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.