CHANGE MASTER TO option [, option] ...

option: {
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_RETRY_COUNT = count
  | MASTER_DELAY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = 'source_log_name'
  | MASTER_LOG_POS = source_log_pos
  | MASTER_AUTO_POSITION = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_CRL = 'crl_file_name'
  | MASTER_SSL_CRLPATH = 'crl_directory_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)
}

server_id_list:
    [server_id [, server_id] ... ]

CHANGE MASTER TO changes the parameters that the replica uses for connecting to the replication source server, for reading the source's binary log, and reading the replica's relay log. It also updates the contents of the replication metadata repositories (see Section 17.2.2, “Relay Log and Replication Metadata Repositories”). CHANGE MASTER TO requires the SUPER privilege.

To use CHANGE MASTER TO, the replication threads must be stopped (use STOP SLAVE if necessary). In MySQL 5.6.11 and later, gtid_next must also be set to AUTOMATIC (Bug #16062608).

Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. For example, if the password to connect to your MySQL source has changed, issue these statements to tell the replica about the new password:

STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication

MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the replica about how to connect to its source:

The MASTER_SSL_xxx options provide information about using SSL for the connection. They correspond to the --ssl-xxx options described in Command Options for Encrypted Connections, and Section 17.3.8, “Setting Up Replication to Use Encrypted Connections”. These options can be changed even on replicas that are compiled without SSL support. They are saved to the connection metadata repository, but are ignored if the replica does not have SSL support enabled. MASTER_SSL_CRL and MASTER_SSL_CRLPATH were added in MySQL 5.6.3.

MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries. The default is 60.

MASTER_RETRY_COUNT, added in MySQL 5.6.1, limits the number of reconnection attempts and updates the value of the Master_Retry_Count column in the output of SHOW SLAVE STATUS (also added in MySQL 5.6.1). The default value is 24 * 3600 = 86400. MASTER_RETRY_COUNT is intended to replace the older --master-retry-count server option, and is now the preferred method for setting this limit. You are encouraged not to rely on --master-retry-count in new applications and, when upgrading to MySQL 5.6.1 or later from earlier versions of MySQL, to update any existing applications that rely on it, so that they use CHANGE MASTER TO ... MASTER_RETRY_COUNT instead.

MASTER_DELAY specifies how many seconds behind the source the replica must lag. An event received from the source is not executed until at least interval seconds later than its execution on the source. The default is 0. An error occurs if interval is not a nonnegative integer in the range from 0 to 2³¹−1. For more information, see Section 17.3.10, “Delayed Replication”. This option was added in MySQL 5.6.0.

MASTER_BIND is for use on replicas having multiple network interfaces, and determines which of the replica's network interfaces is chosen for connecting to the source.

The address configured with this option, if any, can be seen in the Master_Bind column of the output from SHOW SLAVE STATUS. If you are using tables for the replication metadata repositories (the server was started with master_info_repository=TABLE), the value can also be seen as the Master_bind column of the mysql.slave_master_info table.

The ability to bind a replica to a specific network interface was added in MySQL 5.6.2. This is also supported in MySQL NDB Cluster 7.3 and later.

MASTER_HEARTBEAT_PERIOD sets the interval in seconds between replication heartbeats. Whenever the source's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the source only if there are no unsent events in the binary log file for a period longer than interval.

If you are logging source connection information to tables, MASTER_HEARTBEAT_PERIOD can be seen as the value of the Heartbeat column of the mysql.slave_master_info table.

Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2.

Setting @@GLOBAL.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value.

MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the replication I/O thread should begin reading from the source the next time the thread starts. RELAY_LOG_FILE and RELAY_LOG_POS are the coordinates at which the replication SQL thread should begin reading from the relay log the next time the thread starts. If you specify either of MASTER_LOG_FILE or MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS. In MySQL 5.6.5 and later, if you specify either of MASTER_LOG_FILE or MASTER_LOG_POS, you also cannot specify MASTER_AUTO_POSITION = 1 (described later in this section). If neither of MASTER_LOG_FILE or MASTER_LOG_POS is specified, the replica uses the last coordinates of the replication SQL thread before CHANGE MASTER TO was issued. This ensures that there is no discontinuity in replication, even if the replication SQL thread was late compared to the replication I/O thread, when you merely want to change, say, the password to use.

MASTER_AUTO_POSITION was added in MySQL 5.6.5. If MASTER_AUTO_POSITION = 1 is used with CHANGE MASTER TO, the replica attempts to connect to the source using the GTID-based replication protocol.

When using GTIDs, the replica tells the source which transactions it has already received, executed, or both. To compute this set, it reads the global value of gtid_executed and the value of the Retrieved_gtid_set column from SHOW SLAVE STATUS. Since the GTID of the last transmitted transaction is included in Retrieved_gtid_set even if the transaction was only partially transmitted, the last received GTID is subtracted from this set. Thus, the replica computes the following set:

UNION(@@GLOBAL.gtid_executed, Retrieved_gtid_set - last_received_GTID)

This set is sent to the source as part of the initial handshake, and the source sends back all transactions that it has executed which are not part of the set. If any of these transactions have been already purged from the source's binary log, the source sends the error ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the replica, and replication does not start.

When GTID-based replication is employed, the coordinates represented by MASTER_LOG_FILE and MASTER_LOG_POS are not used, and global transaction identifiers are used instead. Thus the use of either or both of these options together with MASTER_AUTO_POSITION causes an error.

Beginning with MySQL 5.6.10, you can see whether replication is running with autopositioning enabled by checking the output of SHOW SLAVE STATUS. (Bug #15992220)

gtid_mode must also be enabled before issuing CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1. Otherwise, the statement fails with an error.

To revert to the older file-based replication protocol after using GTIDs, you can issue a new CHANGE MASTER TO statement that specifies MASTER_AUTO_POSITION = 0, as well as at least one of MASTER_LOG_FILE or MASTER_LOG_POS.

CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay log files are kept; the relay_log_purge global variable is set silently to 0.

Prior to MySQL 5.6.2, RELAY_LOG_FILE required an absolute path. Beginning with MySQL 5.6.2, the path can be relative, in which case it is assumed to be relative to the replica's data directory. (Bug #12190)

IGNORE_SERVER_IDS takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.

In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its source instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use.

When a CHANGE MASTER TO statement is issued without any IGNORE_SERVER_IDS option, any existing list is preserved. To clear the list of ignored servers, it is necessary to use the option with an empty list:

CHANGE MASTER TO IGNORE_SERVER_IDS = ();

RESET SLAVE ALL has no effect on the server ID list. This issue is fixed in MySQL 5.7. (Bug #18816897)

If IGNORE_SERVER_IDS contains the server's own ID and the server was started with the --replicate-same-server-id option enabled, an error results.

In MySQL 5.6, the connection metadata repository and the output of SHOW SLAVE STATUS provide the list of servers that are currently ignored. For more information, see Section 17.2.2.2, “Replication Metadata Repositories”, and Section 13.7.5.35, “SHOW SLAVE STATUS Statement”.

In MySQL 5.6, invoking CHANGE MASTER TO causes the previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and MASTER_LOG_POS to be written to the error log, along with other information about the replica's state prior to execution.

In MySQL 5.6.7 and later, CHANGE MASTER TO causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

CHANGE MASTER TO is useful for setting up a replica when you have the snapshot of the source and have recorded the source 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. This is used when you want to set up the replica to replicate the source:

CHANGE MASTER TO
  MASTER_HOST='source2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='source2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

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 SLAVE SQL_THREAD):

CHANGE MASTER TO
  RELAY_LOG_FILE='replica-relay-bin.006',
  RELAY_LOG_POS=4025;

The following table shows the maximum permissible length for the string-valued options.