Skip navigation links

Additional languages
16.1.2.3 Replication Slave Options and Variables »
Section Navigation      [Toggle]

16.1.2.2. Replication Master Options and Variables

This section describes the options that you can use on master replication servers. You can specify these options either on the command line or in an option file.

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. Example: server-id=3

For options used on the master for controlling binary logging, see Section 16.1.2.4, “Binary Log Options and Variables”.

  • auto_increment_increment

    Version Introduced 5.0.2
    Option Sets Variable Yes, auto_increment_increment
    Variable Name auto_increment_increment
    Variable Scope Both
    Dynamic Variable Yes
    Value Set
    Type numeric
    Default 1
    Range 1-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 non-integer value gives rise to an error, and the actual value of the variable remains unchanged.

    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)

      (Note how SHOW VARIABLES is used here to obtain the current values for these variables.)

    • 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)

      If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

    Should one or both of these variables be 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. In other words, the series is calculated like so:

    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 greatest 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 confine the effects of these two variables to a single table, and thus they do not take the place of the sequences offered by some other database management systems; 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 auto_increment_increment variable was added in MySQL 5.0.2. Its default value is 1. See Section 16.3.1.1, “Replication and AUTO_INCREMENT”.

    auto_increment_increment is supported for use with NDB tables beginning with MySQL 5.0.46. Previously, setting it when using MySQL Cluster tables produced unpredictable results.

  • auto_increment_offset

    Version Introduced 5.0.2
    Option Sets Variable Yes, auto_increment_offset
    Variable Name auto_increment_offset
    Variable Scope Both
    Dynamic Variable Yes
    Value Set
    Type numeric
    Default 1
    Range 1-65535

    This variable was introduced in MySQL 5.0.2. Its default value is 1. For particulars, see the description for auto_increment_increment.

    auto_increment_offset is supported for use with NDB tables beginning with MySQL 5.0.46. Previously, setting it when using MySQL Cluster tables produced unpredictable results.

  • init_slave

    Option Sets Variable Yes, init_slave
    Variable Name init_slave
    Variable Scope Global
    Dynamic Variable Yes
    Value Set
    Type string

    This variable is similar to init_connect, but is a string to be executed by a slave server each time the SQL thread starts. The format of the string is the same as for the init_connect variable.

    Note

    The SQL thread sends an acknowledgement to the client before init_slave is executed. Therefore, it is not guaranteed that init_slave has been executed when START SLAVE returns. See Section 12.6.2.7, “START SLAVE Syntax”, for more information.

  • --show-slave-auth-info

    Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server for slaves started with the --report-user and --report-password options.

  • slave_compressed_protocol

    Option Sets Variable Yes, slave_compressed_protocol
    Variable Name slave_compressed_protocol
    Variable Scope Global
    Dynamic Variable Yes
    Value Set
    Type boolean
    Default FALSE

    Whether to use compression of the slave/master protocol if both the slave and the master support it.

  • slave_load_tmpdir

    Option Sets Variable Yes, slave_load_tmpdir
    Variable Name slave_load_tmpdir
    Variable Scope Global
    Dynamic Variable No
    Value Set
    Type filename
    Default /tmp

    The name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements.

  • slave_net_timeout

    Option Sets Variable Yes, slave_net_timeout
    Variable Name slave_net_timeout
    Variable Scope Global
    Dynamic Variable Yes
    Value Set
    Type numeric
    Default 3600
    Min Value 1

    The number of seconds to wait for more data from a master/slave connection before aborting the read. This timeout applies only to TCP/IP connections, not to connections made via Unix socket files, named pipes, or shared memory.

  • slave_skip_errors

    Option Sets Variable Yes, slave_skip_errors
    Variable Name slave_skip_errors
    Variable Scope Global
    Dynamic Variable No

    Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This variable tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value.

  • slave_transaction_retries

    Version Introduced 5.0.3
    Option Sets Variable Yes, slave_transaction_retries
    Variable Name slave_transaction_retries
    Variable Scope Global
    Dynamic Variable Yes
    Value Set
    Type numeric
    Default 10
    Min Value 0

    If a replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or exceeded InnoDB's innodb_lock_wait_timeout or NDBCLUSTER's TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout, it automatically retries slave_transaction_retries times before stopping with an error. The default prior to MySQL 4.0.3 is 0. You must explicitly set the value greater than 0 to enable the “retry” behavior, which is probably a good idea. In MySQL 5.0.3 or newer, the default is 10.

  • --disconnect-slave-event-count

    Value Set
    Type numeric
    Default 0

    This option is used internally by the MySQL test suite for replication testing and debugging.