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
| MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
| 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 one or both of the
replication threads. From MySQL 8.0.22, use
START REPLICA
in place of
START SLAVE
, which is deprecated
from that release. In releases before MySQL 8.0.22, use
START SLAVE
.
START REPLICA
with no
thread_type
options starts both of
the replication threads. The replication I/O (receiver) thread
reads events from the source server and stores them in the relay
log. The replication SQL (applier) thread reads events from the
relay log and executes them. START REPLICA
requires the
REPLICATION_SLAVE_ADMIN
privilege
(or the deprecated SUPER
privilege).
If START REPLICA
succeeds in starting the
replication threads, it returns without any error. However, even
in that case, it might be that the replication threads start and
then later stop (for example, because they do not manage to
connect to the source or read its binary log, or some other
problem). START REPLICA
does not warn you
about this. 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
.
START REPLICA
causes an implicit commit of an
ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit”.
gtid_next
must be set to
AUTOMATIC
before issuing this statement.
The optional FOR CHANNEL
clause enables you
to name which replication channel the statement applies to.
Providing a channel
FOR CHANNEL
clause applies the
channel
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. This statement is
disallowed for the group_replication_recovery
channel. See Section 17.2.2, “Replication Channels” for more
information.
You can add IO_THREAD
and
SQL_THREAD
options to the statement to name
which of the threads to start. Note that the Group Replication
applier channel (group_replication_applier
)
has no I/O (receiver) thread, only an SQL (applier) thread.
Specifying the IO_THREAD
or
SQL_THREAD
options when you start this
channel has no benefit.
START REPLICA
supports pluggable
user-password authentication with the USER
,
PASSWORD
, DEFAULT_AUTH
and
PLUGIN_DIR
options, as described in the
following list:
USER
: User name. Cannot be set to an empty or null string, or left unset ifPASSWORD
is used.PASSWORD
: Password.DEFAULT_AUTH
: Name of plugin; default is MySQL native authentication.PLUGIN_DIR
: Location of plugin.
You cannot use the SQL_THREAD
option when
specifying any of USER
,
PASSWORD
, DEFAULT_AUTH
, or
PLUGIN_DIR
, unless the
IO_THREAD
option is also provided.
For more information, see Section 6.2.17, “Pluggable Authentication”.
If an insecure connection is used with any these options, the server issues the warning Sending passwords in plain text without SSL/TLS is extremely insecure.
START REPLICA ... UNTIL
supports two
additional options for use with global transaction identifiers
(GTIDs) (see Section 17.1.3, “Replication with Global Transaction Identifiers”). Each of these
takes a set of one or more global transaction identifiers
gtid_set
as an argument (see
GTID Sets, for more
information).
When no thread_type
is specified,
START REPLICA UNTIL SQL_BEFORE_GTIDS
causes
the replication SQL thread to process transactions until it has
reached the first transaction whose GTID is
listed in the gtid_set
.
START REPLICA UNTIL SQL_AFTER_GTIDS
causes
the replication threads to process all transactions until the
last
transaction in the
gtid_set
has been processed by both
threads. In other words, START REPLICA UNTIL
SQL_BEFORE_GTIDS
causes the replication SQL thread to
process all transactions occurring before the first GTID in the
gtid_set
is reached, and
START REPLICA UNTIL SQL_AFTER_GTIDS
causes
the replication threads to handle all transactions, including
those whose GTIDs are found in
gtid_set
, until each has encountered
a transaction whose GTID is not part of the set.
SQL_BEFORE_GTIDS
and
SQL_AFTER_GTIDS
each support the
SQL_THREAD
and IO_THREAD
options, although using IO_THREAD
with them
currently has no effect.
For example, START REPLICA SQL_THREAD UNTIL
SQL_BEFORE_GTIDS =
3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56
causes the
replication SQL thread to process all transactions originating
from the source whose
server_uuid
is
3E11FA47-71CA-11E1-9E33-C80AA9429562
until it
encounters the transaction having sequence number 11; it then
stops without processing this transaction. In other words, all
transactions up to and including the transaction with sequence
number 10 are processed. Executing START REPLICA
SQL_THREAD UNTIL SQL_AFTER_GTIDS =
3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56
, on the
other hand, would cause the replication SQL thread to obtain all
transactions just mentioned from the source, including all of
the transactions having the sequence numbers 11 through 56, and
then to stop without processing any additional transactions;
that is, the transaction having sequence number 56 would be the
last transaction fetched by the replication SQL thread.
When using a multithreaded replica
(replica_parallel_workers
or
slave_parallel_workers
> 0),
there is a chance of gaps in the sequence of transactions that
have been executed from the relay log in the following cases:
The coordinator thread was stopped
An error occurs in the applier threads
mysqld shuts down unexpectedly
From MySQL 8.0.27, all replicas are multithreaded by default.
When
replica_preserve_commit_order=ON
or
slave_preserve_commit_order=ON
is set for the replica, which is also the default setting from
MySQL 8.0.27, gaps should not occur except in the specific
situations listed in the description for
replica_preserve_commit_order
and
slave_preserve_commit_order
.
If
replica_preserve_commit_order=OFF
or
slave_preserve_commit_order=OFF
is set for the replica, which is the default before MySQL
8.0.27, the commit order of transactions is not preserved, so
the chance of gaps occurring is much larger.
Use the START REPLICA UNTIL
SQL_AFTER_MTS_GAPS
statement to cause a multithreaded
replica's worker threads to only run until no more gaps are
found in the relay log, and then to stop. This statement can
take an SQL_THREAD
option, but the effects of
the statement remain unchanged. It has no effect on the
replication I/O (receiver) thread (and cannot be used with the
IO_THREAD
option).
Before MySQL 8.0.26, issuing START REPLICA
on
a multithreaded replica with gaps in the sequence of
transactions executed from the relay log generates a warning. In
such a situation, the solution is to use START REPLICA
UNTIL SQL_AFTER_MTS_GAPS
, then issue
RESET
REPLICA
to remove any remaining relay logs. From MySQL
8.0.26, the process of checking for gaps in the sequence of
transactions is skipped entirely when GTID-based replication and
GTID auto-positioning are in use, because gaps in transactions
can be resolved using GTID auto-positioning. In that situation,
START REPLICA UNTIL SQL_AFTER_MTS_GAPS
just
stops the applier thread when it finds the first event to
execute, and does not attempt to check for gaps in the sequence
of transactions. See
Section 17.5.1.34, “Replication and Transaction Inconsistencies”
for more information.
To change a failed multithreaded replica to single-threaded mode, you can issue the following series of statements, in the order shown:
START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
SET @@GLOBAL.slave_parallel_workers = 0;
START SLAVE SQL_THREAD;
Or from MySQL 8.0.26:
START REPLICA UNTIL SQL_AFTER_MTS_GAPS;
SET @@GLOBAL.replica_parallel_workers = 0;
START REPLICA SQL_THREAD;
It is possible to view the entire text of a running
START REPLICA
statement, including any
USER
or PASSWORD
values
used, in the output of SHOW
PROCESSLIST
. This is also true for the text of a
running CHANGE REPLICATION SOURCE
TO
| CHANGE MASTER TO
statement, including any values it employs for
SOURCE_USER
|
MASTER_USER
or
SOURCE_PASSWORD
|
MASTER_PASSWORD
.
START REPLICA
sends an acknowledgment to the
user after both the replication I/O (receiver) thread and the
replication SQL (applier) thread have started. However, the
replication receiver thread may not yet have connected. For this
reason, a successful START REPLICA
causes
SHOW
REPLICA STATUS
to show
Replica_SQL_Running=Yes
, but this does not
guarantee that Replica_IO_Running=Yes
(because Replica_IO_Running=Yes
only if the
receiver thread is running and connected).
For more information, see Section 13.7.7.35, “SHOW REPLICA STATUS Statement”,
and Section 17.1.7.1, “Checking Replication Status”.
An UNTIL
clause
(until_option
, in the preceding
grammar) may be added to specify that the replica should start
and run until the replication SQL thread reaches a given point
in the source's binary log or in the replica's relay log. Use
one of the following pairs of options to specify the position:
MASTER_LOG_POS
andMASTER_LOG_FILE
for the binary log (to MySQL 8.0.22).SOURCE_LOG_POS
andSOURCE_LOG_FILE
for the binary log (from MySQL 8.0.23).RELAY_LOG_POS
andRELAY_LOG_FILE
for the relay log.
For compressed transaction payloads, the position must be based
on the compressed Transaction_payload_event
.
When the SQL thread reaches the point specified, it stops. If
the SQL_THREAD
option is specified in the
statement, it starts only the SQL thread. Otherwise, it starts
both replication threads. If the SQL thread is running, the
UNTIL
clause is ignored and a warning is
issued. You cannot use an UNTIL
clause with
the IO_THREAD
option.
It is also possible with START REPLICA UNTIL
to specify a stop point relative to a given GTID or set of GTIDs
using one of the options SQL_BEFORE_GTIDS
or
SQL_AFTER_GTIDS
, as explained previously in
this section. When using one of these options, you can specify
SQL_THREAD
, IO_THREAD
,
both of these, or neither of them. If you specify only
SQL_THREAD
, then only the replication SQL
thread (applier) is affected by the statement; if only
IO_THREAD
is used, then only the replication
I/O (receiver) thread is affected. If both
SQL_THREAD
and IO_THREAD
are used, or if neither of them is used, then both the SQL
(applier) and I/O (receiver) threads are affected by the
statement.
For an UNTIL
clause, you must specify any one
of the following:
Both a log file name and a position in that file
Either of
SQL_BEFORE_GTIDS
orSQL_AFTER_GTIDS
SQL_AFTER_MTS_GAPS
Do not mix source and relay log options. Do not mix log file options with GTID options.
The UNTIL
clause can still be used with the
file name and file position options 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.
The UNTIL
clause is not supported for
multithreaded replicas except when also using
SQL_AFTER_MTS_GAPS
. If
UNTIL
is used on a multithreaded replica
without SQL_AFTER_MTS_GAPS
, the replica
operates in single-threaded (sequential) mode for replication
until the point specified by the UNTIL
clause
is reached.
Any UNTIL
condition is reset by a subsequent
STOP
REPLICA
statement, a START REPLICA
statement that includes no UNTIL
clause, or a
server restart.
When specifying a log file and position, you can use the
IO_THREAD
option with START REPLICA
... UNTIL
even though only the SQL thread is affected
by this statement. The IO_THREAD
option is
ignored in such cases. The preceding restriction does not apply
when using one of the GTID options
(SQL_BEFORE_GTIDS
and
SQL_AFTER_GTIDS
); the GTID options support
both SQL_THREAD
and
IO_THREAD
, as explained previously in this
section.
The UNTIL
clause can be useful for debugging
replication, or to cause replication to proceed until just
before the point where you want to avoid having the replica
replicate an event. For example, if an unwise
DROP TABLE
statement was executed
on the source, you can use UNTIL
to tell the
replica to execute up to that point but no farther. To find what
the event is, use mysqlbinlog with the
source's binary log or the replica's relay log, or by using a
SHOW BINLOG EVENTS
statement.
If you are using UNTIL
to have the replica
process replicated queries in sections, it is recommended that
you start the replica with the
--skip-slave-start
option, or
from MySQL 8.0.24, the
skip_slave_start
system
variable, 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.