MySQL 9.3 Reference Manual Including MySQL NDB Cluster 9.3

15.4.2.2 CHANGE REPLICATION SOURCE TO Statement

CHANGE REPLICATION SOURCE TO option [, option] ... [ channel_option ]

option: {
    SOURCE_BIND = 'interface_name'
  | SOURCE_HOST = 'host_name'
  | SOURCE_USER = 'user_name'
  | SOURCE_PASSWORD = 'password'
  | SOURCE_PORT = port_num
  | PRIVILEGE_CHECKS_USER = {NULL | 'account'}
  | REQUIRE_ROW_FORMAT = {0|1}
  | REQUIRE_TABLE_PRIMARY_KEY_CHECK = {STREAM | ON | OFF | GENERATE}
  | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = {OFF | LOCAL | uuid}
  | SOURCE_LOG_FILE = 'source_log_name'
  | SOURCE_LOG_POS = source_log_pos
  | SOURCE_AUTO_POSITION = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | SOURCE_HEARTBEAT_PERIOD = interval
  | SOURCE_CONNECT_RETRY = interval
  | SOURCE_RETRY_COUNT = count
  | SOURCE_CONNECTION_AUTO_FAILOVER = {0|1}
  | SOURCE_DELAY = interval
  | SOURCE_COMPRESSION_ALGORITHMS = 'algorithm[,algorithm][,algorithm]'
  | SOURCE_ZSTD_COMPRESSION_LEVEL = level
  | SOURCE_SSL = {0|1}
  | SOURCE_SSL_CA = 'ca_file_name'
  | SOURCE_SSL_CAPATH = 'ca_directory_name'
  | SOURCE_SSL_CERT = 'cert_file_name'
  | SOURCE_SSL_CRL = 'crl_file_name'
  | SOURCE_SSL_CRLPATH = 'crl_directory_name'
  | SOURCE_SSL_KEY = 'key_file_name'
  | SOURCE_SSL_CIPHER = 'cipher_list'
  | SOURCE_SSL_VERIFY_SERVER_CERT = {0|1}
  | SOURCE_TLS_VERSION = 'protocol_list'
  | SOURCE_TLS_CIPHERSUITES = 'ciphersuite_list'
  | SOURCE_PUBLIC_KEY_PATH = 'key_file_name'
  | GET_SOURCE_PUBLIC_KEY = {0|1}
  | NETWORK_NAMESPACE = 'namespace'
  | IGNORE_SERVER_IDS = (server_id_list),
  | GTID_ONLY = {0|1}
}

channel_option:
    FOR CHANNEL channel

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

CHANGE REPLICATION SOURCE TO changes the parameters that the replica server uses for connecting to the source and reading data from the source. It also updates the contents of the replication metadata repositories (see Section 19.2.4, “Relay Log and Replication Metadata Repositories”).

CHANGE REPLICATION SOURCE TO requires the REPLICATION_SLAVE_ADMIN privilege (or the deprecated SUPER privilege).

Options that you do not specify on a CHANGE REPLICATION SOURCE TO statement retain their value, except as indicated in the following discussion. In most cases, there is therefore no need to specify options that do not change.

Values used for SOURCE_HOST and other CHANGE REPLICATION SOURCE TO options are checked for linefeed (\n or 0x0A) characters. The presence of such characters in these values causes the statement to fail with an error.

The optional FOR CHANNEL channel clause lets you name which replication channel the statement applies to. Providing a FOR CHANNEL channel clause applies the CHANGE REPLICATION SOURCE 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 REPLICATION SOURCE TO SOURCE_HOST=host1, SOURCE_PORT=3002 FOR CHANNEL 'channel2';

If no clause is named and no extra channels exist, a CHANGE REPLICATION SOURCE TO statement applies to the default channel, whose name is the empty string (""). When you have set up multiple replication channels, every CHANGE REPLICATION SOURCE TO statement must name a channel using the FOR CHANNEL channel clause. See Section 19.2.2, “Replication Channels” for more information.

For some of the options of the CHANGE REPLICATION SOURCE TO statement, you must issue a STOP REPLICA statement prior to issuing a CHANGE REPLICATION SOURCE TO statement (and a START REPLICA statement afterwards). Sometimes, you only need to stop the replication SQL (applier) thread or the replication I/O (receiver) thread, not both:

You can check the current state of the replication applier thread and replication receiver thread using SHOW REPLICA STATUS. Note that the Group Replication applier channel (group_replication_applier) has no receiver thread, only an applier thread.

CHANGE REPLICATION SOURCE TO statements have a number of side-effects and interactions that you should be aware of beforehand:

The following options are available for CHANGE REPLICATION SOURCE TO statements:

Examples

CHANGE REPLICATION SOURCE 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 REPLICATION SOURCE TO SOURCE_LOG_FILE='log_name', SOURCE_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: 

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source2.example.com',
  SOURCE_USER='replication',
  SOURCE_PASSWORD='password',
  SOURCE_PORT=3306,
  SOURCE_LOG_FILE='source2-bin.001',
  SOURCE_LOG_POS=4,
  SOURCE_CONNECT_RETRY=10;

For the procedure to switch an existing replica to a new source during failover, see Section 19.4.8, “Switching Sources During Failover”.

When GTIDs are in use on the source and the replica, specify GTID auto-positioning instead of giving the binary log file position, as in the following example. For full instructions to configure and start GTID-based replication on new or stopped servers, online servers, or additional replicas, see Section 19.1.3, “Replication with Global Transaction Identifiers”. 

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source3.example.com',
  SOURCE_USER='replication',
  SOURCE_PASSWORD='password',
  SOURCE_PORT=3306,
  SOURCE_AUTO_POSITION = 1,
  FOR CHANNEL "source_3";

In this example, multi-source replication is in use, and the CHANGE REPLICATION SOURCE TO statement is applied to the replication channel "source_3" that connects the replica to the specified host. For guidance on setting up multi-source replication, see Section 19.1.5, “MySQL Multi-Source Replication”.

The next example shows how to make the replica apply transactions from relay log files that you want to repeat. To do this, the source need not be reachable. You can use CHANGE REPLICATION SOURCE TO to locate the relay log position where you want the replica to start reapplying transactions, and then start the SQL thread: 

CHANGE REPLICATION SOURCE TO
  RELAY_LOG_FILE='replica-relay-bin.006',
  RELAY_LOG_POS=4025;
START REPLICA SQL_THREAD;

CHANGE REPLICATION SOURCE TO can also be used to skip over transactions in the binary log that are causing replication to stop. The appropriate method to do this depends on whether GTIDs are in use or not. For instructions to skip transactions using CHANGE REPLICATION SOURCE TO or another method, see Section 19.1.7.3, “Skipping Transactions”.

MySQL Documentation Library