17.1.6.3 Replication Slave Options and Variables

--log-slave-updates

This option makes a slave write updates that are received from a master server and performed by the slave's SQL thread to the slave's own binary log. Binary logging, which is controlled by the --log-bin option and is enabled by default, must also be enabled on the slave for updates to be logged. --log-slave-updates is enabled by default, unless you specify --skip-log-bin to disable binary logging, in which case MySQL also disables slave update logging by default. If you need to disable slave update logging when binary logging is enabled, specify --skip-log-slave-updates.

--log-slave-updates enables replication servers to be chained. 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. With binary logging and the --log-slave-updates option enabled, which are the default settings, updates received from A are logged by B to its binary log, and can therefore be passed on to C.

--master-info-file=file_name

The name for the master info log, if --master-info-repository=FILE is set. The default name is master.info in the data directory. --master-info-file and the setting --master-info-repository=FILE are deprecated, as the use of a file for the master info log has been superseded by crash-safe slave tables. For information about the master info log, see Section 17.2.4.2, “Slave Status Logs”.

--master-retry-count=count

The number of times that the slave tries to reconnect to the master before giving up. The default value is 86400 times. A value of 0 means “infinite”, and the slave attempts to connect forever. Reconnection attempts are triggered when the slave reaches its connection timeout (specified by the --slave-net-timeout option) without receiving data or a heartbeat signal from the master. Reconnection is attempted at intervals set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (which defaults to every 60 seconds).

This option is deprecated and will be removed in a future MySQL release. Use the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement instead.

--max-relay-log-size=size

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

--relay-log=file_name

The base name for the relay log. The server creates relay log files in sequence by adding a numeric suffix to the base name.

For the default replication channel, the default base name for relay logs is host_name-relay-bin, using the name of the host machine. For non-default replication channels, the default base name for relay logs is host_name-relay-bin-channel, where channel is the name of the replication channel recorded in this relay log.

The default location for relay log files is the data directory. You can use the --relay-log option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory.

The relay log and relay log index on a replication server cannot be given the same names as the binary log and binary log index, whose names are specified by the --log-bin and --log-bin-index options. The server issues an error message and does not start if the binary log and relay log file base names would be the same.

Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default base name 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.2, “Specifying Program Options”.

If you specify this option, 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 option.

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 is replaced with the absolute path set using the --relay-log option. 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. Previously, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

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

You can obtain the relay log file name (and path) from the relay_log_basename system variable.

--relay-log-index=file_name

The name for the relay log index file. If you do not specify the --relay-log-index option, but the --relay-log option is specified, its value is used as the default base name for the relay log index file. If the --relay-log option is also not specified, then for the default replication channel, the default name is host_name-relay-bin.index, using the name of the host machine. For non-default replication channels, the default name is host_name-relay-bin-channel.index, where channel is the name of the replication channel recorded in this relay log index.

The default location for relay log files is the data directory, or any other location that was specified using the --relay-log option. You can use the --relay-log-index option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory.

The relay log and relay log index on a replication server cannot be given the same names as the binary log and binary log index, whose names are specified by the --log-bin and --log-bin-index options. The server issues an error message and does not start if the binary log and relay log file base names would be the same.

Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default base name 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.2, “Specifying Program Options”.

--relay-log-info-file=file_name

The name for the relay log info file, when --relay-log-info-repository is set to FILE. The default name is relay-log.info in the data directory. --relay-log-info-file and the setting --relay-log-info-repository=FILE are deprecated, as the use of a file for the relay log info log has been superseded by crash-safe slave tables. For information about the relay log info log, see Section 17.2.4.2, “Slave Status Logs”.

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

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. Disabling purging of relay logs when using the --relay-log-recovery option risks data consistency and is therefore not crash-safe.

--relay-log-recovery={0|1}

Enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the master then continues. 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).

To provide a crash-safe slave, this option must be enabled (set to 1), --relay-log-info-repository must be set to TABLE, and relay-log-purge must be enabled. Enabling the --relay-log-recovery option when relay-log-purge is disabled risks reading the relay log from files that were not purged, leading to data inconsistency, and is therefore not crash-safe. See Making replication resilient to unexpected halts, for more information.

