Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.0Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 286.0Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

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

System Variables Used on Replication Masters

The following system variables are used to control replication masters:

  • auto_increment_increment

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

  • auto_increment_offset

    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.

    Note

    auto_increment_offset is also supported for use with NDB tables.

  • rpl_semi_sync_master_enabled

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


User Comments
Sign Up Login You must be logged in to post a comment.