Chapter 17 Replication

In the event of the server stopping unexpectedly, the set of GTIDs from the current binary log file is not saved in the mysql.gtid_executed table. These GTIDs are added to the table from the binary log file during recovery so that replication can continue. The exception to this is if you disable binary logging when the server is restarted (using --skip-log-bin or --disable-log-bin). In that case, the server cannot access the binary log file to recover the GTIDs, so replication cannot be started.

The mysql.gtid_executed table does not hold a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the gtid_executed system variable. In releases before MySQL 8.0.17 and with storage engines other than InnoDB, always use @@GLOBAL.gtid_executed, which is updated after every commit, to represent the GTID state for the MySQL server, instead of querying the mysql.gtid_executed table.

A stored procedure is invoked that commits multiple transactions. One GTID is generated for each transaction that the procedure commits.

A multi-table DROP TABLE statement drops tables of different types. Multiple GTIDs can be generated if any of the tables use storage engines that do not support atomic DDL, or if any of the tables are temporary tables.

A CREATE TABLE ... SELECT statement is issued when row-based replication is in use (binlog_format=ROW). One GTID is generated for the CREATE TABLE action and one GTID is generated for the row-insert actions.

When gtid_next is set to AUTOMATIC, which is the default, and a transaction is committed and written to the binary log, the server automatically generates and assigns a new GTID. If a transaction is rolled back or not written to the binary log for another reason, the server does not generate and assign a GTID.

If you set gtid_next to a valid GTID (consisting of a UUID and a transaction sequence number, separated by a colon), the server assigns that GTID to your transaction. This GTID is assigned and added to gtid_executed even when the transaction is not written to the binary log, or when the transaction is empty.

GTIDs of replicated transactions that were committed with binary logging disabled on the replica.

GTIDs of transactions that were written to a binary log file that has now been purged.

GTIDs that were added explicitly to the set by the statement SET @@GLOBAL.gtid_purged.

gtid_executed is computed as the union of the GTIDs in Previous_gtids_log_event in the most recent binary log file, the GTIDs of transactions in that binary log file, and the GTIDs stored in the mysql.gtid_executed table. This GTID set contains all the GTIDs that have been used (or added explicitly to gtid_purged) on the server, whether or not they are currently in a binary log file on the server. It does not include the GTIDs for transactions that are currently being processed on the server (@@GLOBAL.gtid_owned).

gtid_purged is computed by first adding the GTIDs in Previous_gtids_log_event in the most recent binary log file and the GTIDs of transactions in that binary log file. This step gives the set of GTIDs that are currently, or were once, recorded in a binary log on the server (gtids_in_binlog). Next, the GTIDs in Previous_gtids_log_event in the oldest binary log file are subtracted from gtids_in_binlog. This step gives the set of GTIDs that are currently recorded in a binary log on the server (gtids_in_binlog_not_purged). Finally, gtids_in_binlog_not_purged is subtracted from gtid_executed. The result is the set of GTIDs that have been used on the server, but are not currently recorded in a binary log file on the server, and this result is used to initialize gtid_purged.

The value of the gtid_purged system variable is set to an empty string ('').

The global value (but not the session value) of the gtid_executed system variable is set to an empty string.

The mysql.gtid_executed table is cleared (see mysql.gtid_executed Table).

If the server has binary logging enabled, the existing binary log files are deleted and the binary log index file is cleared.

abort-slave-event-count: Option used by mysql-test for debugging and testing of replication.

auto_increment_increment: AUTO_INCREMENT columns are incremented by this value.

auto_increment_offset: Offset added to AUTO_INCREMENT columns.

binlog_expire_logs_seconds: Purge binary logs after this many seconds.

binlog_gtid_simple_recovery: Controls how binary logs are iterated during GTID recovery.

Com_change_master: Count of CHANGE MASTER TO statements.

Com_show_master_status: Count of SHOW MASTER STATUS statements.

Com_show_slave_hosts: Count of SHOW REPLICAS and SHOW SLAVE HOSTS statements.

Com_show_replicas: Count of SHOW REPLICAS and SHOW SLAVE HOSTS statements.

Com_show_slave_status: Count of SHOW REPLICA STATUS and SHOW SLAVE STATUS statements.

Com_show_replica_status: Count of SHOW REPLICA STATUS and SHOW SLAVE STATUS statements.

Com_slave_start: Count of START REPLICA and START SLAVE statements.

Com_replica_start: Count of START REPLICA and START SLAVE statements.

Com_slave_stop: Count of STOP REPLICA and STOP SLAVE statements.