When using a multithreaded slave (in other words slave_parallel_workers is greater than 0), inconsistencies such as gaps can occur in the sequence of transactions that have been executed from the relay log. Enabling the --relay-log-recovery option when there are inconsistencies causes an error and the option has no effect. The solution in this situation is to issue START SLAVE UNTIL SQL_AFTER_MTS_GAPS, which brings the server to a more consistent state, then issue RESET SLAVE to remove the relay logs. See Section 17.4.1.33, “Replication and Transaction Inconsistencies” for more information.

--relay-log-space-limit=size

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

Creates a replication filter using the name of a database. Such filters can also be created using CHANGE REPLICATION FILTER REPLICATE_DO_DB.

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-do-db:channel_1:db_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

The precise effect of this replication filter depends 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.

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:

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:

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:

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:

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:

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

--replicate-ignore-db=db_name

Creates a replication filter using the name of a database. Such filters can also be created using CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB.

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-ignore-db:channel_1:db_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

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.

As with --replicate-do-db, the precise effect of this filtering depends on whether statement-based or row-based replication is in use, and are described in the next several paragraphs.

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:

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.

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

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

--replicate-do-table=db_name.tbl_name

Creates a replication filter by telling the slave SQL thread to restrict replication to a given 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 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing a CHANGE REPLICATION FILTER REPLICATE_DO_TABLE statement.

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-do-table:channel_1:db_name.tbl_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

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

Creates a replication filter by telling 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 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing a CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE statement.

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-ignore-table:channel_1:db_name.tbl_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

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

Tells the slave to create a replication filter that translates 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. You can also create such a filter by issuing a CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB statement.

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"

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. Specify the channel name followed by a colon, followed by the filter specification. The first colon is interpreted as a separator, and any subsequent colons are interpreted as literal colons. For example, to configure a channel specific replication filter on a channel named channel_1, use:

shell> mysqld --replicate-rewrite-db=channel_1:db_name1->db_name2

If you use a colon but do not specify a channel name, the option configures the replication filter for the default replication channel. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

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:

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

--replicate-same-server-id

This option is for use on replication slaves. The default is 0 (FALSE). With this option set to 1 (TRUE), the slave does not skip events that have its own server ID. This setting is normally useful only in rare configurations.

When binary logging is enabled on a replication slave, the combination of the --replicate-same-server-id and --log-slave-updates options on the slave can cause infinite loops in replication if the server is part of a circular replication topology. (In MySQL 8.0, binary logging is enabled by default, and slave update logging is the default when binary logging is enabled.) However, the use of global transaction identifiers (GTIDs) prevents this situation by skipping the execution of transactions that have already been applied. If gtid_mode=ON is set on the slave, you can start the server with this combination of options, but you cannot change to any other GTID mode while the server is running. If any other GTID mode is set, the server does not start with this combination of options.

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

Creates a replication filter by telling 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 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing a CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE statement.

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-wild-do-table:channel_1:db_name.tbl_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

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.

As an 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

Creates a replication filter which keeps the slave thread from replicating a statement in which 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 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing a CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE statement.

This option supports channel specific replication filters, enabling multi-source replication slaves to use specific filters for different sources. To configure a channel specific replication filter on a channel named channel_1 use --replicate-wild-ignore:channel_1:db_name.tbl_name. In this case, the first colon is interpreted as a separator and subsequent colons are literal colons. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.

As an 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

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.

--report-password=password

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

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.

