MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

13.4.2.1 CHANGE MASTER TO Statement

CHANGE MASTER TO option [, option] ... [ channel_option ]

option: {
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | PRIVILEGE_CHECKS_USER = {'account' | NULL}
  | REQUIRE_ROW_FORMAT = {0|1}
  | REQUIRE_TABLE_PRIMARY_KEY_CHECK = {STREAM | ON | OFF}
  | 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_COMPRESSION_ALGORITHMS = 'value'
  | MASTER_ZSTD_COMPRESSION_LEVEL = level
  | 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}
  | MASTER_TLS_VERSION = 'protocol_list'
  | MASTER_TLS_CIPHERSUITES = 'ciphersuite_list'
  | MASTER_PUBLIC_KEY_PATH = 'key_file_name'
  | GET_MASTER_PUBLIC_KEY = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)
}

channel_option:
    FOR CHANNEL channel

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

CHANGE MASTER TO changes the parameters that the replica server uses for connecting to the source, 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.4, “Relay Log and Replication Metadata Repositories”). CHANGE MASTER TO requires the REPLICATION_SLAVE_ADMIN privilege (or the deprecated SUPER privilege).

You can issue CHANGE MASTER TO statements on a running replica without first stopping it, depending on the states of the replication SQL thread and replication I/O thread. The rules governing such use are provided later in this section.

When using a multithreaded replica (in other words slave_parallel_workers is greater than 0), stopping the replica can cause gaps in the sequence of transactions that have been executed from the relay log, regardless of whether the replica was stopped intentionally or otherwise. When such gaps exist, issuing CHANGE MASTER TO fails. The solution in this situation is to issue START SLAVE UNTIL SQL_AFTER_MTS_GAPS which ensures that the gaps are closed.

The optional FOR CHANNEL channel clause enables you to name which replication channel the statement applies to. Providing a FOR CHANNEL channel clause applies the CHANGE MASTER TO statement to a specific replication channel, and is used to add a new channel or modify an existing channel. For example, to add a new channel called channel2:

CHANGE MASTER TO MASTER_HOST=host1, MASTER_PORT=3002 FOR CHANNEL 'channel2'

If no clause is named and no extra channels exist, the statement applies to the default channel.

When using multiple replication channels, if a CHANGE MASTER TO statement does not name a channel using a FOR CHANNEL channel clause, an error occurs. See Section 17.2.2, “Replication Channels” for more information.

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.

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

REQUIRE_ROW_FORMAT (available as of MySQL 8.0.19) permits only row-based replication events to be processed by the replication channel. This option prevents the replication applier from taking actions such as creating temporary tables and executing LOAD DATA INFILE requests, which increases the security of the channel. Group Replication channels are automatically created with REQUIRE_ROW_FORMAT set, and you cannot change the option for those channels. For more information, see Section 17.3.3, “Replication Privilege Checks”.

PRIVILEGE_CHECKS_USER (available as of MySQL 8.0.18) names a user account that supplies a security context for the specified channel. NULL, which is the default, means no security context is used. The use of row-based binary logging is strongly recommended when PRIVILEGE_CHECKS_USER is set, and you can set REQUIRE_ROW_FORMAT to enforce this. For example, to start privilege checks on the channel channel_1 on a running replica, issue the following statements:

mysql> STOP SLAVE FOR CHANNEL 'channel_1';
mysql> CHANGE MASTER TO
         PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com',
         REQUIRE_ROW_FORMAT = 1,
         FOR CHANNEL 'channel_1';
mysql> START SLAVE FOR CHANNEL 'channel_1';

The user name and host name for the user account must follow the syntax described in Section 6.2.4, “Specifying Account Names”, and the user must not be an anonymous user (with a blank user name) or the CURRENT_USER. The account must have the REPLICATION_APPLIER privilege, plus the required privileges to execute the transactions replicated on the channel. For details of the privileges required by the account, see Section 17.3.3, “Replication Privilege Checks”. When you restart the replication channel, the privilege checks are applied from that point on. If you do not specify a channel and no other channels exist, the statement is applied to the default channel.

