Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual STOP SLAVE Syntax

STOP SLAVE [thread_types]

    [thread_type [, thread_type] ... ]

thread_type: IO_THREAD | SQL_THREAD

    FOR CHANNEL channel

Stops the slave threads. STOP SLAVE requires the SUPER privilege. Recommended best practice is to execute STOP SLAVE on the slave before stopping the slave server (see Section 5.1.12, “The Server Shutdown Process”, for more information).

When using the row-based logging format: You should execute STOP SLAVE or STOP SLAVE SQL_THREAD on the slave prior to shutting down the slave server if you are replicating any tables that use a nontransactional storage engine (see the Note later in this section).

Like START SLAVE, this statement may be used with the IO_THREAD and SQL_THREAD options to name the thread or threads to be stopped.

In MySQL 5.7, STOP SLAVE causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

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

In MySQL 5.7.2 and later, you can control how long STOP SLAVE waits before timing out by setting the rpl_stop_slave_timeout system variable. This can be used to avoid deadlocks between STOP SLAVE and other slave SQL statements using different client connections to the slave. (Bug #16856735)

Prior to MySQL 5.7.4, it was necessary to issue this statement on a running slave prior to executing CHANGE MASTER TO. In MySQL 5.7.4 and later, this is no longer always the case; some CHANGE MASTER TO statements are now allowed while the slave is running, depending on the states of the slave SQL and I/O threads. However, using STOP SLAVE prior to executing CHANGE MASTER TO in such cases is still supported. See Section, “CHANGE MASTER TO Syntax”, and Section 17.3.6, “Switching Masters During Failover”, for more information.

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 STOP SLAVE statement to a specific replication channel. If a STOP SLAVE statement does not have a channel defined when using multiple channels, this statement stops the specified threads for all channels. Beginning with MySQL 5.7.9, this statement cannot be used with the group_replication_recovery channel. See Section 17.2.3, “Replication Channels” for more information.

When using statement-based replication: changing the master while it has open temporary tables is potentially unsafe. This is one of the reasons why statement-based replication of temporary tables is not recommended. You can find out whether there are any temporary tables on the slave by checking the value of Slave_open_temp_tables; when using statement-based replication, this value should be 0 before executing CHANGE MASTER TO. In MySQL 5.7.4 and later, if there are any temporary tables open on the slave, issuing a CHANGE MASTER TO statement after issuing a STOP SLAVE causes an ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO warning.

When using a multi-threaded slave (slave_parallel_workers is a nonzero value), any gaps in the sequence of transactions executed from the relay log are closed as part of stopping the worker threads. If the slave is stopped unexpectedly (for example due to an error in a worker thread, or another thread issuing KILL) while a STOP SLAVE statement is executing, the sequence of executed transactions from the relay log may become inconsistent. See Section, “Replication and Transaction Inconsistencies” for more information.


In MySQL 5.7, STOP SLAVE waits until the current replication event group affecting one or more nontransactional tables has finished executing (if there is any such replication group), or until you issue a KILL QUERY or KILL CONNECTION statement. (Bug #319, Bug #38205)

Download this Manual
User Comments
  Posted by Ralf Hauser on November 25, 2004
can this statement be used for a backup that allows for other (application) database operations in parallel (i.e. no locking as per; if so, will the user apart from a slight service degradation that e.g. only 1 instead of 2 slaves are available not notice that the backup is happening (in contrast to "mysqlhotcopy ")?

or would one rather need a temporary DISCONNECT or PAUSE SLAVE command for such a backup?

see also
Sign Up Login You must be logged in to post a comment.