The default value for this option is the port number actually used by the slave (Bug #13333431). This is also the default value displayed by SHOW SLAVE HOSTS.

--report-user=user_name

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

--slave-checkpoint-group=#

Sets the maximum number of transactions that can be processed by a multithreaded slave before a checkpoint operation is called to update its status as shown by SHOW SLAVE STATUS. Setting this option has no effect on slaves for which multithreading is not enabled.

This option works in combination with the --slave-checkpoint-period option in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

The minimum allowed value for this option is 32, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 1. The effective value is always a multiple of 8; you can set it to a value that is not such a multiple, but the server rounds it down to the next lower multiple of 8 before storing the value. (Exception: No such rounding is performed by the debug server.) Regardless of how the server was built, the default value is 512, and the maximum allowed value is 524280.

--slave-checkpoint-period=#

Sets the maximum time (in milliseconds) that is allowed to pass before a checkpoint operation is called to update the status of a multithreaded slave as shown by SHOW SLAVE STATUS. Setting this option has no effect on slaves for which multithreading is not enabled.

This option works in combination with the --slave-checkpoint-group option in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

The minimum allowed value for this option is 1, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 0. Regardless of how the server was built, the default value is 300, and the maximum possible value is 4294967296 (4GB).

--slave-parallel-workers

Enables multithreading on the slave and sets the number of slave applier threads for executing replication transactions in parallel. When the value is a number greater than 0, the slave is a multithreaded slave with the specified number of applier threads, plus a coordinator thread to manage them. If you are using multiple replication channels, each channel has this number of threads.

Retrying of transactions is supported when multithreading is enabled on a slave. When slave_preserve_commit_order=1, transactions on a slave are externalized on the slave in the same order as they appear in the slave's relay log. The way in which transactions are distributed among applier threads is configured by --slave-parallel-type.

To disable parallel execution, set this option to 0, which gives the slave a single applier thread and no coordinator thread. With this setting, the --slave-parallel-type and slave_preserve_commit_order options have no effect and are ignored.

--slave-pending-jobs-size-max=#

For multithreaded slaves, this option sets the maximum amount of memory (in bytes) available to slave worker queues holding events not yet applied. Setting this option has no effect on slaves for which multithreading is not enabled.

The minimum possible value for this option is 1024 bytes; the default is 128MB. The maximum possible value is 18446744073709551615 (16 exbibytes). Values that are not exact multiples of 1024 bytes are rounded down to the next lower multiple of 1024 bytes prior to being stored.

The value of this variable is a soft limit and can be set to match the normal workload. If an unusually large event exceeds this size, the transaction is held until all the slave workers have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed.

--skip-slave-start

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-load-tmpdir=dir_name

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, or the default that applies when that system variable is not specified. 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 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) so that the temporary files used to replicate LOAD DATA can survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process. However, replication can now continue after a restart if the temporary files have been removed.

slave-max-allowed-packet=bytes

This option sets the maximum packet size in bytes that the slave SQL and I/O threads can handle. It is possible for a replication master to write binary log events longer than its max_allowed_packet setting once the event header is added. The setting for slave_max_allowed_packet must be larger than the max_allowed_packet setting on the master, so that large updates using row-based replication do not cause replication to fail.

The corresponding server variable slave_max_allowed_packet always has a value that is a positive integer multiple of 1024; if you set it to some value that is not such a multiple, the value is automatically rounded down to the next highest multiple of 1024. (For example, if you start the server with --slave-max-allowed-packet=10000, the value used is 9216; setting 0 as the value causes 1024 to be used.) A truncation warning is issued in such cases.

The maximum (and default) value is 1073741824 (1 GB); the minimum is 1024.

--slave-net-timeout=seconds

The number of seconds to wait for more data or a heartbeat signal from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The default value is 60 seconds (one minute). 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 for the CHANGE MASTER TO statement.

The heartbeat interval, which stops the connection timeout occurring in the absence of data if the connection is still good, is controlled by the MASTER_HEARTBEAT_PERIOD option for the CHANGE MASTER TO statement. The heartbeat interval defaults to half the value of --slave-net-timeout, and it is recorded in the master info log and shown in the replication_connection_configuration Performance Schema table. Note that a change to the value or default setting of --slave-net-timeout does not automatically change the heartbeat interval, whether that has been set explicitly or is using a previously calculated default. If the connection timeout is changed, you must also issue CHANGE MASTER TO to adjust the heartbeat interval to an appropriate value so that it occurs before the connection timeout.

--slave-parallel-type=type

When using a multithreaded slave (slave_parallel_workers is greater than 0), this option specifies the policy used to decide which transactions are allowed to execute in parallel on the slave. The option has no effect on slaves for which multithreading is not enabled. The possible values are:

When slave_preserve_commit_order=1 is set, you can only use LOGICAL_CLOCK.

If your replication topology uses multiple levels of slaves, LOGICAL_CLOCK may achieve less parallelization for each level the slave is away from the master. You can reduce this effect by using binlog_transaction_dependency_tracking on the master to specify that write sets are used instead of timestamps for parallelization where possible.

slave-rows-search-algorithms=list

When preparing batches of rows for row-based logging and replication, this option controls how the rows are searched for matches, in particular whether hash scans are used. The use of this option is now deprecated. The default setting INDEX_SCAN,HASH_SCAN is optimal for performance and works correctly in all scenarios.

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

Normally, replication stops when an error occurs on the slave, which gives you the opportunity to resolve the inconsistency in the data manually. This option causes 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.

The shorthand value ddl_exist_errors is equivalent to the error code list 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

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
--slave-skip-errors=ddl_exist_errors

--slave-sql-verify-checksum={0|1}

When this option is enabled, the slave examines checksums read from the relay log, in the event of a mismatch, the slave stops with an error.

--abort-slave-event-count

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.

--disconnect-slave-event-count

--master-info-repository={TABLE|FILE}

This option determines whether the slave server logs master status and connection information to an InnoDB table in the mysql database, or to a file in the data directory.

The default setting is TABLE. As an InnoDB table, the master info log is named mysql.slave_master_info. The TABLE setting is required when multiple replication channels are configured.

The FILE setting is deprecated, and will be removed in a future release. As a file, the master info log is named master.info by default, and you can change this name using the --master-info-file option.

The setting for the location of this slave status log has a direct influence on the effect had by the setting of the sync_master_info system variable. You can only change the setting when no replication threads are executing.

--relay-log-info-repository={TABLE|FILE}

This option determines whether the slave server logs its position in the relay logs to an InnoDB table in the mysql database, or to a file in the data directory.

The default setting is TABLE. As an InnoDB table, the relay log info log is named mysql.slave_relay_log_info. The TABLE setting is required when multiple replication channels are configured. The TABLE setting for the relay log info log is also required to make replication resilient to unexpected halts, for which the --relay-log-recovery option must also be enabled. See Making replication resilient to unexpected halts for more information.

The FILE setting is deprecated, and will be removed in a future release. As a file, the relay log info log is named relay-log.info by default, and you can change this name using the --relay-log-info-file option.

The setting for the location of this slave status log has a direct influence on the effect had by the setting of the sync_relay_log_info system variable. You can only change the setting when no replication threads are executing.

init_slave

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. The setting of this variable takes effect for subsequent START SLAVE statements.

log_slow_slave_statements

When the slow query log is enabled, this variable enables logging for queries that have taken more than long_query_time seconds to execute on the slave. Note that if row-based replication is in use (binlog_format=ROW), log_slow_slave_statements has no effect. Queries are only added to the slave's slow query log when they are logged in statement format in the binary log, that is, when binlog_format=STATEMENT is set, or when binlog_format=MIXED is set and the statement is logged in statement format. Slow queries that are logged in row format when binlog_format=MIXED is set, or that are logged when binlog_format=ROW is set, are not added to the slave's slow query log, even if log_slow_slave_statements is enabled.

Setting log_slow_slave_statements has no immediate effect. The state of the variable applies on all subsequent START SLAVE statements. Also note that the global setting for long_query_time applies for the lifetime of the SQL thread. If you change that setting, you must stop and restart the slave's SQL thread to implement the change there (for example, by issuing STOP SLAVE and START SLAVE statements with the SQL_THREAD option).

master_info_repository

The setting of this variable determines whether the slave server logs master status and connection information to an InnoDB table in the mysql database, or to a file in the data directory.

The default setting is TABLE. As an InnoDB table, the master info log is named mysql.slave_master_info. The TABLE setting is required when multiple replication channels are configured.

The FILE setting is deprecated, and will be removed in a future release. As a file, the master info log is named master.info by default, and you can change this name using the --master-info-file option.

The setting for the location of this slave status log has a direct influence on the effect had by the setting of the sync_master_info system variable. You can only change the setting when no replication threads are executing.

