16.1.2.3 Replication Slave Options and Variables

Startup Options for Replication Slaves

System Variables Used on Replication Slaves

This section describes the server options and system variables that apply to slave replication servers. You can specify the options either on the command line or in an option file. Many of the options can be set while the server is running by using the CHANGE MASTER TO statement. You can specify system variable values using SET.

Server ID.  On the master and each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 232 – 1. Unique means that each ID must be different from every other ID in use by any other replication master or slave. Example my.cnf file:

[mysqld]
server-id=3

Some slave server replication options are handled in a special way, in the sense that each is ignored if a master.info file exists when the slave starts and contains a value for the option. The following options are handled this way:

The master.info file format in MySQL 5.0 includes as its first line the number of lines in the file. (See Section 16.2.2, “Replication Relay and Status Logs”.) If you upgrade an older server (before MySQL 4.1.1) to a newer version, the new server upgrades the master.info file to the new format automatically when it starts. However, if you downgrade a newer server to a version older than 4.1.1, you should manually remove the first line before starting the older server for the first time. Note that, in this case, the downgraded server can no longer use an SSL connection to communicate with the master.

If no master.info file exists when the slave server starts, it uses the values for those options that are specified in option files or on the command line. This occurs when you start the server as a replication slave for the very first time, or when you have run RESET SLAVE and then have shut down and restarted the slave.

If the master.info file exists when the slave server starts, the server uses its contents and ignores any startup options that correspond to the values listed in the file. Thus, if you start the slave server with different values of the startup options that correspond to values in the master.info file, the different values have no effect because the server continues to use the master.info file. To use different values, the preferred method is to use the CHANGE MASTER TO statement to reset the values while the slave is running. Alternatively, you can stop the server, remove the master.info file, and restart the server with different option values.

Suppose that you specify this option in your my.cnf file:

[mysqld]
master-host=some_host

The first time you start the server as a replication slave, it reads and uses that option from the my.cnf file. The server then records the value in the master.info file. The next time you start the server, it reads the master host value from the master.info file only and ignores the value in the option file. If you modify the my.cnf file to specify a different master host of some_other_host, the change still has no effect. You should use CHANGE MASTER TO instead.

Because the server gives an existing master.info file precedence over the startup options just described, you might prefer not to use startup options for these values at all, and instead specify them by using the CHANGE MASTER TO statement. See Section 13.4.2.1, “CHANGE MASTER TO Syntax”.

This example shows a more extensive use of startup options to configure a slave server:

[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=pertinax
master-password=freitag
master-connect-retry=60
report-host=db-slave.mycompany.com
Startup Options for Replication Slaves

The following list describes startup options for controlling replication slave servers. Many of these options can be set while the server is running by using the CHANGE MASTER TO statement. Others, such as the --replicate-* options, can be set only when the slave server starts. Replication-related system variables are discussed later in this section.

  • --abort-slave-event-count

    Command-Line Format--abort-slave-event-count=#
     Permitted Values
    Typenumeric
    Default0
    Min Value0

    When this option is set to some positive integer value other than 0 (the default) it affects replication behavior as follows: After the slave SQL thread has started, value log events are permitted to be executed; after that, the slave SQL thread does not receive any more events, just as if the network connection from the master were cut. The slave thread continues to run, and the output from SHOW SLAVE STATUS displays Yes in both the Slave_IO_Running and the Slave_SQL_Running columns, but no further events are read from the relay log.

    This option is used internally by the MySQL test suite for replication testing and debugging. It is not intended for use in a production setting.

  • --disconnect-slave-event-count

    Command-Line Format--disconnect-slave-event-count=#
     Permitted Values
    Typenumeric
    Default0

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

  • --log-slave-updates

    Command-Line Format--log-slave-updates
    System Variable Namelog_slave_updates
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultOFF

    Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

    A -> B -> C
    

    Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

  • --log-warnings[=level]

    Command-Line Format--log-warnings[=#]
     -W [#]
    System Variable Namelog_warnings
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default1
    Min Value0
    Max Value18446744073709547520

    This option causes a server to print more messages to the error log about what it is doing. With respect to replication, the server generates warnings that it succeeded in reconnecting after a network/connection failure, and informs you as to how each slave thread started. This option is enabled (1) by default; to disable it, use --log-warnings=0. Aborted connections are not logged to the error log unless the value is greater than 1.

    Note that the effects of this option are not limited to replication. It produces warnings across a spectrum of server activities.

  • --master-connect-retry=seconds

    Command-Line Format--master-connect-retry=#
     Permitted Values
    Typenumeric
    Default60

    The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of --slave-net-timeout. The number of reconnection attempts is limited by the --master-retry-count option.

  • --master-host=host_name

    Command-Line Format--master-host=name
     Permitted Values
    Typestring

    The host name or IP address of the master replication server. The value in master.info takes precedence if it can be read. If no master host is specified, the slave thread does not start.

  • --master-info-file=file_name

    Command-Line Format--master-info-file=file_name
     Permitted Values
    Typefile name
    Defaultmaster.info

    The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory. For information about the format of this file, see Section 16.2.2.2, “Slave Status Logs”.

  • --master-password=password

    Command-Line Format--master-password=name
     Permitted Values
    Typestring

    The password of the account that the slave thread uses for authentication when it connects to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.

  • --master-port=port_number

    Command-Line Format--master-port=#
     Permitted Values
    Typenumeric
    Default3306

    The TCP/IP port number that the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).

  • --master-retry-count=count

    Command-Line Format--master-retry-count=#
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default86400
    Min Value0
    Max Value4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default86400
    Min Value0
    Max Value18446744073709551615

    The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the --master-connect-retry option (or the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement) and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400. A value of 0 means infinite; the slave attempts to connect forever.

  • --master-ssl, --master-ssl-ca=file_name, --master-ssl-capath=directory_name, --master-ssl-cert=file_name, --master-ssl-cipher=cipher_list, --master-ssl-key=file_name

    These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options that are described in Section 6.3.6.4, “SSL Command Options”. The values in the master.info file take precedence if they can be read.

  • --master-user=user_name

    Command-Line Format--master-user=name
     Permitted Values
    Typestring
    Defaulttest

    The user name of the account that the slave thread uses for authentication when it connects to the master. This account must have the REPLICATION SLAVE privilege. The value in the master.info file takes precedence if it can be read. If the master user name is not set, the name test is assumed.

  • --max-relay-log-size=size

    Command-Line Format--max_relay_log_size=#
    System Variable Namemax_relay_log_size
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default0
    Min Value0
    Max Value1073741824

    The size at which the server rotates relay log files automatically. If this value is nonzero, the relay log is rotated automatically when its size exceeds this value. If this value is zero (the default), the size at which relay log rotation occurs is determined by the value of max_binlog_size. For more information, see Section 16.2.2.1, “The Slave Relay Log”.

  • --read-only

    Command-Line Format--read-only
    System Variable Nameread_only
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    Defaultfalse

    Cause the slave to permit no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables.

  • --relay-log=file_name

    Command-Line Format--relay-log=file_name
    System Variable Namerelay_log
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    The basename for the relay log. The default basename is host_name-relay-bin. The server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory. The server creates relay log files in sequence by adding a numeric suffix to the basename.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.

    If you specify this option, the value specified is also used as the basename for the relay log index file. You can override this behavior by specifying a different relay log index file basename using the --relay-log-index option.

    You may find the --relay-log option useful in performing the following tasks:

    • Creating relay logs whose names are independent of host names.

    • If you need to put the relay logs in some area other than the data directory because your relay logs tend to be very large and you do not want to decrease max_relay_log_size.

    • To increase speed by using load-balancing between disks.

  • --relay-log-index=file_name

    Command-Line Format--relay-log-index=file_name
    System Variable Namerelay_log_index
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    The name to use for the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log-index option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.

    If you specify this option, the value specified is also used as the basename for the relay logs. You can override this behavior by specifying a different relay log file basename using the --relay-log option.

  • --relay-log-info-file=file_name

    Command-Line Format--relay-log-info-file=file_name
     Permitted Values
    Typefile name
    Defaultrelay-log.info

    The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory. For information about the format of this file, see Section 16.2.2.2, “Slave Status Logs”.

  • --relay-log-purge={0|1}

    Command-Line Format--relay_log_purge
    System Variable Namerelay_log_purge
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultTRUE

    Disable or enable automatic purging of relay logs as soon as they are no longer needed. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N.

  • --relay-log-space-limit=size

    Command-Line Format--relay_log_space_limit=#
    System Variable Namerelay_log_space_limit
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default0
    Min Value0
    Max Value4294967295

    This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means no limit. This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs because not doing so would cause a deadlock. You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to ignore --relay-log-space-limit temporarily.

  • --replicate-do-db=db_name

    Command-Line Format--replicate-do-db=name
     Permitted Values
    Typestring

    Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database.

    Warning

    To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

    An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    

    The main reason for this check just the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE or multiple-table UPDATE statements that go across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

    If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    Beginning with MySQL 5.0.84, this option has no effect on BEGIN, COMMIT, or ROLLBACK statements. (Bug #43263)

  • --replicate-ignore-db=db_name

    Command-Line Format--replicate-ignore-db=name
     Permitted Values
    Typestring

    Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name. To specify more than one database to ignore, use this option multiple times, once for each database. You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    An example of what does not work as you might expect: If the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master, the UPDATE statement is replicated:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    
    Note

    In the preceding example the statement is replicated because --replicate-ignore-db only applies to the default database (set through the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered.

    If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    Beginning with MySQL 5.0.84, this option has no effect on BEGIN, COMMIT, or ROLLBACK statements. (Bug #43263)

  • --replicate-do-table=db_name.tbl_name

    Command-Line Format--replicate-do-table=name
     Permitted Values
    Typestring

    Tells the slave SQL thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for both cross-database updates and default database updates, in contrast to --replicate-do-db. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

  • --replicate-ignore-table=db_name.tbl_name

    Command-Line Format--replicate-ignore-table=name
     Permitted Values
    Typestring

    Tells the slave SQL thread not to replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-ignore-db. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

  • --replicate-rewrite-db=from_name->to_name

    Command-Line Format--replicate-rewrite-db=old_name->new_name
     Permitted Values
    Typestring

    Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. To specify multiple rewrites, use this option multiple times. The server uses the first one with a from_name value that matches. The database name translation is done before the --replicate-* rules are tested.

    Statements in which table names are qualified with database names when using this option do not work with table-level replication filtering options such as --replicate-do-table. Suppose we have a database named a on the master, one named b on the slave, each containing a table t, and have started the master with --replicate-rewrite-db='a->b'. At a later point in time, we execute DELETE FROM a.t. In this case, no relevant filtering rule works, for the reasons shown here:

    1. --replicate-do-table=a.t does not work because the slave has table t in database b.

    2. --replicate-do-table=b.t does not match the original statement and so is ignored.

    3. --replicate-do-table=*.t is handled identically to --replicate-do-table=a.t, and thus does not work, either.

    Similarly, the --replication-rewrite-db option does not work with cross-database updates.

    If you use this option on the command line and the > character is special to your command interpreter, quote the option value. For example:

    shell> mysqld --replicate-rewrite-db="olddb->newdb"
    
  • --replicate-same-server-id

    Introduced5.0.1
    Command-Line Format--replicate-same-server-id
     Permitted Values
    Typeboolean
    DefaultFALSE

    To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if --log-slave-updates is used. By default, the slave I/O thread does not write binary log events to the relay log if they have the slave's server ID (this optimization helps save disk usage). If you want to use --replicate-same-server-id, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.

  • --replicate-wild-do-table=db_name.tbl_name

    Command-Line Format--replicate-wild-do-table=name
     Permitted Values
    Typestring

    Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the % and _ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    This option applies to tables, views, and triggers. It does not apply to stored procedures and functions. To filter statements operating on the latter objects, use one or more of the --replicate-*-db options.

    Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

    If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements are replicated if the database name matches the pattern foo%.

    To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own%db, but not replicate tables from the my1ownAABCdb database, you should escape the _ and % characters like this: --replicate-wild-do-table=my\_own\%db. If you use the option on the command line, you might need to double the backslashes or quote the option value, depending on your command interpreter. For example, with the bash shell, you would need to type --replicate-wild-do-table=my\\_own\\%db.

  • --replicate-wild-ignore-table=db_name.tbl_name

    Command-Line Format--replicate-wild-ignore-table=name
     Permitted Values
    Typestring

    Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

    Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.

    For information about how matching works, see the description of the --replicate-wild-do-table option. The rules for including literal wildcard characters in the option value are the same as for --replicate-wild-ignore-table as well.

  • --report-host=host_name

    Command-Line Format--report-host=host_name
    System Variable Namereport_host
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typestring

    The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server. Leave the value unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP address of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.

  • --report-password=password

    Command-Line Format--report-password=name
    System Variable Namereport_password
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typestring

    The account password of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.

    Although the name of this option might imply otherwise, --report-password is not connected to the MySQL user privilege system and so is not necessarily (or even likely to be) the same as the password for the MySQL replication user account.

  • --report-port=slave_port_num

    Command-Line Format--report-port=#
    System Variable Namereport_port
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default3306
    Min Value0
    Max Value65535

    The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a nondefault port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.

  • --report-user=user_name

    Command-Line Format--report-user=name
    System Variable Namereport_user
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typestring

    The account user name of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.

    Although the name of this option might imply otherwise, --report-user is not connected to the MySQL user privilege system and so is not necessarily (or even likely to be) the same as the name of the MySQL replication user account.

  • --show-slave-auth-info

    Command-Line Format--show-slave-auth-info
     Permitted Values
    Typeboolean
    DefaultFALSE

    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.

  • --skip-slave-start

    Command-Line Format--skip-slave-start
     Permitted Values
    Typeboolean
    DefaultFALSE

    Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.

  • --slave_compressed_protocol={0|1}

    Command-Line Format--slave_compressed_protocol
    System Variable Nameslave_compressed_protocol
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultOFF

    If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

  • --slave-load-tmpdir=file_name

    Command-Line Format--slave-load-tmpdir=path
    System Variable Nameslave_load_tmpdir
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typedirectory name
    Default/tmp

    The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some file system that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the --relay-log option to place the relay logs in that file system.

    The directory specified by this option should be located in a disk-based file system (not a memory-based file system) because the temporary files used to replicate LOAD DATA INFILE must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.

  • --slave-net-timeout=seconds

    Command-Line Format--slave-net-timeout=#
    System Variable Nameslave_net_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default3600
    Min Value1

    The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the CHANGE MASTER TO statement or --master-connect-retry option and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

  • --slave-skip-errors=[err_code1,err_code2,...|all]

    Command-Line Format--slave-skip-errors=name
    System Variable Nameslave_skip_errors
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all

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

    Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

    For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.

    You can also (but should not) use the very nonrecommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.

    Examples:

    --slave-skip-errors=1062,1053
    --slave-skip-errors=all
    
System Variables Used on Replication Slaves

The following list describes system variables for controlling replication slave servers. They can be set at server startup and some of them can be changed at runtime using SET. Server options used with replication slaves are listed earlier in this section.

  • init_slave

    Command-Line Format--init-slave=name
    System Variable Nameinit_slave
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typestring

    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 acknowledgment to the client before it executes init_slave. Therefore, it is not guaranteed that init_slave has been executed when START SLAVE returns. See Section 13.4.2.7, “START SLAVE Syntax”, for more information.

  • relay_log

    Command-Line Format--relay-log=file_name
    System Variable Namerelay_log
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    The name of the relay log file.

  • relay_log_index

    Command-Line Format--relay-log-index
    System Variable Namerelay_log_index
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name
    Default*host_name*-relay-bin.index

    The name of the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

  • relay_log_info_file

    Command-Line Format--relay-log-info-file=file_name
    System Variable Namerelay_log_info_file
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name
    Defaultrelay-log.info

    The name of the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory.

  • rpl_recovery_rank

    This variable is unused, and is removed in MySQL 5.6.

  • slave_compressed_protocol

    Command-Line Format--slave_compressed_protocol
    System Variable Nameslave_compressed_protocol
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultOFF

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

  • slave_load_tmpdir

    Command-Line Format--slave-load-tmpdir=path
    System Variable Nameslave_load_tmpdir
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typedirectory name
    Default/tmp

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

  • slave_net_timeout

    Command-Line Format--slave-net-timeout=#
    System Variable Nameslave_net_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default3600
    Min Value1

    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 using Unix socket files, named pipes, or shared memory.

  • slave_skip_errors

    Command-Line Format--slave-skip-errors=name
    System Variable Nameslave_skip_errors
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all

    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

    Introduced5.0.3
    Command-Line Format--slave_transaction_retries=#
    System Variable Nameslave_transaction_retries
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default10
    Min Value0
    Max Value4294967295

    If a replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because the transaction's execution time 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. Prior to MySQL 5.0.3, the default is 0, and you must explicitly set the value greater than 0 to enable the retry behavior. In MySQL 5.0.3 or newer, the default is 10.

  • sql_slave_skip_counter

    System Variable Namesql_slave_skip_counter
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric

    The number of events from the master that a slave server should skip.

    Important

    If skipping the number of events specified by setting this variable would cause the slave to begin in the middle of an event group, the slave continues to skip until it finds the beginning of the next event group and begins from that point. For more information, see Section 13.4.2.6, “SET GLOBAL sql_slave_skip_counter Syntax”.