REQUIRE_TABLE_PRIMARY_KEY_CHECK (available as of MySQL 8.0.20) enables a replica to select its own policy for primary key checks. When the option is set to ON for a replication channel, the replica always uses the value ON for the sql_require_primary_key system variable in replication operations, requiring a primary key. When the option is set to OFF, the replica always uses the value OFF for the sql_require_primary_key system variable in replication operations, so that a primary key is never required, even if the source required one. When the REQUIRE_TABLE_PRIMARY_KEY_CHECK option is set to STREAM, which is the default, the replica uses whatever value is replicated from the source for each transaction.

MASTER_COMPRESSION_ALGORITHMS and MASTER_ZSTD_COMPRESSION_LEVEL (available as of MySQL 8.0.18) enable control over the use of compression for connections to the source:

For more information, see Section 4.2.8, “Connection Compression Control”.

Binary log transaction compression (available as of MySQL 8.0.20), which is activated by the binlog_transaction_compression system variable, can also be used to save bandwidth. If you do this in combination with connection compression, connection compression has less opportunity to act on the data, but can still compress headers and those events and transaction payloads that are uncompressed. For more information on binary log transaction compression, see Section 5.4.4.5, “Binary Log Transaction Compression”.

The MASTER_SSL_xxx options and the MASTER_TLS_xxx options specify how the replica uses encryption and ciphers to secure the replication connection. These options can be changed even on replicas that are compiled without SSL support. They are saved to the source metadata repository, but are ignored if the replica does not have SSL support enabled. The MASTER_SSL_xxx and MASTER_TLS_xxx options perform the same functions as the --ssl-xxx and --tls-xxx client options described in Command Options for Encrypted Connections. The correspondence between the two sets of options, and the use of the MASTER_SSL_xxx and MASTER_TLS_xxx options to set up a secure connection, is explained in Section 17.3.1, “Setting Up Replication to Use Encrypted Connections”.

Important

To connect to the source using a replication user account that authenticates with the caching_sha2_password plugin, you must either set up a secure connection as described in Section 17.3.1, “Setting Up Replication to Use Encrypted Connections”, or enable the unencrypted connection to support password exchange using an RSA key pair. The caching_sha2_password authentication plugin is the default for new users created from MySQL 8.0 (for details, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”). If the user account that you create or use for replication (as specified by the MASTER_USER option) uses this authentication plugin, and you are not using a secure connection, you must enable RSA key pair-based password exchange for a successful connection.

To enable RSA key pair-based password exchange, specify either the MASTER_PUBLIC_KEY_PATH or the GET_MASTER_PUBLIC_KEY=1 option. Either of these options provides the RSA public key to the replica:

The MASTER_HEARTBEAT_PERIOD, MASTER_CONNECT_RETRY, and MASTER_RETRY_COUNT options control how the replica recognizes that the connection to the source has been lost and makes attempts to reconnect.

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 231−1. For more information, see Section 17.4.10, “Delayed Replication”.

A CHANGE MASTER TO statement employing the MASTER_DELAY option can be executed on a running replica when the replication SQL thread is stopped.

MASTER_BIND is for use on replicas that have 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. In the source metadata repository table mysql.slave_master_info, the value can be seen as the Master_bind column.

The ability to bind a replica to a specific network interface is also supported by NDB Cluster.

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

RELAY_LOG_FILE can use either an absolute or relative path, and uses the same base name as MASTER_LOG_FILE. A CHANGE MASTER TO statement employing RELAY_LOG_FILE, RELAY_LOG_POS, or both options can be executed on a running replica when the replication SQL thread is stopped. Relay logs are preserved if at least one of the replication SQL thread and the replication I/O thread is running. If both threads are stopped, all relay log files are deleted unless at least one of RELAY_LOG_FILE or RELAY_LOG_POS is specified. Note that the Group Replication applier channel (group_replication_applier) has no I/O thread, only a SQL thread. For this channel, the relay logs are not preserved when the SQL thread is stopped.

When MASTER_AUTO_POSITION = 1 is used with CHANGE MASTER TO, the replica attempts to connect to the source using the GTID-based replication protocol. This option can be used with CHANGE MASTER TO only if both the replication SQL thread and replication I/O thread are stopped. Both the replica and the source must have GTIDs enabled (GTID_MODE=ON, ON_PERMISSIVE, or OFF_PERMISSIVE on the replica, and GTID_MODE=ON on the source). Auto-positioning is used for the connection, so the coordinates represented by MASTER_LOG_FILE and MASTER_LOG_POS are not used, and the use of either or both of these options together with MASTER_AUTO_POSITION = 1 causes an error. If multi-source replication is enabled on the replica, you need to set the MASTER_AUTO_POSITION = 1 option for each applicable replication channel.