max_relay_log_size

If a write by a replication slave to its relay log causes the current log file size to exceed the value of this variable, the slave rotates the relay logs (closes the current file and opens the next one). If max_relay_log_size is 0, the server uses max_binlog_size for both the binary log and the relay log. If max_relay_log_size is greater than 0, it constrains the size of the relay log, which enables you to have different sizes for the two logs. You must set max_relay_log_size to between 4096 bytes and 1GB (inclusive), or to 0. The default value is 0. See Section 17.2.2, “Replication Implementation Details”.

relay_log

The base name for relay log files, with no paths and no file extension. For the default replication channel, the default base name for relay logs is host_name-relay-bin. For non-default replication channels, the default base name for relay logs is host_name-relay-bin-channel, where channel is the name of the replication channel recorded in this relay log.

relay_log_basename

Holds the name and complete path to the relay log file. This variable is set by the server and is read only.

relay_log_index

The name of the relay log index file for the default replication channel. The default name is host_name-relay-bin.index.

relay_log_info_file

The name of the relay log info log, when relay_log_info_repository=FILE is set. The default name is relay-log.info in the data directory. relay_log_info_file and the setting relay_log_info_repository=FILE are deprecated, as the use of a file for the relay log info log has been superseded by crash-safe slave tables. For information about the relay log info log, see Section 17.2.4.2, “Slave Status Logs”.

relay_log_info_repository

The setting of this variable determines whether the slave server logs its position in the relay logs to an InnoDB table in the mysql database, or to a file in the data directory.

The default setting is TABLE. As an InnoDB table, the relay log info log is named mysql.slave_relay_log_info. The TABLE setting is required when multiple replication channels are configured. The TABLE setting for the relay log info log is also required to make replication resilient to unexpected halts, for which the --relay-log-recovery option must also be enabled. See Making replication resilient to unexpected halts for more information.

The FILE setting is deprecated, and will be removed in a future release. As a file, the relay log info log is named relay-log.info by default, and you can change this name using the --relay-log-info-file option.

The setting for the location of this slave status log has a direct influence on the effect had by the setting of the sync_relay_log_info system variable. You can only change the setting when no replication threads are executing.

relay_log_purge

Disables or enables automatic purging of relay log files as soon as they are not needed any more. The default value is 1 (ON).

relay_log_recovery

Enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the master then continues. This global variable is read-only; its value can be changed by starting the slave with the --relay-log-recovery option, which should be used following a crash on the replication slave to ensure that no possibly corrupted relay logs are processed, and must be used in order to guarantee a crash-safe slave. The default value is 0 (disabled).

This variable also interacts with relay-log-purge, which controls purging of logs when they are no longer needed. Enabling the --relay-log-recovery option when relay-log-purge is disabled risks reading the relay log from files that were not purged, leading to data inconsistency, and is therefore not crash-safe.

When relay_log_recovery is enabled and the slave has stopped due to errors encountered while running in multithreaded mode, you can use START SLAVE UNTIL SQL_AFTER_MTS_GAPS to ensure that all gaps are processed before switching back to single-threaded mode or executing a CHANGE MASTER TO statement.

relay_log_space_limit

The maximum amount of space to use for all relay logs.

report_host

The value of the --report-host option.

report_password

The value of the --report-password option. Not the same as the password used for the MySQL replication user account.

report_port

The value of the --report-port option.

report_user

The value of the --report-user option. Not the same as the name for the MySQL replication user account.

rpl_read_size

The rpl_read_size system variable controls the minimum amount of data in bytes that is read from the binary log files and relay log files. If heavy disk I/O activity for these files is impeding performance for the database, increasing the read size might reduce file reads and I/O stalls when the file data is not currently cached by the operating system.

The minimum and default value for rpl_read_size is 8192 bytes. The value must be a multiple of 4KB. Note that a buffer the size of this value is allocated for each thread that reads from the binary log and relay log files, including dump threads on masters and coordinator threads on slaves. Setting a large value might therefore have an impact on memory consumption for servers.

rpl_semi_sync_slave_enabled

