Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.9Mb
PDF (A4) - 0.9Mb
HTML Download (TGZ) - 278.4Kb
HTML Download (Zip) - 293.1Kb


MySQL Replication  /  ...  /  Replication Slave Options and Variables

2.3.3 Replication Slave Options and Variables

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 set the server_id system variable 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

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

    Property Value
    Command-Line Format --abort-slave-event-count=#
    Type Integer
    Default Value 0
    Minimum Value 0

    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

    Property Value
    Command-Line Format --disconnect-slave-event-count=#
    Type Integer
    Default Value 0

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

  • --log-slow-slave-statements

    Property Value
    Command-Line Format --log-slow-slave-statements[={OFF|ON}]
    Type Boolean
    Default Value OFF

    When the slow query log is enabled, this option enables logging for queries that have taken more than long_query_time seconds to execute on the slave.

    Note that all statements logged in row format in the master will not be logged in the slave's slow log, even if this option is enabled.

  • --log-warnings[=level]

    Property Value
    Command-Line Format --log-warnings[=#]
    System Variable log_warnings
    Scope Global, Session
    Dynamic Yes
    Type Integer
    Default Value 1
    Minimum Value 0
    Maximum Value (64-bit platforms, >= 5.5.3) 18446744073709551615
    Maximum Value (64-bit platforms, <= 5.5.2) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    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. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See Communication Errors and Aborted Connections.

    Note

    The effects of this option are not limited to replication. It affects diagnostic messages across a spectrum of server activities.

  • --master-info-file=file_name

    Property Value
    Command-Line Format --master-info-file=file_name
    Type File name
    Default Value master.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 5.2.2, “Slave Status Logs”.

  • --master-retry-count=count

    Property Value
    Command-Line Format --master-retry-count=#
    Type Integer
    Default Value 86400
    Minimum Value 0
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    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 of the CHANGE MASTER TO statement (default 60). Reconnection attempts are triggered when the slave reaches its connection timeout (specified by the slave_net_timeout system variable) without receiving data from the master. The default value is 86400. A value of 0 means infinite; the slave attempts to connect forever.

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

    Property Value
    Command-Line Format --max-relay-log-size=#
    System Variable max_relay_log_size
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 1073741824

    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 5.2.1, “The Slave Relay Log”.

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

    Property Value
    Command-Line Format --relay-log-purge[={OFF|ON}]
    System Variable relay_log_purge
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value ON

    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

    Property Value
    Command-Line Format --relay-log-space-limit=#
    System Variable relay_log_space_limit
    Scope Global
    Dynamic No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value (64-bit platforms, >= 5.5.3) 18446744073709551615
    Maximum Value (64-bit platforms, <= 5.5.2) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    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

    Property Value
    Command-Line Format --replicate-do-db=name
    Type String

    The effects of this option depend on whether statement-based or row-based replication is in use.

    Statement-based replication.  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; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

    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 when using statement-based replication: 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 statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

    Row-based replication.  Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master:

    USE prices;
    UPDATE sales.february SET amount=amount+100;

    The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, issuing the following statements on the master has no effect on the slave when using row-based replication and --replicate-do-db=sales:

    USE prices;
    UPDATE prices.march SET amount=amount-25;

    Even if the statement USE prices were changed to USE sales, the UPDATE statement's effects would still not be replicated.

    Another important difference in how --replicate-do-db is handled in statement-based replication as opposed to row-based replication occurs with regard to statements that refer to multiple databases. Suppose that the slave is started with --replicate-do-db=db1, and the following statements are executed on the master:

    USE db1;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

    If you are using statement-based replication, then both tables are updated on the slave. However, when using row-based replication, only table1 is affected on the slave; since table2 is in a different database, table2 on the slave is not changed by the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

    USE db4;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

    In this case, the UPDATE statement would have no effect on the slave when using statement-based replication. However, if you are using row-based replication, the UPDATE would change table1 on the slave, but not table2—in other words, only tables in the database named by --replicate-do-db are changed, and the choice of default database has no effect on this behavior.

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

    Note

    This option affects replication in the same manner that --binlog-do-db affects binary logging, and the effects of the replication format on how --replicate-do-db affects replication behavior are the same as those of the logging format on the behavior of --binlog-do-db.

    This option has no effect on BEGIN, COMMIT, or ROLLBACK statements.

  • --replicate-ignore-db=db_name

    Property Value
    Command-Line Format --replicate-ignore-db=name
    Type String

    As with --replicate-do-db, the effects of this option depend on whether statement-based or row-based replication is in use.

    Statement-based replication.  Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

    Row-based replication.  Tells the slave SQL thread not to update any tables in the database db_name. The default database has no effect.

    When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master:

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

    The UPDATE statement is replicated in such a case because --replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, the UPDATE statement's effects are not propagated to the slave, and the slave's copy of the sales.january table is unchanged; in this instance, --replicate-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored by the slave.

    To specify more than one database to ignore, use this option multiple times, once for each database. 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.

    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 5.3, “How Servers Evaluate Replication Filtering Rules”.

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

    Note

    This option affects replication in the same manner that --binlog-ignore-db affects binary logging, and the effects of the replication format on how --replicate-ignore-db affects replication behavior are the same as those of the logging format on the behavior of --binlog-ignore-db.

    This option has no effect on BEGIN, COMMIT, or ROLLBACK statements.

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

    Property Value
    Command-Line Format --replicate-do-table=name
    Type String

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

    Property Value
    Command-Line Format --replicate-ignore-table=name
    Type String

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

    Property Value
    Command-Line Format --replicate-rewrite-db=old_name->new_name
    Type String

    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

    Property Value
    Command-Line Format --replicate-same-server-id[={OFF|ON}]
    Type Boolean
    Default Value OFF

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

    Property Value
    Command-Line Format --replicate-wild-do-table=name
    Type String

    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 5.3, “How Servers Evaluate Replication Filtering Rules”.

    This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. 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

    Property Value
    Command-Line Format --replicate-wild-ignore-table=name
    Type String

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

  • --skip-slave-start

    Property Value
    Command-Line Format --skip-slave-start[={OFF|ON}]
    Type Boolean
    Default Value OFF

    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}

    Property Value
    Command-Line Format --slave-compressed-protocol[={OFF|ON}]
    System Variable slave_compressed_protocol
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    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-skip-errors=[err_code1,err_code2,...|all]

    (MySQL NDB Cluster 7.2.6 and higher:) --slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]

    Property Value
    Command-Line Format --slave-skip-errors=name
    System Variable slave_skip_errors
    Scope Global
    Dynamic No
    Type String
    Default Value OFF
    Valid Values

    OFF

    [list of error codes]

    all

    ddl_exist_errors

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

    MySQL NDB Cluster 7.2.6 and higher support an additional shorthand value ddl_exist_errors for use with the enhanced failover mechanism which is implemented beginning with that version of NDB Cluster. This value is equivalent to the error code list 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146. This value is not supported by the mysqld binary included with the MySQL Server 5.5 distribution. (Bug #11762277, Bug #54854) For more information, see Implementing Failover with NDB Cluster Replication.

    Examples:

    --slave-skip-errors=1062,1053
    --slave-skip-errors=all
    --slave-skip-errors=ddl_exist_errors

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see CHANGE MASTER TO Statement).

