MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
START SLAVE [thread_types
] [until_option
] [connection_options
]thread_types
: [thread_type
[,thread_type
] ... ]thread_type
: IO_THREAD | SQL_THREADuntil_option
: UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} =gtid_set
| MASTER_LOG_FILE = 'log_name
', MASTER_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
']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 SLAVE
with no
thread_type
options starts both of
the replication threads. The I/O thread reads events from the
replication source server and stores them in the relay log. The
SQL thread reads events from the relay log and executes them.
START SLAVE
requires the
SUPER
privilege.
If START SLAVE
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 SLAVE
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 SLAVE STATUS
.
START SLAVE
causes an implicit commit of an
ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit”.
Beginning with MySQL 5.6.11,
gtid_next
must be set to
AUTOMATIC
before issuing this statement (Bug
#16062608).
START SLAVE
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 if
PASSWORD
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.11, “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 SLAVE ... 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 SLAVE 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 SLAVE 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 SLAVE 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 SLAVE 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 SLAVE 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 SLAVE
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.
Prior to MySQL 5.6.14, SQL_AFTER_GTIDS
did
not stop the replica once the indicated transaction was
completed, but waited until another GTID event was received (Bug
#14767986).
Use the START SLAVE
UNTIL SQL_AFTER_MTS_GAPS
statement to cause a
multithreaded replica's SQL threads to 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 thread (and cannot be used with the
IO_THREAD
option). START SLAVE UNTIL
SQL_AFTER_MTS_GAPS
should be used before switching the
replica from multithreaded mode to single-threaded mode (that
is, when resetting
slave_parallel_workers
back to
0 from a positive, nonzero value) after the replica has failed
with errors in multithreaded mode.
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;
If you were running the failed multithreaded replica with
relay_log_recovery
enabled,
then you must issue START SLAVE UNTIL
SQL_AFTER_MTS_GAPS
prior to executing
CHANGE MASTER TO
. Otherwise the
latter statement fails.
It is possible to view the entire text of a running
START SLAVE ...
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 MASTER TO
statement, including any values it employs for
MASTER_USER
or
MASTER_PASSWORD
.
START SLAVE
sends an
acknowledgment to the user after both the I/O thread and the SQL
thread have started. However, the I/O thread may not yet have
connected. For this reason, a successful
START SLAVE
causes
SHOW SLAVE STATUS
to show
Slave_SQL_Running=Yes
, but this does not
guarantee that Slave_IO_Running=Yes
(because
Slave_IO_Running=Yes
only if the I/O thread
is running and connected). For more
information, see Section 13.7.5.35, “SHOW SLAVE STATUS Statement”, and
Section 17.1.5.1, “Checking Replication Status”.
You can add IO_THREAD
and
SQL_THREAD
options to the statement to name
which of the threads to start. The SQL_THREAD
option is disallowed when specifying any of
USER
, PASSWORD
,
DEFAULT_AUTH
, or
PLUGIN_DIR
, unless the
IO_THREAD
option is also provided.
An UNTIL
clause
(until_option
, in the preceding
grammar) may be added to specify that the replica should start
and run until the SQL thread reaches a given point in the
source's binary log, specified by the
MASTER_LOG_POS
and
MASTER_LOG_FILE
options, or a given point in
the replica's relay log, indicated with the
RELAY_LOG_POS
and
RELAY_LOG_FILE
options. 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 SLAVE 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 is affected by the statement; if only
IO_THREAD
is used, then only the replication
I/O thread is affected. If both SQL_THREAD
and IO_THREAD
are used, or if neither of them
is used, then both the replication threads are affected by the
statement.
The UNTIL
clause is not supported for
multithreaded replicas except when also using
SQL_AFTER_MTS_GAPS
.
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
or
SQL_AFTER_GTIDS
SQL_AFTER_MTS_GAPS
Do not mix binary log and relay log options. Do not mix log file options with GTID options.
Any UNTIL
condition is reset by a subsequent
STOP SLAVE
statement, a
START SLAVE
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 SLAVE
... 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 to
prevent the SQL thread from running when the replica server
starts. It is probably best to use this option in an option file
rather than on the command line, so that an unexpected server
restart does not cause it to be forgotten.
The SHOW SLAVE STATUS
statement
includes output fields that display the current values of the
UNTIL
condition.