SQL Statements for Controlling Replica Servers CHANGE MASTER TO  causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit” . For some of the options of the  CHANGE MASTER TO  statement, you must issue a  STOP REPLICA | SLAVE  statement prior to issuing a  CHANGE MASTER TO  statement (and a  START REPLICA | SLAVE statement afterwards). Sometimes, you only need to stop the replication SQL thread or the replication I/O thread, not both: •When the SQL thread is stopped, you can execute  CHANGE MASTER TO  using any combination that is otherwise allowed of  RELAY_LOG_FILE ,  RELAY_LOG_POS , and  MASTER_DELAY  options, even if the replication I/O thread is running. No other options may be used with this statement when the I/O thread is running. •When the I/O thread is stopped, you can execute  CHANGE MASTER TO  using any of the options for this statement (in any allowed combination)  except RELAY_LOG_FILE ,  RELAY_LOG_POS ,  MASTER_DELAY , or  MASTER_AUTO_POSITION = 1  even when the SQL thread is running. •Both the SQL thread and the I/O thread must be stopped before issuing a CHANGE MASTER TO  statement that employs  MASTER_AUTO_POSITION = 1  or ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS . You can check the current state of the replication SQL thread and replication I/O thread using  SHOW REPLICA | SLAVE STATUS . Note that the Group Replication applier channel ( group_replication_applier ) has no I/O thread, only an SQL thread. For more information, see Section 17.4.8, “Switching Sources During Failover” . If you are using statement-based replication and temporary tables, it is possible for a  CHANGE MASTER TO  statement following a  STOP REPLICA | SLAVE  statement to leave behind temporary tables on the replica. A warning ( ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO ) is now issued whenever this occurs. You can avoid this in such cases by making sure that the value of the  Slave_open_temp_tables system status variable is equal to 0 prior to executing such a  CHANGE MASTER TO  statement. CHANGE MASTER TO  is useful for setting up a replica when you have the snapshot of the source and have recorded the source’s binary log coordinates corresponding to the time of the snapshot. After loading the snapshot into the replica to synchronize it with the source, you can run  CHANGE MASTER TO MASTER_LOG_FILE=’ log_name ’, MASTER_LOG_POS= log_pos  on the replica to specify the coordinates at which the replica should begin reading the source’s binary log. The following example changes the source server the replica uses and establishes the source’s binary log coordinates from which the replica begins reading: The next example shows an operation that is less frequently employed. It is used when the replica has relay log files that you want it to execute again for some reason. To do this, the source need not be reachable. You need only use  CHANGE MASTER TO  and start the SQL thread ( START REPLICA | SLAVE SQL_THREAD ): CHANGE MASTER TO MASTER_HOST=’source2.example.com’, MASTER_USER=’replication’, MASTER_PASSWORD=’ password ’, MASTER_PORT=3306, MASTER_LOG_FILE=’source2-bin.001’, MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10; CHANGE MASTER TO RELAY_LOG_FILE=’replica-relay-bin.006’, 2745