With MASTER_AUTO_POSITION = 1 set, in the initial connection handshake, the replica sends a GTID set containing the transactions that it has already received, committed, or both. The source responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the replica. This exchange ensures that the source only sends the transactions with a GTID that the replica has not already recorded or committed. If the replica receives transactions from more than one source, as in the case of a diamond topology, the auto-skip function ensures that the transactions are not applied twice. For details of how the GTID set sent by the replica is computed, see Section 17.1.3.3, “GTID Auto-Positioning”.

If any of the transactions that should be sent by the source have been purged from the source's binary log, or added to the set of GTIDs in the gtid_purged system variable by another method, the source sends the error ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the replica, and replication does not start. The GTIDs of the missing purged transactions are identified and listed in the source's error log in the warning message ER_FOUND_MISSING_GTIDS. Also, if during the exchange of transactions it is found that the replica has recorded or committed transactions with the source's UUID in the GTID, but the source itself has not committed them, the source sends the error ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER to the replica and replication does not start. For information on how to handle these situations, see Section 17.1.3.3, “GTID Auto-Positioning”.

You can see whether replication is running with auto-positioning enabled by checking the Performance Schema replication_connection_status table or the output of SHOW SLAVE STATUS. Disabling the MASTER_AUTO_POSITION option again makes the replica revert to file-based replication, in which case you must also specify one or both of the MASTER_LOG_FILE or MASTER_LOG_POS options.

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.

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.

Note

When global transaction identifiers (GTIDs) are used for replication, transactions that have already been applied are automatically ignored, so the IGNORE_SERVER_IDS function is not required and is deprecated. If gtid_mode=ON is set for the server, a deprecation warning is issued if you include the IGNORE_SERVER_IDS option in a CHANGE MASTER TO statement.

The source 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.4.2, “Replication Metadata Repositories”, and Section 13.7.7.34, “SHOW SLAVE STATUS Statement”.

If 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 clears IGNORE_SERVER_IDS.

Note

A deprecation warning is issued if SET GTID_MODE=ON is issued when any channel has existing server IDs set with IGNORE_SERVER_IDS. Before starting GTID-based replication, check for and clear all ignored server ID lists on the servers involved. The SHOW_SLAVE_STATUS statement displays the list of ignored IDs, if there is one. If you do receive the deprecation warning, you can still clear a list after gtid_mode=ON is set by issuing a CHANGE MASTER TO statement containing the IGNORE_SERVER_IDS option with an empty list.

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.

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

From MySQL 5.7, the strict requirement to execute STOP SLAVE prior to issuing any CHANGE MASTER TO statement (and START SLAVE afterward) is removed. Instead of depending on whether the replica is stopped, the behavior of CHANGE MASTER TO depends on the states of the replication SQL thread and replication I/O thread; which of these threads is stopped or running now determines the options that can or cannot be used with a CHANGE MASTER TO statement at a given point in time. The rules for making this determination are listed here:

You can check the current state of the replication SQL thread and replication I/O thread using SHOW SLAVE STATUS. Note that the Group Replication applier channel (group_replication_applier) has no I/O thread, only a 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 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. 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='password',
  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.

Option Maximum Length
MASTER_HOST 255 (60 prior to MySQL 8.0.17)
MASTER_USER 96
MASTER_PASSWORD 32
MASTER_LOG_FILE 511
RELAY_LOG_FILE 511
MASTER_SSL_CA 511
MASTER_SSL_CAPATH 511
MASTER_SSL_CERT 511
MASTER_SSL_CRL 511
MASTER_SSL_CRLPATH 511
MASTER_SSL_KEY 511
MASTER_SSL_CIPHER 511
MASTER_TLS_VERSION 511
MASTER_TLS_CIPHERSUITES 4000
MASTER_PUBLIC_KEY_PATH 511
MASTER_COMPRESSION_ALGORITHMS 99