Controls whether semisynchronous replication is enabled on the slave. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. The default is OFF.

This variable is available only if the slave-side semisynchronous replication plugin is installed.

rpl_semi_sync_slave_trace_level

The semisynchronous replication debug trace level on the slave. See rpl_semi_sync_master_trace_level for the permissible values.

This variable is available only if the slave-side semisynchronous replication plugin is installed.

rpl_stop_slave_timeout

You can control the length of time (in seconds) that STOP SLAVE waits before timing out by setting this variable. This can be used to avoid deadlocks between STOP SLAVE and other slave SQL statements using different client connections to the slave.

The maximum and default value of rpl_stop_slave_timeout is 31536000 seconds (1 year). The minimum is 2 seconds. Changes to this variable take effect for subsequent STOP SLAVE statements.

This variable affects only the client that issues a STOP SLAVE statement. When the timeout is reached, the issuing client returns an error message stating that the command execution is incomplete. The client then stops waiting for the slave threads to stop, but the slave threads continue to try to stop, and the STOP SLAVE instruction remains in effect. Once the slave threads are no longer busy, the STOP SLAVE statement is executed and the slave stops.

slave_checkpoint_group

Sets the maximum number of transactions that can be processed by a multithreaded slave before a checkpoint operation is called to update its status as shown by SHOW SLAVE STATUS. Setting this variable has no effect on slaves for which multithreading is not enabled. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE commands.

This variable works in combination with the slave_checkpoint_period system variable in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

The minimum allowed value for this variable is 32, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 1. The effective value is always a multiple of 8; you can set it to a value that is not such a multiple, but the server rounds it down to the next lower multiple of 8 before storing the value. (Exception: No such rounding is performed by the debug server.) Regardless of how the server was built, the default value is 512, and the maximum allowed value is 524280.

slave_checkpoint_period

Sets the maximum time (in milliseconds) that is allowed to pass before a checkpoint operation is called to update the status of a multithreaded slave as shown by SHOW SLAVE STATUS. Setting this variable has no effect on slaves for which multithreading is not enabled. Setting this variable takes effect for all replication channels immediately, including running channels.

This variable works in combination with the slave_checkpoint_group system variable in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

The minimum allowed value for this variable is 1, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 0. Regardless of how the server was built, the default value is 300, and the maximum possible value is 4294967296 (4GB).

slave_compressed_protocol

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. Changes to this variable take effect on subsequent connection attempts; this includes after issuing a START SLAVE statement, as well as reconnections made by a running I/O thread (for example, after setting the MASTER_RETRY_COUNT option for the CHANGE MASTER TO statement).

As of MySQL 8.0.18, if slave_compressed_protocol is enabled, it takes precedence over any MASTER_COMPRESSION_ALGORITHMS option specified for the CHANGE MASTER TO statement. In this case, connections to the master use zlib compression if both the master and slave support that algorithm. If slave_compressed_protocol is disabled, the value of MASTER_COMPRESSION_ALGORITHMS applies. For more information, see Section 4.2.6, “Connection Compression Control”.

As of MySQL 8.0.18, this system variable is deprecated. It will be removed in a future MySQL version. See Legacy Connection Compression Configuration.

slave_exec_mode

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 Section 22.6.10, “NDB Cluster Replication: Multi-Master and Circular Replication”, and Section 22.6.11, “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 8.0, STRICT mode is the default value.

Setting this variable takes immediate effect for all replication channels, including running channels.

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

The name of the directory where the slave creates temporary files. Setting this variable takes effect for all replication channels immediately, including running channels. This system variable is by default equal to the value of the tmpdir system variable, or the default that applies when that system variable is not specified.

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 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) so that the temporary files used to replicate LOAD DATA can survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process. However, replication can now continue after a restart if the temporary files have been removed.

slave_max_allowed_packet

This option sets the maximum packet size in bytes that the slave SQL and I/O threads can handle. Setting this variable takes effect for all replication channels immediately, including running channels. It is possible for a replication master to write binary log events longer than its max_allowed_packet setting once the event header is added. The setting for slave_max_allowed_packet must be larger than the max_allowed_packet setting on the master, so that large updates using row-based replication do not cause replication to fail.

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_max_allowed_packet can also be set at startup, using the --slave-max-allowed-packet option.

