start slave until 用法舉例

mysql> start slave until SQL_BEFORE_GTIDS ='96359478-a845-11e6-a3d6-000c296e817a:44' for channel 'master_133070';

如上所示，在啟動slave時，限制slave執行到的主庫gtid位置（不包括96359478-a845-11e6-a3d6-000c296e817a:44’）；
當sql執行緒停止後，slave狀態如下：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 13307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 5761
               Relay_Log_File: mysql-relay-bin-master_133070.000007
                Relay_Log_Pos: 982
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: mysql.ibbackup_binlog_marker
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.backup_%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5450
              Relay_Log_Space: 5883
              Until_Condition: SQL_BEFORE_GTIDS
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100106302
                  Master_UUID: 96359478-a845-11e6-a3d6-000c296e817a
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 96359478-a845-11e6-a3d6-000c296e817a:26-44
            Executed_Gtid_Set: 96359478-a845-11e6-a3d6-000c296e817a:1-43
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master_133070
           Master_TLS_Version: 
1 row in set (0.00 sec)

執行start slave,可以重新開啟sql thread。

更詳細的用法，請看

mysql> ? start slave
Name: 'START SLAVE'
Description:
Syntax:
START SLAVE [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
          |   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 SLAVE with no thread_type options starts both of the slave
threads. The I/O thread reads events from the master 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 slave threads, it returns
without any error. However, even in that case, it might be that the
slave threads start and then later stop (for example, because they do
not manage to connect to the master or read its binary log, or some
other problem). START SLAVE does not warn you about this. You must
check the slave's error log for error messages generated by the slave
threads, or check that they are running satisfactorily with SHOW SLAVE
STATUS.

In MySQL 5.7, START SLAVE causes an implicit commit of an ongoing
transaction. See
http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html.

Beginning with MySQL 5.7.1, gtid_next must be set to AUTOMATIC before
issuing this statement (Bug #16062608).

The optional FOR CHANNEL channel clause added in MySQL 5.7.6 enables
you to choose which replication channel the statement applies to. If no
clause is set and no extra channels exist, the statement applies to the
default channel and behaves the same as versions of MySQL prior to
5.7.6. Providing a FOR CHANNEL channel clause applies the START SLAVE
statement to a specific replication channel. If a START SLAVE statement
does not have a channel defined when using multiple channels, this
statement starts the specified threads for all channels. Beginning with
MySQL 5.7.9, this statement is disallowed for the
group_replication_recovery channel. See
http://dev.mysql.com/doc/refman/5.7/en/replication-channels.html for
more information.

MySQL 5.7 supports pluggable user-password authentication with START
SLAVE with the USER, PASSWORD, DEFAULT_AUTH and PLUGIN_DIR options, as
described in the following list:

o USER: User name. Cannot be set to an empty or null string, or left
  unset if PASSWORD is used.

o PASSWORD: Password.

o DEFAULT_AUTH: Name of plugin; default is MySQL native authentication.

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

See
http://dev.mysql.com/doc/refman/5.7/en/pluggable-authentication.html,
for more information.

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
http://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html). Each of
these takes a set of one or more global transaction identifiers
gtid_set as an argument (see
http://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#
replication-gtids-concepts-gtid-sets, for more information).

When no thread_type is specified, START SLAVE UNTIL SQL_BEFORE_GTIDS
causes the slave 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 slave 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 slave 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 slave 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 slave SQL thread
to process all transactions originating from the master 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 slave SQL thread to obtain all transactions just mentioned
from the master, 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 slave SQL
thread.

Prior to MySQL 5.7.3, SQL_AFTER_GTIDS did not stop the slave once the
indicated transaction was completed, but waited until another GTID
event was received (Bug #14767986).

When using a multi-threaded slave, there is a chance of gaps in the
sequence of transactions that have been executed from the relay log in
the following cases:

o killing the coordinator thread

o after an error occurs in the worker threads

o mysqld shuts down unexpectedly

Use the START SLAVE UNTIL SQL_AFTER_MTS_GAPS statement to cause a
multi-threaded slave'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 slave I/O thread (and cannot be used
with the IO_THREAD option).

Issuing START SLAVE on a multi-threaded slave 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 SLAVE UNTIL
SQL_AFTER_MTS_GAPS, then issue RESET SLAVE to remove any remaining
relay logs. See
http://dev.mysql.com/doc/refman/5.7/en/replication-features-transaction
-inconsistencies.html for more information.

To change a failed multi-threaded slave 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;

*Note*: 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.

URL: http://dev.mysql.com/doc/refman/5.7/en/start-slave.html