The options affected are shown in this list:

  • --master-host

  • --master-user

  • --master-password

  • --master-port

  • --master-connect-retry

  • --master-ssl

  • --master-ssl-ca

  • --master-ssl-capath

  • --master-ssl-cert

  • --master-ssl-cipher

  • --master-ssl-key

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

    Property Value
    Command-Line Format --init-slave=name
    System Variable init_slave
    Scope Global
    Dynamic Yes
    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 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 START SLAVE Statement, for more information.

  • relay_log

    Property Value
    Command-Line Format --relay-log=file_name
    System Variable relay_log
    Scope Global
    Dynamic No
    Type File name

    The base name for the relay log. The default base name is host_name-relay-bin.

    The server writes the file in the data directory unless the base name 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 base name.

    Due to the manner in which MySQL parses server options, if you specify this variable at server startup, you must supply a value; the default base name is used only if the option is not actually specified. If you specify the relay_log system variable at server startup 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 Specifying Program Options.

    If you specify this variable, the value specified is also used as the base name for the relay log index file. You can override this behavior by specifying a different relay log index file base name using the relay_log_index system variable.

    Starting with MySQL 5.5.20, when the server reads an entry from the index file, it checks whether the entry contains a relative path. If it does, the relative part of the path in replaced with the absolute path set using the relay_log system variable. An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Prior to MySQL 5.5.20, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

    You may find the relay_log system variable 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

    Property Value
    Command-Line Format --relay-log-index=file_name
    System Variable relay_log_index
    Scope Global
    Dynamic No
    Type File name
    Default Value *host_name*-relay-bin.index

    The name 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 variable at server startup, you must supply a value; the default base name is used only if the option is not actually specified. If you specify the relay_log_index system variable at server startup 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 Specifying Program Options.

  • relay_log_info_file

    Property Value
    Command-Line Format --relay-log-info-file=file_name
    System Variable relay_log_info_file
    Scope Global
    Dynamic No
    Type File name
    Default Value relay-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. For information about the format of this file, see Section 5.2.2, “Slave Status Logs”.

  • relay_log_recovery

    Property Value
    Command-Line Format --relay-log-recovery[={OFF|ON}]
    System Variable relay_log_recovery
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    If enabled, this variable enables automatic relay log recovery immediately following server startup, which means that the replication slave discards all unprocessed relay logs and retrieves them from the replication master. This should be used following a crash on the replication slave to ensure that no possibly corrupted relay logs are processed. The default value is 0 (disabled). This global variable can be changed dynamically at runtime, or set with the --relay-log-recovery option at slave startup.

  • report_host

    Property Value
    Command-Line Format --report-host=host_name
    System Variable report_host
    Scope Global
    Dynamic No
    Type String

    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

    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

    Property Value
    Command-Line Format --report-password=name
    System Variable report_password
    Scope Global
    Dynamic No
    Type String

    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 master was started with --show-slave-auth-info.

    Although the name of this variable 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

    Property Value
    Command-Line Format --report-port=port_num
    System Variable report_port
    Scope Global
    Dynamic No
    Type Integer
    Default Value (>= 5.5.23) 0
    Default Value (<= 5.5.22) 3306
    Minimum Value 0
    Maximum Value 65535

    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.

    Prior to MySQL 5.5.23, the default value for this option was 3306. In MySQL 5.5.23 and higher, the value shown is the port number actually used by the slave (Bug #13333431). This change also affects the default value displayed by SHOW SLAVE HOSTS.

  • report_user

    Property Value
    Command-Line Format --report-user=name
    System Variable report_user
    Scope Global
    Dynamic No
    Type String

    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 master was started with --show-slave-auth-info.

    Although the name of this variable 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.

  • rpl_recovery_rank

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

  • slave_compressed_protocol

    Property Value
    Command-Line Format --slave-compressed-protocol[={OFF|ON}]
    System Variable slave_compressed_protocol
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value OFF

    Whether to use compression of the master/slave protocol if both master and slave support it. If this variable is disabled (the default), connections are uncompressed. See also Connection Compression Control.

  • slave_exec_mode

    Property Value
    Command-Line Format --slave-exec-mode=mode
    System Variable slave_exec_mode
    Scope Global
    Dynamic Yes
    Type Enumeration
    Default Value

    IDEMPOTENT (NDB)

    STRICT (Other)

    Valid Values

    IDEMPOTENT

    STRICT

    Controls how a slave thread resolves conflicts and errors during replication. IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors; STRICT means no such suppression takes place.

    IDEMPOTENT mode is intended for use in multi-master replication, circular replication, and some other special replication scenarios for NDB Cluster Replication. (See NDB Cluster Replication: Multi-Master and Circular Replication, and NDB Cluster Replication Conflict Resolution, for more information.) NDB Cluster ignores any value explicitly set for slave_exec_mode, and always treats it as IDEMPOTENT.

    In MySQL Server 5.5, STRICT mode is the default value.

    For storage engines other than NDB, IDEMPOTENT mode should be used only when you are absolutely sure that duplicate-key errors and key-not-found errors can safely be ignored. It is meant to be used in fail-over scenarios for NDB Cluster where multi-master replication or circular replication is employed, and is not recommended for use in other cases.

  • slave_load_tmpdir

    Property Value
    Command-Line Format --slave-load-tmpdir=dir_name
    System Variable slave_load_tmpdir
    Scope Global
    Dynamic No
    Type Directory name
    Default Value Value of --tmpdir

    The name of the directory where the slave creates temporary files. The variable value is by default equal to the value of the tmpdir system variable.

    When the slave SQL thread replicates a LOAD DATA 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 set the relay_log system variable 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) so that the temporary files used to replicate LOAD DATA statements can survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.

  • slave_max_allowed_packet

    Property Value
    Command-Line Format --slave-max-allowed-packet=#
    Introduced 5.5.26
    System Variable slave_max_allowed_packet
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 1073741824
    Minimum Value 1024
    Maximum Value 1073741824

    In MySQL 5.5.26 and higher, this variable sets the maximum packet size for the slave SQL and I/O threads, so that large updates using row-based replication do not cause replication to fail because an update exceeded max_allowed_packet.

    This global variable always has a value that is a positive integer multiple of 1024; if you set it to some value that is not, the value is rounded down to the next highest multiple of 1024 for it is stored or used; setting slave_max_allowed_packet to 0 causes 1024 to be used. (A truncation warning is issued in all such cases.) The default and maximum value is 1073741824 (1 GB); the minimum is 1024.

  • slave_net_timeout

    Property Value
    Command-Line Format --slave-net-timeout=#
    System Variable slave_net_timeout
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 3600
    Minimum Value 1

    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 MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

  • slave_skip_errors

    Property Value
    Command-Line Format --slave-skip-errors=name
    System Variable slave_skip_errors
    Scope Global
    Dynamic No
    Type String
    Default Value OFF
    Valid Values

    OFF

    [list of error codes]

    all

    ddl_exist_errors

    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

    Property Value
    Command-Line Format --slave-transaction-retries=#
    System Variable slave_transaction_retries
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 10
    Minimum Value 0
    Maximum Value (64-bit platforms, >= 5.5.3) 18446744073709551615
    Maximum Value (64-bit platforms, <= 5.5.2) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    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. The default value is 10.

  • slave_type_conversions

    Property Value
    Command-Line Format --slave-type-conversions=set
    Introduced 5.5.3
    System Variable slave_type_conversions
    Scope Global
    Dynamic No
    Type Set
    Default Value
    Valid Values

    ALL_LOSSY

    ALL_NON_LOSSY

    Controls the type conversion mode in effect on the slave when using row-based replication, including NDB Cluster Replication. Its value is a comma-delimited set of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY. Set this variable to an empty string to disallow type conversions between the master and the slave. Changes require a restart of the slave to take effect.

    For additional information on type conversion modes applicable to attribute promotion and demotion in row-based replication, see Row-based replication: attribute promotion and demotion.

  • sql_slave_skip_counter

    Property Value
    System Variable sql_slave_skip_counter
    Scope Global
    Dynamic Yes
    Type Integer

    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 SET GLOBAL sql_slave_skip_counter Statement.

  • sync_master_info

    Property Value
    Command-Line Format --sync-master-info=#
    System Variable sync_master_info
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value (64-bit platforms, >= 5.5.3) 18446744073709551615
    Maximum Value (64-bit platforms, <= 5.5.2) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk (using fdatasync()) after every sync_master_info events. The default value is 0 (recommended in most situations), which does not force any synchronization to disk by the MySQL server; in this case, the server relies on the operating system to flush the master.info file's contents from time to time as for any other file.

  • sync_relay_log

    Property Value
    Command-Line Format --sync-relay-log=#
    System Variable sync_relay_log
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value (64-bit platforms, >= 5.5.3) 18446744073709551615
    Maximum Value (64-bit platforms, <= 5.5.2) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log.

    The default value of sync_relay_log is 0, which does no synchronizing to disk; in this case, the server relies on the operating system to flush the relay log's contents from time to time as for any other file.

    A value of 1 is the safest choice because in the event of a crash you lose at most one event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

  • sync_relay_log_info

    Property Value
    Command-Line Format --sync-relay-log-info=#
    System Variable sync_relay_log_info
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value (64-bit platforms, >= 5.5.3) 18446744073709551615
    Maximum Value (64-bit platforms, <= 5.5.2) 18446744073709547520
    Maximum Value (32-bit platforms) 4294967295

    If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk (using fdatasync()) after every sync_relay_log_info transactions. A value of 1 is the generally the best choice. The default value of sync_relay_log_info is 0, which does not force any synchronization to disk by the MySQL server—in this case, the server relies on the operating system to flush the relay-log.info file's contents from time to time as for any other file.