slave_net_timeout

The number of seconds to wait for more data or a heartbeat signal from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE commands.

The default value is 60 seconds (one minute). 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 for the CHANGE MASTER TO statement.

The heartbeat interval, which stops the connection timeout occurring in the absence of data if the connection is still good, is controlled by the MASTER_HEARTBEAT_PERIOD option for the CHANGE MASTER TO statement. The heartbeat interval defaults to half the value of slave_net_timeout, and it is recorded in the master info log and shown in the replication_connection_configuration Performance Schema table. Note that a change to the value or default setting of slave_net_timeout does not automatically change the heartbeat interval, whether that has been set explicitly or is using a previously calculated default. If the connection timeout is changed, you must also issue CHANGE MASTER TO to adjust the heartbeat interval to an appropriate value so that it occurs before the connection timeout.

slave_parallel_type=type

When using a multithreaded slave (slave_parallel_workers is greater than 0), this variable specifies the policy used to decide which transactions are allowed to execute in parallel on the slave. The variable has no effect on slaves for which multithreading is not enabled. The possible values are:

When slave_preserve_commit_order=1 is set, you can only use LOGICAL_CLOCK.

If your replication topology uses multiple levels of slaves, LOGICAL_CLOCK may achieve less parallelization for each level the slave is away from the master. You can reduce this effect by using binlog_transaction_dependency_tracking on the master to specify that write sets are used instead of timestamps for parallelization where possible.

slave_parallel_workers

Enables multithreading on the slave and sets the number of slave applier threads for executing replication transactions in parallel. When the value is a number greater than 0, the slave is a multithreaded slave with the specified number of applier threads, plus a coordinator thread to manage them. If you are using multiple replication channels, each channel has this number of threads.

Retrying of transactions is supported when multithreading is enabled on a slave. When slave_preserve_commit_order=1, transactions on a slave are externalized on the slave in the same order as they appear in the slave's relay log. The way in which transactions are distributed among applier threads is configured by --slave-parallel-type.

To disable parallel execution, set this option to 0, which gives the slave a single applier thread and no coordinator thread. With this setting, the --slave-parallel-type and slave_preserve_commit_order options have no effect and are ignored.

Setting slave_parallel_workers has no immediate effect. The state of the variable applies on all subsequent START SLAVE statements.

slave_pending_jobs_size_max

For multithreaded slaves, this variable sets the maximum amount of memory (in bytes) available to slave worker queues holding events not yet applied. Setting this variable has no effect on slaves for which multithreading is not enabled. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE commands.

The minimum possible value for this variable is 1024 bytes; the default is 128MB. The maximum possible value is 18446744073709551615 (16 exbibytes). Values that are not exact multiples of 1024 bytes are rounded down to the next lower multiple of 1024 bytes prior to being stored.

The value of this variable is a soft limit and can be set to match the normal workload. If an unusually large event exceeds this size, the transaction is held until all the slave workers have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed.

slave_preserve_commit_order

For multithreaded slaves, the setting 1 for this variable ensures that transactions are externalized on the slave in the same order as they appear in the slave's relay log, and prevents gaps in the sequence of transactions that have been executed from the relay log. This variable has no effect on slaves for which multithreading is not enabled. Note that slave_preserve_commit_order=1 does not preserve the order of non-transactional DML updates, so these might commit before transactions that precede them in the relay log, which might result in gaps.

slave_preserve_commit_order=1 requires that --log-bin and --log-slave-updates are enabled on the slave, and --slave-parallel-type is set to LOGICAL_CLOCK.

With slave_preserve_commit_order enabled, the executing thread waits until all previous transactions are committed before committing. While the slave thread is waiting for other workers to commit their transactions it reports its status as Waiting for preceding transaction to commit. With this mode, a multithreaded slave never enters a state that the master was not in. This supports the use of replication for read scale-out. See Section 17.3.5, “Using Replication for Scale-Out”.