Com_replica_stop: Count of STOP REPLICA and STOP SLAVE statements.

disconnect-slave-event-count: Option used by mysql-test for debugging and testing of replication.

enforce_gtid_consistency: Prevents execution of statements that cannot be logged in transactionally safe manner.

expire_logs_days: Purge binary logs after this many days.

gtid_executed: Global: All GTIDs in binary log (global) or current transaction (session). Read-only.

gtid_executed_compression_period: Compress gtid_executed table each time this many transactions have occurred. 0 means never compress this table. Applies only when binary logging is disabled.

gtid_mode: Controls whether GTID based logging is enabled and what type of transactions logs can contain.

gtid_next: Specifies GTID for next statement to execute; see documentation for details.

gtid_owned: Set of GTIDs owned by this client (session), or by all clients, together with thread ID of owner (global). Read-only.

gtid_purged: Set of all GTIDs that have been purged from binary log.

init_slave: Statements that are executed when replica connects to source.

log_bin_trust_function_creators: If equal to 0 (default), then when --log-bin is used, stored function creation is allowed only to users having SUPER privilege and only if function created does not break binary logging.

log_statements_unsafe_for_binlog: Disables error 1592 warnings being written to error log.

master-info-file: Location and name of file that remembers source and where I/O replication thread is in source's binary log.

master-retry-count: Number of tries replica makes to connect to source before giving up.

master_info_repository: Whether to write connection metadata repository, containing source information and replication I/O thread location in source's binary log, to file or table.

max_relay_log_size: If nonzero, relay log is rotated automatically when its size exceeds this value. If zero, size at which rotation occurs is determined by value of max_binlog_size.

original_commit_timestamp: Time when transaction was committed on original source.

immediate_server_version: MySQL Server release number of server which is immediate replication source.

original_server_version: MySQL Server release number of server on which transaction was originally committed.

relay_log: Location and base name to use for relay logs.

relay_log_basename: Complete path to relay log, including file name.

relay_log_index: Location and name to use for file that keeps list of last relay logs.

relay_log_info_file: File name for applier metadata repository in which replica records information about relay logs.

relay_log_info_repository: Whether to write location of replication SQL thread in relay logs to file or table.

relay_log_purge: Determines whether relay logs are purged.

relay_log_recovery: Whether automatic recovery of relay log files from source at startup is enabled; must be enabled for crash-safe replica.

relay_log_space_limit: Maximum space to use for all relay logs.

replicate-do-db: Tells replication SQL thread to restrict replication to specified database.

replicate-do-table: Tells replication SQL thread to restrict replication to specified table.

replicate-ignore-db: Tells replication SQL thread not to replicate to specified database.

replicate-ignore-table: Tells replication SQL thread not to replicate to specified table.

replicate-rewrite-db: Updates to database with different name from original.

replicate-same-server-id: In replication, if enabled, do not skip events having our server id.

replicate-wild-do-table: Tells replication SQL thread to restrict replication to tables that match specified wildcard pattern.

replicate-wild-ignore-table: Tells replication SQL thread not to replicate to tables that match given wildcard pattern.

report_host: Host name or IP of replica to be reported to source during replica registration.

report_password: Arbitrary password which replica server should report to source; not same as password for replication user account.

report_port: Port for connecting to replica reported to source during replica registration.

report_user: Arbitrary user name which replica server should report to source; not same as name used for replication user account.

Rpl_semi_sync_master_clients: Number of semisynchronous replicas.

rpl_semi_sync_master_enabled: Whether semisynchronous replication is enabled on source.

Rpl_semi_sync_master_net_avg_wait_time: Average time source has waited for replies from replica.

Rpl_semi_sync_master_net_wait_time: Total time source has waited for replies from replica.

Rpl_semi_sync_master_net_waits: Total number of times source waited for replies from replica.

Rpl_semi_sync_master_no_times: Number of times source turned off semisynchronous replication.

Rpl_semi_sync_master_no_tx: Number of commits not acknowledged successfully.

Rpl_semi_sync_master_status: Whether semisynchronous replication is operational on source.

Rpl_semi_sync_master_timefunc_failures: Number of times source failed when calling time functions.

rpl_semi_sync_master_timeout: Number of milliseconds to wait for replica acknowledgment.

rpl_semi_sync_master_trace_level: Semisynchronous replication debug trace level on source.

Rpl_semi_sync_master_tx_avg_wait_time: Average time source waited for each transaction.

Rpl_semi_sync_master_tx_wait_time: Total time source waited for transactions.

Rpl_semi_sync_master_tx_waits: Total number of times source waited for transactions.

rpl_semi_sync_master_wait_for_slave_count: Number of replica acknowledgments source must receive per transaction before proceeding.

rpl_semi_sync_master_wait_no_slave: Whether source waits for timeout even with no replicas.

rpl_semi_sync_master_wait_point: Wait point for replica transaction receipt acknowledgment.

Rpl_semi_sync_master_wait_pos_backtraverse: Total number of times source has waited for event with binary coordinates lower than events waited for previously.

Rpl_semi_sync_master_wait_sessions: Number of sessions currently waiting for replica replies.

Rpl_semi_sync_master_yes_tx: Number of commits acknowledged successfully.

replication_sender_observe_commit_only: Limited callbacks for semisynchronous replication.

replication_optimize_for_static_plugin_config: Shared locks for semisynchronous replication.

rpl_semi_sync_slave_enabled: Whether semisynchronous replication is enabled on replica.

Rpl_semi_sync_slave_status: Whether semisynchronous replication is operational on replica.

rpl_semi_sync_slave_trace_level: Semisynchronous replication debug trace level on replica.

rpl_read_size: Set minimum amount of data in bytes which is read from binary log files and relay log files.

rpl_stop_slave_timeout: Number of seconds that STOP REPLICA or STOP SLAVE waits before timing out.

server_uuid: Server's globally unique ID, automatically (re)generated at server start.

show-slave-auth-info: Show user name and password in SHOW REPLICAS and SHOW SLAVE HOSTS on this source.

skip-slave-start: If set, replication is not autostarted when replica server starts.

slave_load_tmpdir: Location where replica should put its temporary files when replicating LOAD DATA statements.

slave_net_timeout: Number of seconds to wait for more data from source/replica connection before aborting read.

slave-skip-errors: Tells replication thread to continue replication when query returns error from provided list.

slave_checkpoint_group: Maximum number of transactions processed by multithreaded replica before checkpoint operation is called to update progress status. Not supported by NDB Cluster.

slave_checkpoint_period: Update progress status of multithreaded replica and flush relay log info to disk after this number of milliseconds. Not supported by NDB Cluster.

slave_compressed_protocol: Use compression of source/replica protocol.

slave_exec_mode: Allows for switching replication thread between IDEMPOTENT mode (key and some other errors suppressed) and STRICT mode; STRICT mode is default, except for NDB Cluster, where IDEMPOTENT is always used.

slave_max_allowed_packet: Maximum size, in bytes, of packet that can be sent from replication source server to replica; overrides max_allowed_packet.

Slave_open_temp_tables: Number of temporary tables that replication SQL thread currently has open.

slave_parallel_type: Tells replica to use timestamp information (LOGICAL_CLOCK) or database partioning (DATABASE) to parallelize transactions.

slave_parallel_workers: Number of applier threads for executing replication transactions in parallel. 0 disables replica multithreading. Not supported by MySQL Cluster.

slave_pending_jobs_size_max: Maximum size of replica worker queues holding events not yet applied.

slave_preserve_commit_order: Ensures that all commits by replica workers happen in same order as on source to maintain consistency when using parallel applier threads.

slave_rows_search_algorithms: Determines search algorithms used for replica update batching. Any 2 or 3 from this list: INDEX_SEARCH, TABLE_SCAN, HASH_SCAN.

Slave_rows_last_search_algorithm_used: Search algorithm most recently used by this replica to locate rows for row-based replication (index, table, or hash scan).

slave_transaction_retries: Number of times replication SQL thread retries transaction in case it failed with deadlock or elapsed lock wait timeout, before giving up and stopping.

slave_type_conversions: Controls type conversion mode on replica. Value is list of zero or more elements from this list: ALL_LOSSY, ALL_NON_LOSSY. Set to empty string to disallow type conversions between source and replica.

sql_log_bin: Controls binary logging for current session.

sql_slave_skip_counter: Number of events from source that replica should skip. Not compatible with GTID replication.

sync_master_info: Synchronize master.info to disk after every #th event.

sync_relay_log: Synchronize relay log to disk after every #th event.

sync_relay_log_info: Synchronize relay.info file to disk after every #th event.

transaction_write_set_extraction: Defines algorithm used to hash writes extracted during transaction.

binlog-checksum: Enable/disable binary log checksums.

binlog-do-db: Limits binary logging to specific databases.

binlog_format: Specifies format of binary log.

binlog-ignore-db: Tells source that updates to given database should not be written to binary log.

binlog-row-event-max-size: Binary log max event size.

binlog_encryption: Enable encryption for binary log files and relay log files on this server.

binlog_rotate_encryption_master_key_at_startup: Rotate binary log master key at server startup.

Binlog_cache_disk_use: Number of transactions which used temporary file instead of binary log cache.

binlog_cache_size: Size of cache to hold SQL statements for binary log during transaction.

Binlog_cache_use: Number of transactions that used temporary binary log cache.

binlog_checksum: Enable/disable binary log checksums.

binlog_direct_non_transactional_updates: Causes updates using statement format to nontransactional engines to be written directly to binary log. See documentation before using.

binlog_error_action: Controls what happens when server cannot write to binary log.

binlog_group_commit_sync_delay: Sets number of microseconds to wait before synchronizing transactions to disk.

binlog_group_commit_sync_no_delay_count: Sets maximum number of transactions to wait for before aborting current delay specified by binlog_group_commit_sync_delay.

binlog_max_flush_queue_time: How long to read transactions before flushing to binary log.

binlog_order_commits: Whether to commit in same order as writes to binary log.

binlog_row_image: Use full or minimal images when logging row changes.

binlog_row_metadata: Whether to record all or only minimal table related metadata to binary log when using row-based logging.

binlog_row_value_options: Enables binary logging of partial JSON updates for row-based replication.

binlog_rows_query_log_events: When enabled, enables logging of rows query log events when using row-based logging. Disabled by default. Do not enable when producing logs for pre-5.6 replicas/readers.

Binlog_stmt_cache_disk_use: Number of nontransactional statements that used temporary file instead of binary log statement cache.

binlog_stmt_cache_size: Size of cache to hold nontransactional statements for binary log during transaction.

Binlog_stmt_cache_use: Number of statements that used temporary binary log statement cache.

binlog_transaction_compression: Enable compression for transaction payloads in binary log files.

binlog_transaction_compression_level_zstd: Compression level for transaction payloads in binary log files.

binlog_transaction_dependency_tracking: Source of dependency information (commit timestamps or transaction write sets) from which to assess which transactions can be executed in parallel by replica's multithreaded applier.

binlog_transaction_dependency_history_size: Number of row hashes kept for looking up transaction that last updated some row.

Com_show_binlog_events: Count of SHOW BINLOG EVENTS statements.

Com_show_binlogs: Count of SHOW BINLOGS statements.

log-bin: Base name for binary log files.

log-bin-index: Name of binary log index file.

log_bin: Whether binary log is enabled.

log_bin_basename: Path and base name for binary log files.

log_bin_use_v1_row_events: Whether server is using version 1 binary log row events.

log_slave_updates: Whether replica should log updates performed by its replication SQL thread to its own binary log.

master_verify_checksum: Cause source to examine checksums when reading from binary log.

max-binlog-dump-events: Option used by mysql-test for debugging and testing of replication.

max_binlog_cache_size: Can be used to restrict total size used to cache multi-statement transaction.

max_binlog_size: Binary log is rotated automatically when size exceeds this value.

max_binlog_stmt_cache_size: Can be used to restrict total size used to cache all nontransactional statements during transaction.

slave-sql-verify-checksum: Cause replica to examine checksums when reading from relay log.

slave_sql_verify_checksum: Cause replica to examine checksums when reading from relay log.

sporadic-binlog-dump-fail: Option used by mysql-test for debugging and testing of replication.

sync_binlog: Synchronously flush binary log to disk after every #th event.

--show-slave-auth-info

Display replication user names and passwords in the output of SHOW REPLICAS | SHOW SLAVE HOSTS on the source for replicas started with the --report-user and --report-password options.

auto_increment_increment

auto_increment_increment and auto_increment_offset are intended for use with circular (source-to-source) replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values, and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. Attempting to set the value of auto_increment_increment or auto_increment_offset to a noninteger value produces an error, and the actual value of the variable remains unchanged.

As of MySQL 8.0.18, setting the session value of this system variable is no longer a restricted operation.

When Group Replication is started on a server, the value of auto_increment_increment is changed to the value of group_replication_auto_increment_increment, which defaults to 7, and the value of auto_increment_offset is changed to the server ID. The changes are reverted when Group Replication is stopped. These changes are only made and reverted if auto_increment_increment and auto_increment_offset each have their default value of 1. If their values have already been modified from the default, Group Replication does not alter them. From MySQL 8.0, the system variables are also not modified when Group Replication is in single-primary mode, where only one server writes.

auto_increment_increment and auto_increment_offset affect AUTO_INCREMENT column behavior as follows:

If either of these variables is changed, and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column. The series is calculated like this:

auto_increment_offset + N × auto_increment_increment

where N is a positive integer value in the series [1, 2, 3, ...]. For example:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
+-----+
4 rows in set (0.00 sec)

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
|  35 |
|  45 |
|  55 |
|  65 |
+-----+
8 rows in set (0.00 sec)

The values shown for auto_increment_increment and auto_increment_offset generate the series 5 + N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in the col column prior to the INSERT is 31, and the next available value in the AUTO_INCREMENT series is 35, so the inserted values for col begin at that point and the results are as shown for the SELECT query.

It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or until mysqld is restarted. If the local value is set, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

The default value of auto_increment_increment is 1. See Section 17.5.1.1, “Replication and AUTO_INCREMENT”.

auto_increment_offset

This variable has a default value of 1. If it is left with its default value, and Group Replication is started on the server in multi-primary mode, it is changed to the server ID. For more information, see the description for auto_increment_increment.

As of MySQL 8.0.18, setting the session value of this system variable is no longer a restricted operation.

immediate_server_version

For internal use by replication. This session system variable holds the MySQL Server release number of the server that is the immediate source in a replication topology (for example, 80014 for a MySQL 8.0.14 server instance). If this immediate server is at a release that does not support the session system variable, the value of the variable is set to 0 (UNKNOWN_SERVER_VERSION).

The value of the variable is replicated from a source to a replica. With this information the replica can correctly process data originating from a source at an older release, by recognizing where syntax changes or semantic changes have occurred between the releases involved and handling these appropriately. The information can also be used in a Group Replication environment where one or more members of the replication group is at a newer release than the others. The value of the variable can be viewed in the binary log for each transaction (as part of the Gtid_log_event, or Anonymous_gtid_log_event if GTIDs are not in use on the server), and could be helpful in debugging cross-version replication issues.

Setting the session value of this system variable is a restricted operation. The session user must have either the REPLICATION_APPLIER privilege (see Section 17.3.3, “Replication Privilege Checks”), or privileges sufficient to set restricted session variables (see Section 5.1.9.1, “System Variable Privileges”). However, note that the variable is not intended for users to set; it is set automatically by the replication infrastructure.

original_server_version

For internal use by replication. This session system variable holds the MySQL Server release number of the server where a transaction was originally committed (for example, 80014 for a MySQL 8.0.14 server instance). If this original server is at a release that does not support the session system variable, the value of the variable is set to 0 (UNKNOWN_SERVER_VERSION). Note that when a release number is set by the original server, the value of the variable is reset to 0 if the immediate server or any other intervening server in the replication topology does not support the session system variable, and so does not replicate its value.

The value of the variable is set and used in the same ways as for the immediate_server_version system variable. If the value of the variable is the same as that for the immediate_server_version system variable, only the latter is recorded in the binary log, with an indicator that the original server version is the same.

In a Group Replication environment, view change log events, which are special transactions queued by each group member when a new member joins the group, are tagged with the server version of the group member queuing the transaction. This ensures that the server version of the original donor is known to the joining member. Because the view change log events queued for a particular view change have the same GTID on all members, for this case only, instances of the same GTID might have a different original server version.

Setting the session value of this system variable is a restricted operation. The session user must have either the REPLICATION_APPLIER privilege (see Section 17.3.3, “Replication Privilege Checks”), or privileges sufficient to set restricted session variables (see Section 5.1.9.1, “System Variable Privileges”). However, note that the variable is not intended for users to set; it is set automatically by the replication infrastructure.

rpl_semi_sync_master_enabled

Controls whether semisynchronous replication is enabled on the source server. 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 source-side semisynchronous replication plugin is installed.

rpl_semi_sync_master_timeout

A value in milliseconds that controls how long the source waits on a commit for acknowledgment from a replica before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).

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

rpl_semi_sync_master_trace_level

The semisynchronous replication debug trace level on the source server. Four levels are defined:

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

rpl_semi_sync_master_wait_for_slave_count

The number of replica acknowledgments the source must receive per transaction before proceeding. By default rpl_semi_sync_master_wait_for_slave_count is 1, meaning that semisynchronous replication proceeds after receiving a single replica acknowledgment. Performance is best for small values of this variable.

For example, if rpl_semi_sync_master_wait_for_slave_count is 2, then 2 replicas must acknowledge receipt of the transaction before the timeout period configured by rpl_semi_sync_master_timeout for semisynchronous replication to proceed. If fewer replicas acknowledge receipt of the transaction during the timeout period, the source reverts to normal replication.

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

rpl_semi_sync_master_wait_no_slave

Controls whether the source waits for the timeout period configured by rpl_semi_sync_master_timeout to expire, even if the replica count drops to less than the number of replicas configured by rpl_semi_sync_master_wait_for_slave_count during the timeout period.

When the value of rpl_semi_sync_master_wait_no_slave is ON (the default), it is permissible for the replica count to drop to less than rpl_semi_sync_master_wait_for_slave_count during the timeout period. As long as enough replicas acknowledge the transaction before the timeout period expires, semisynchronous replication continues.

When the value of rpl_semi_sync_master_wait_no_slave is OFF, if the replica count drops to less than the number configured in rpl_semi_sync_master_wait_for_slave_count at any time during the timeout period configured by rpl_semi_sync_master_timeout, the source reverts to normal replication.

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

rpl_semi_sync_master_wait_point

This variable controls the point at which a semisynchronous replication source server waits for replica acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

The replication characteristics of these settings differ as follows:

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

With the addition of rpl_semi_sync_master_wait_point in MySQL 5.7, a version compatibility constraint was created because it increments the semisynchronous interface version: Servers for MySQL 5.7 and higher do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7 and higher.

--master-info-file=file_name

The use of this option is now deprecated. It was used to set the file name for the replica's connection metadata repository if master_info_repository=FILE was set. --master-info-file and the use of the master_info_repository system variable are deprecated because the use of a file for the connection metadata repository has been superseded by crash-safe tables. For information about the connection metadata repository, see Section 17.2.4.2, “Replication Metadata Repositories”.

--master-retry-count=count

The number of times that the replica tries to reconnect to the source before giving up. The default value is 86400 times. A value of 0 means “infinite”, and the replica attempts to connect forever. Reconnection attempts are triggered when the replica reaches its connection timeout (specified by the slave_net_timeout system variable) without receiving data or a heartbeat signal from the source. 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; expect it to 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 Relay Log”.

--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 enabling the --relay-log-recovery option risks data consistency and is therefore not crash-safe.

--relay-log-space-limit=size

This option places an upper limit on the total size in bytes of all relay logs on the replica. A value of 0 means “no limit”. This is useful for a replica server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the source 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 replicas 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 replication 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 replica is started with --replicate-do-db=sales and you issue the following statements on the source, 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 replication 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 replica is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the source:

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

The february table in the sales database on the replica 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 source has no effect on the replica 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 replica is started with --replicate-do-db=db1, and the following statements are executed on the source:

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

If you are using statement-based replication, then both tables are updated on the replica. However, when using row-based replication, only table1 is affected on the replica; since table2 is in a different database, table2 on the replica 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, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;

In this case, the UPDATE statement would have no effect on the replica when using statement-based replication. However, if you are using row-based replication, the UPDATE would change table1 on the replica, 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 replicas 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, it is 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 replication 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 replication 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 replica is started with --replicate-ignore-db=sales and you issue the following statements on the source:

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 replica, and the replica's copy of the sales.january table is unchanged; in this instance, --replicate-ignore-db=sales causes all changes made to tables in the source's copy of the sales database to be ignored by the replica.

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 replication 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 replicas 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 replication 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 replicas 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 replica to create a replication filter that translates the specified database to to_name if it was from_name on the source. Only statements involving tables are affected, not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE.

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 the --replicate-rewrite-db 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"

The effect of the --replicate-rewrite-db option differs depending on whether statement-based or row-based binary logging format is used for the query. With statement-based format, DML statements are translated based on the current database, as specified by the USE statement. With row-based format, DML statements are translated based on the database where the modified table exists. DDL statements are always filtered based on the current database, as specified by the USE statement, regardless of the binary logging format.

To ensure that rewriting produces the expected results, particularly in combination with other replication filtering options, follow these recommendations when you use the --replicate-rewrite-db option:

If these recommendations are followed, it is safe to use the --replicate-rewrite-db option in combination with table-level replication filtering options such as --replicate-do-table.

This option supports channel specific replication filters, enabling multi-source replicas 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.

--replicate-same-server-id

This option is for use on replicas. The default is 0 (FALSE). With this option set to 1 (TRUE), the replica 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 replica, the combination of the --replicate-same-server-id and --log-slave-updates options on the replica 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 replica 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 replica, 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 replication I/O thread does not write binary log events to the relay log if they have the replica's server ID (this optimization helps save disk usage). If you want to use --replicate-same-server-id, be sure to start the replica with this option before you make the replica read its own events that you want the replication SQL thread to execute.

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

Creates a replication filter by telling the replication SQL 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 replicas 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 replication SQL 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 replicas 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.

--skip-slave-start

Tells the replica server not to start the replication I/O and SQL threads when the server starts. To start the threads later, use a START REPLICA | SLAVE statement.

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

Normally, replication stops when an error occurs on the replica, which gives you the opportunity to resolve the inconsistency in the data manually. This option causes the replication 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 replicas becoming hopelessly out of synchrony with the source, with you having no idea why this has occurred.

For error codes, you should use the numbers provided by the error message in your replica's error log and in the output of SHOW REPLICA | SLAVE STATUS. Appendix B, Error Messages 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 replica 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 replica's data is not anywhere close to what it is on the source. 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 replica examines checksums read from the relay log. In the event of a mismatch, the replica 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 replication SQL thread has started, value log events are permitted to be executed; after that, the replication SQL thread does not receive any more events, just as if the network connection from the source were cut. The replication SQL thread continues to run, and the output from SHOW REPLICA | SLAVE STATUS displays Yes in both the Replica_IO_Running and the Replica_SQL_Running columns, but no further events are read from the relay log.

--disconnect-slave-event-count

init_slave

This variable is similar to init_connect, but is a string to be executed by a replica server each time the replication 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 REPLICA | 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 replica. 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 replica'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 replica'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 REPLICA | 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 replication SQL thread to implement the change there (for example, by issuing STOP REPLICA | SLAVE and START REPLICA | SLAVE statements with the SQL_THREAD option).

master_info_repository

The use of this system variable is now deprecated. The setting TABLE is the default, and is required when multiple replication channels are configured. The alternative setting FILE was previously deprecated.

With the default setting, the replica records metadata about the source, consisting of status and connection information, to an InnoDB table in the mysql system database named mysql.slave_master_info. For more information on the connection metadata repository, see Section 17.2.4, “Relay Log and Replication Metadata Repositories”.

The FILE setting wrote the replica's connection metadata repository to a file, which was named master.info by default. The name could be changed using the --master-info-file option.

max_relay_log_size

If a write by a replica to its relay log causes the current log file size to exceed the value of this variable, the replica 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.3, “Replication Threads”.

relay_log

The base name for relay log files. 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.

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.

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 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 Section 4.2.2, “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.

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

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

Holds the base name and complete path to the relay log file. The maximum variable length is 256. This variable is set by the server and is read only.

relay_log_index

The name for the relay log index file. The maximum variable length is 256. If you do not specify this variable, but the relay_log system variable is specified, its value is used as the default base name for the relay log index file. If relay_log 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 system variable. You can use the relay_log_index system variable 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 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 Section 4.2.2, “Specifying Program Options”.

relay_log_info_file

The use of this system variable is now deprecated. It was used to set the file name for the replica's applier metadata repository if relay_log_info_repository=FILE was set. relay_log_info_file and the use of the relay_log_info_repository system variable are deprecated because the use of a file for the applier metadata repository has been superseded by crash-safe tables. For information about the applier metadata repository, see Section 17.2.4.2, “Replication Metadata Repositories”.

relay_log_info_repository

The use of this system variable is now deprecated. The setting TABLE is the default, and is required when multiple replication channels are configured. The TABLE setting for the replica's applier metadata repository is also required to make replication resilient to unexpected halts. See Section 17.4.2, “Handling an Unexpected Halt of a Replica” for more information. The alternative setting FILE was previously deprecated.

With the default setting, the replica stores its applier metadata repository as an InnoDB table in the mysql system database named mysql.slave_relay_log_info. For more information on the applier metadata repository, see Section 17.2.4, “Relay Log and Replication Metadata Repositories”.

The FILE setting wrote the replica's applier metadata repository to a file, which was named relay-log.info by default. The name could be changed using the relay_log_info_file system variable.

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

If enabled, this variable 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 source then continues.

This global variable is read-only at runtime. Its value can be set with the --relay-log-recovery option at replica server startup, which should be used following an unexpected halt of a replica to ensure that no possibly corrupted relay logs are processed, and must be used in order to guarantee a crash-safe replica. The default value is 0 (disabled). For information on the combination of settings on a replica that is most resilient to unexpected halts, see Section 17.4.2, “Handling an Unexpected Halt of a Replica”.

For a multithreaded replica (where slave_parallel_workers is greater than 0), setting --relay-log-recovery at startup automatically handles any inconsistencies and gaps in the sequence of transactions that have been executed from the relay log. These gaps can occur when file position based replication is in use. (For more details, see Section 17.5.1.34, “Replication and Transaction Inconsistencies”.) The relay log recovery process deals with gaps using the same method as the START REPLICA | SLAVE UNTIL SQL_AFTER_MTS_GAPS statement would. When the replica reaches a consistent gap-free state, the relay log recovery process goes on to fetch further transactions from the source beginning at the SQL (applier) thread position. When GTID-based replication is in use, this process is unnecessary, and from MySQL 8.0.18 a multithreaded replica automatically skips relay log recovery when MASTER_AUTO_POSITION is set to ON, so the setting for relay_log_recovery makes no difference in that case.

relay_log_space_limit

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

replication_optimize_for_static_plugin_config

Use shared locks, and avoid unnecessary lock acquisitions, to improve performance for semisynchronous replication. While this system variable is enabled, the semisynchronous replication plugin cannot be uninstalled, so you must disable the system variable before the uninstall can complete.

This system variable can be enabled before or after installing the semisynchronous replication plugin, and can be enabled while replication is running. Semisynchronous replication source servers can also get performance benefits from enabling this system variable, because they use the same locking mechanisms as the replicas.

replication_sender_observe_commit_only

Limit callbacks to improve performance for semisynchronous replication. This system variable can be enabled before or after installing the semisynchronous replication plugin, and can be enabled while replication is running. Semisynchronous replication source servers can also get performance benefits from enabling this system variable, because they use the same locking mechanisms as the replicas.

report_host

The host name or IP address of the replica to be reported to the source during replica registration. This value appears in the output of SHOW REPLICAS | SHOW SLAVE HOSTS on the source server. Leave the value unset if you do not want the replica to register itself with the source.

report_password

The account password of the replica to be reported to the source during replica registration. This value appears in the output of SHOW REPLICAS | SHOW SLAVE HOSTS on the source server if the source 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

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

The default value for this option is the port number actually used by the replica. This is also the default value displayed by SHOW REPLICAS | SHOW SLAVE HOSTS.

report_user

The account user name of the replica to be reported to the source during replica registration. This value appears in the output of SHOW REPLICAS | SHOW SLAVE HOSTS on the source server if the source 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_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 sources and coordinator threads on replicas. 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 replica server. 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 replica-side semisynchronous replication plugin is installed.

rpl_semi_sync_slave_trace_level

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

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

rpl_stop_slave_timeout

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

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 REPLICA | SLAVE statements.

This variable affects only the client that issues a STOP REPLICA | 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 replication I/O and SQL threads to stop, but the replication threads continue to try to stop, and the STOP REPLICA | SLAVE instruction remains in effect. Once the replication threads are no longer busy, the STOP REPLICA | SLAVE statement is executed and the replica stops.

slave_checkpoint_group

Sets the maximum number of transactions that can be processed by a multithreaded replica before a checkpoint operation is called to update its status as shown by SHOW REPLICA | SLAVE STATUS. Setting this variable has no effect on replicas for which multithreading is not enabled. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START REPLICA | 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 replica as shown by SHOW REPLICA | SLAVE STATUS. Setting this variable has no effect on replicas 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 source/replica connection protocol if both source and replica 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 REPLICA | SLAVE statement, as well as reconnections made by a running replication I/O thread (for example, after setting the MASTER_RETRY_COUNT option for the CHANGE MASTER TO statement).

Binary log transaction compression (available as of MySQL 8.0.20), which is activated by the binlog_transaction_compression system variable, can also be used to save bandwidth. If you use binary log transaction compression in combination with protocol compression, protocol compression has less opportunity to act on the data, but can still compress headers and those events and transaction payloads that are uncompressed. For more information on binary log transaction compression, see Section 5.4.4.5, “Binary Log Transaction Compression”.

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 source use zlib compression if both the source and replica support that algorithm. If slave_compressed_protocol is disabled, the value of MASTER_COMPRESSION_ALGORITHMS applies. For more information, see Section 4.2.8, “Connection Compression Control”.

As of MySQL 8.0.18, this system variable is deprecated. You should expect it to be removed in a future version of MySQL. See Configuring Legacy Connection Compression.

slave_exec_mode

Controls how a replication 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-source replication, circular replication, and some other special replication scenarios for NDB Cluster Replication. (See Section 23.6.10, “NDB Cluster Replication: Bidrectional and Circular Replication”, and Section 23.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-source 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 replica creates temporary files. Setting this variable takes effect for all replication channels immediately, including running channels. The variable value 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 replication 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 source is huge, the temporary files on the replica are huge, too. Therefore, it might be advisable to use this option to tell the replica 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. 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 replication 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 source 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 source, so that large updates using row-based replication do not cause replication to fail.