15.4.2.4 START REPLICA Statement

START REPLICA [thread_types] [until_option] [connection_options] [channel_option]

thread_types:
    [thread_type [, thread_type] ... ]

thread_type:
    IO_THREAD | SQL_THREAD

until_option:
    UNTIL {   {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
          |   SOURCE_LOG_FILE = 'log_name', SOURCE_LOG_POS = log_pos
          |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
          |   SQL_AFTER_MTS_GAPS  }

connection_options:
    [USER='user_name'] [PASSWORD='user_pass'] [DEFAULT_AUTH='plugin_name'] [PLUGIN_DIR='plugin_dir']


channel_option:
    FOR CHANNEL channel

gtid_set:
    uuid_set [, uuid_set] ...
    | ''

uuid_set:
    uuid:interval[:interval]...

uuid:
    hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh

h:
    [0-9,A-F]

interval:
    n[-n]

    (n >= 1)

START REPLICA starts the replication threads, either together or separately.

START REPLICA requires the REPLICATION_SLAVE_ADMIN privilege (or the deprecated SUPER privilege). START REPLICA causes an implicit commit of an ongoing transaction. See Section 15.3.3, “Statements That Cause an Implicit Commit”.

For the thread type options, you can specify IO_THREAD, SQL_THREAD, both of these, or neither of them. Only the threads that are started are affected by the statement.

• START REPLICA with no thread type options starts all of the replication threads, and so does START REPLICA with both of the thread type options.

• IO_THREAD starts the replication receiver thread, which reads events from the source server and stores them in the relay log.

• SQL_THREAD starts the replication applier thread, which reads events from the relay log and executes them. A multithreaded replica (with replica_parallel_workers > 0) applies transactions using a coordinator thread and multiple applier threads, and SQL_THREAD starts all of these.

Important

START REPLICA sends an acknowledgment to the user after all the replication threads have started. However, the replication receiver thread might not yet have connected to the source successfully, or an applier thread might stop when applying an event right after starting. START REPLICA does not continue to monitor the threads after they are started, so it does not warn you if they subsequently stop or cannot connect. You must check the replica's error log for error messages generated by the replication threads, or check that they are running satisfactorily with SHOW REPLICA STATUS. A successful START REPLICA statement causes SHOW REPLICA STATUS to show Replica_SQL_Running=Yes, but it might or might not show Replica_IO_Running=Yes, because Replica_IO_Running=Yes is only shown if the receiver thread is both running and connected. For more information, see Section 19.1.7.1, “Checking Replication Status”.

The optional FOR CHANNEL channel clause enables you to name which replication channel the statement applies to. Providing a FOR CHANNEL channel clause applies the START REPLICA statement to a specific replication channel. If no clause is named and no extra channels exist, the statement applies to the default channel. If a START REPLICA statement does not have a channel defined when using multiple channels, this statement starts the specified threads for all channels. See Section 19.2.2, “Replication Channels” for more information.

The replication channels for Group Replication (group_replication_applier and group_replication_recovery) are managed automatically by the server instance. START REPLICA cannot be used at all with the group_replication_recovery channel, and should only be used with the group_replication_applier channel when Group Replication is not running. The group_replication_applier channel only has an applier thread and has no receiver thread, so it can be started if required by using the SQL_THREAD option without the IO_THREAD option.

START REPLICA supports pluggable user-password authentication (see Section 8.2.17, “Pluggable Authentication”) with the USER, PASSWORD, DEFAULT_AUTH and PLUGIN_DIR options, as described in the following list. When you use these options, you must start the receiver thread (IO_THREAD option) or all the replication threads; you cannot start the replication applier thread (SQL_THREAD option) alone.

USER

The user name for the account. You must set this if PASSWORD is used. The option cannot be set to an empty or null string.

PASSWORD

The password for the named user account.

DEFAULT_AUTH

The name of the authentication plugin. The default is MySQL native authentication.

PLUGIN_DIR

The location of the authentication plugin.

Important

The password that you set using START REPLICA is masked when it is written to MySQL Server’s logs, Performance Schema tables, and SHOW PROCESSLIST statements. However, it is sent in plain text over the connection to the replica server instance. To protect the password in transit, use SSL/TLS encryption, an SSH tunnel, or another method of protecting the connection from unauthorized viewing, for the connection between the replica server instance and the client that you use to issue START REPLICA.

The UNTIL clause makes the replica start replication, then process transactions up to the point that you specify in the UNTIL clause, then stop again. The UNTIL clause can be used to make a replica proceed until just before the point where you want to skip a transaction that is unwanted, and then skip the transaction as described in Section 19.1.7.3, “Skipping Transactions”. To identify a transaction, you can use mysqlbinlog with the source's binary log or the replica's relay log, or use a SHOW BINLOG EVENTS statement.

You can also use the UNTIL clause for debugging replication by processing transactions one at a time or in sections. If you are using the UNTIL clause to do this, start the replica with --skip-replica-start to prevent the SQL thread from running when the replica server starts. Remove the option or system variable setting after the procedure is complete, so that it is not forgotten in the event of an unexpected server restart.

The SHOW REPLICA STATUS statement includes output fields that display the current values of the UNTIL condition. The UNTIL condition lasts for as long as the affected threads are still running, and is removed when they stop.

The UNTIL clause operates on the replication applier thread (SQL_THREAD option). You can use the SQL_THREAD option or let the replica default to starting both threads. If you use the IO_THREAD option alone, the UNTIL clause is ignored because the applier thread is not started.

The point that you specify in the UNTIL clause can be any one (and only one) of the following options:

SOURCE_LOG_FILE and SOURCE_LOG_POS

These options make the replication applier process transactions up to a position in its relay log, identified by the file name and file position of the corresponding point in the binary log on the source server. The applier thread finds the nearest transaction boundary at or after the specified position, finishes applying the transaction, and stops there. For compressed transaction payloads, specify the end position of the compressed Transaction_payload_event.

These options can still be used when the GTID_ONLY option was set on the CHANGE REPLICATION SOURCE TO statement to stop the replication channel from persisting file names and file positions in the replication metadata repositories. The file names and file positions are tracked in memory.

RELAY_LOG_FILE and RELAY_LOG_POS

These options make the replication applier process transactions up to a position in the replica’s relay log, identified by the relay log file name and a position in that file. The applier thread finds the nearest transaction boundary at or after the specified position, finishes applying the transaction, and stops there. For compressed transaction payloads, specify the end position of the compressed Transaction_payload_event.

These options can still be used when the GTID_ONLY option was set on the CHANGE REPLICATION SOURCE TO statement to stop the replication channel from persisting file names and file positions in the replication metadata repositories. The file names and file positions are tracked in memory.

SQL_BEFORE_GTIDS

This option makes the replication applier start processing transactions and stop when it encounters any transaction that is in the specified GTID set. The encountered transaction from the GTID set is not applied, and nor are any of the other transactions in the GTID set. The option takes a GTID set containing one or more global transaction identifiers as an argument (see GTID Sets). Transactions in a GTID set do not necessarily appear in the replication stream in the order of their GTIDs, so the transaction before which the applier stops is not necessarily the earliest.

SQL_AFTER_GTIDS

This option makes the replication applier start processing transactions and stop when it has processed all of the transactions in a specified GTID set. The option takes a GTID set containing one or more global transaction identifiers as an argument (see GTID Sets).

With SQL_AFTER_GTIDS, the replication threads stop after they have processed all transactions in the GTID set. Transactions are processed in the order received, so it is possible that these include transactions which are not part of the GTID set, but which are received (and processed) before all transactions in the set have been committed. For example, executing START REPLICA UNTIL SQL_AFTER_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56 causes the replica to obtain (and process) all transactions from the source until all of the transactions having the sequence numbers 11 through 56 have been processed, and then to stop without processing any additional transactions after that point has been reached.

In older versions of MySQL, this option could not be used with replica_parallel_workers > 1. In MySQL 9.1, this is no longer an issue, and SQL_AFTER_GTIDS can be used without causing the replica to fall back into single-threaded mode.

SQL_AFTER_MTS_GAPS

For a multithreaded replica only (with replica_parallel_workers > 0), this option makes the replica process transactions up to the point where there are no more gaps in the sequence of transactions executed from the relay log. When using a multithreaded replica, there is a chance of gaps occurring in the following situations:

• The coordinator thread is stopped.

• An error occurs in the applier threads.

• mysqld shuts down unexpectedly.

When a replication channel has gaps, the replica’s database is in a state that might never have existed on the source. The replica tracks the gaps internally and disallows CHANGE REPLICATION SOURCE TO statements that would remove the gap information if they executed.

All replicas are multithreaded by default. When replica_preserve_commit_order=ON on the replica (the default), gaps should not occur except in the specific situations listed in the description for this variable. If replica_preserve_commit_order is OFF, the commit order of transactions is not preserved, so the chance of gaps occurring is much larger.

If GTIDs are not in use and you need to change a failed multithreaded replica to single-threaded mode, you can issue the following series of statements, in the order shown:

START REPLICA UNTIL SQL_AFTER_MTS_GAPS;
SET @@GLOBAL.replica_parallel_workers = 0;
START REPLICA SQL_THREAD;