Before changing this variable, all replication threads (for all replication channels if you are using multiple replication channels) must be stopped. If slave_preserve_commit_order=0 is set, the transactions that the slave applies in parallel may commit out of order. Therefore, checking for the most recently executed transaction does not guarantee that all previous transactions from the master have been executed on the slave. There is a chance of gaps in the sequence of transactions that have been executed from the slave's relay log. This has implications for logging and recovery when using a multithreaded slave. Note that the setting slave_preserve_commit_order=1 prevents gaps, but does not prevent master log position lag (where Exec_master_log_pos is behind the position up to which transactions have been executed). See Section 17.4.1.33, “Replication and Transaction Inconsistencies” for more information.

slave_rows_search_algorithms

When preparing batches of rows for row-based logging and replication, this system variable controls how the rows are searched for matches, in particular whether hash scans are used. The use of this system variable is now deprecated. The default setting INDEX_SCAN,HASH_SCAN is optimal for performance and works correctly in all scenarios.

slave_skip_errors

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

slave_sql_verify_checksum

Cause the slave SQL thread to verify data using the checksums read from the relay log. In the event of a mismatch, the slave stops with an error. Setting this variable takes effect for all replication channels immediately, including running channels.

slave_transaction_retries

Sets the maximum number of times for replication slave SQL threads on a single-threaded or multithreaded slave to automatically retry failed transactions before stopping. Setting this variable takes effect for all replication channels immediately, including running channels. The default value is 10. Setting the variable to 0 disables automatic retrying of transactions.

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 NDB's TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout, it automatically retries slave_transaction_retries times before stopping with an error. Transactions with a non-temporary error are not retried.

The Performance Schema table replication_applier_status shows the number of retries that took place on each replication channel, in the COUNT_TRANSACTIONS_RETRIES column. The Performance Schema table replication_applier_status_by_worker shows detailed information on transaction retries by individual applier threads on a single-threaded or multithreaded replication slave, and identifies the errors that caused the last transaction and the transaction currently in progress to be reattempted.

slave_type_conversions

Controls the type conversion mode in effect on the slave when using row-based replication. Its value is a comma-delimited set of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED. Set this variable to an empty string to disallow type conversions between the master and the slave. Setting this variable takes effect for all replication channels immediately, including running channels.

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

The number of events from the master that a slave server should skip. Setting the option has no immediate effect. The variable applies to the next START SLAVE statement; the next START SLAVE statement also changes the value back to 0. When this variable is set to a nonzero value and there are multiple replication channels configured, the START SLAVE statement can only be used with the FOR CHANNEL channel clause.

This option is incompatible with GTID-based replication, and must not be set to a nonzero value when --gtid-mode=ON. If you need to skip transactions when employing GTIDs, use gtid_executed from the master instead. See Injecting empty transactions, for information about how to do this.

sync_master_info

The effects of this variable on a replication slave depend on whether the slave's master_info_repository is set to FILE or TABLE, as explained in the following paragraphs.

master_info_repository = FILE.  If the value of sync_master_info is greater than 0, the slave synchronizes its master.info file to disk (using fdatasync()) after every sync_master_info events. If it is 0, the MySQL server performs no synchronization of the master.info file to disk; instead, the server relies on the operating system to flush its contents periodically as with any other file.

master_info_repository = TABLE.  If the value of sync_master_info is greater than 0, the slave updates its master info repository table after every sync_master_info events. If it is 0, the table is never updated.

The default value for sync_master_info is 10000. Setting this variable takes effect for all replication channels immediately, including running channels.

sync_relay_log

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. Setting this variable takes effect for all replication channels immediately, including running channels.

Setting sync_relay_log to 0 causes no synchronization to be done 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

The default value for sync_relay_log_info is 10000. Setting this variable takes effect for all replication channels immediately, including running channels.

The effects of this variable on the replication slave depend on the server's relay_log_info_repository setting (FILE or TABLE). If the setting is TABLE, the effects of the variable also depend on whether the storage engine used by the relay log info table is transactional (such as InnoDB) or not transactional (MyISAM). The effects of these factors on the behavior of the server for sync_relay_log_info values of zero and greater than zero are as follows: