CHANGE MASTER TO

Complete CHANGE MASTER TO reference: MASTER_HOST/PORT/USER/PASSWORD syntax, MASTER_LOG_FILE/POS options, MASTER_USE_GTID configuration, and MASTER_SSL settings.

The terms master and slave have historically been used in replication, and MariaDB has begun the process of adding primary and replica synonyms. The old terms will continue to be used to maintain backward compatibility - see MDEV-18777 to follow progress on this effort.

Description

The CHANGE MASTER TO statement sets up a replication replica server to connect to a specific primary server and defines the replication coordinates (binary log file and position or GTID) and connection parameters (host, user, password, port, SSL options, etc.). When executed, the replica updates its internal replication metadata accordingly.

Using Defaults for Replication Parameters

Starting with MariaDB 12.3, replication connection parameters can be configured once at the server level and applied to replication channels by referencing them with the DEFAULT option in the CHANGE MASTER statement. This enables you to set up parameters like SSL certificates or retry intervals centrally in the server configuration (for example, in the my.cnf file) and reuse them across multiple replication channels.

Default System Variables

When DEFAULT is used in a CHANGE MASTER, the value is extracted from the corresponding server-level configuration variable. Selecting DEFAULT makes the replication channel inherit the value from the matching system variable.

CHANGE MASTER Option

System Variable

Description

MASTER_CONNECT_RETRY

replication_connect_retry

The interval to wait between connection retry attempts.

MASTER_RETRY_COUNT

replication_retry_count

Number of connection attempts before stopping.

MASTER_HEARTBEAT_PERIOD

replication_heartbeat_period

The interval for replication heartbeats. If not configured, it defaults to half the value of slave_net_timeout

MASTER_SSL

replication_ssl

Enable/disable SSL for the channel

MASTER_SSL_CA

replication_ssl_ca

Path to the Certificate Authority (CA) file.

MASTER_SSL_CERT

replication_ssl_cert

Path to the client public certificate.

MASTER_SSL_KEY

replication_ssl_key

Path to the client private key.

MASTER_SSL_CAPATH

replication_ssl_capath

Path to the directory containing CA certificates.

MASTER_SSL_VERIFY_SERVER_CERT

replication_ssl_verify_server_cert

Enable verification of the primary's certificate.

MASTER_SSL_CRL

replication_ssl_crl

Path to the Certificate Revocation List (CRL) file.

MASTER_SSL_CRLPATH

replication_ssl_crlpath

Path to the directory containing CRL files.

MASTER_SSL_CIPHER

replication_ssl_cipher

List of permitted TLS ciphers.

MASTER_USE_GTID

replication_use_gtid

Setting for Global Transaction ID (GTID) mode.

Syntax

CHANGE MASTER ['connection_name'] TO master_def  [, master_def] ... 
  [FOR CHANNEL 'channel_name']

master_def:
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = {interval | DEFAULT}
  | MASTER_HEARTBEAT_PERIOD = {interval | DEFAULT}
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_DELAY = interval
  | MASTER_SSL = {0 | 1 | DEFAULT}
  | MASTER_SSL_CA = {'ca_file_name' | DEFAULT}
  | MASTER_SSL_CAPATH = {'ca_directory_name' | DEFAULT}
  | MASTER_SSL_CERT = {'cert_file_name' | DEFAULT}
  | MASTER_SSL_CRL = {'crl_file_name' | DEFAULT}
  | MASTER_SSL_CRLPATH = {'crl_directory_name' | DEFAULT}
  | MASTER_SSL_KEY = {'key_file_name' | DEFAULT}
  | MASTER_SSL_CIPHER = {'cipher_list' | DEFAULT}
  | MASTER_SSL_VERIFY_SERVER_CERT = {0 | 1 | DEFAULT}
  | MASTER_USE_GTID = {current_pos | slave_pos | no | DEFAULT}
  | MASTER_DEMOTE_TO_SLAVE = bool
  | IGNORE_SERVER_IDS = (server_id_list)
  | DO_DOMAIN_IDS = ([N,..])
  | IGNORE_DOMAIN_IDS = ([N,..])
  | MASTER_RETRY_COUNT = {long | DEFAULT}

Note: The value is extracted from the corresponding server option or system variable that support DEFAULT. This allows you to reset a replication configuration parameter to its server-level configuration without providing an explicit value.

Description

CHANGE MASTER is used on a replica to set up or change replication settings for connecting to the primary.

The FOR CHANNEL keyword was added for MySQL compatibility. This is identical to using the channel_name directly after CHANGE MASTER.

FOR CHANNEL is not available.

Multi-Source Replication

If you are using multi-source replication, then you need to specify a connection name when you execute CHANGE MASTER. There are two ways to do this:

Setting the default_master_connection system variable prior to executing CHANGE MASTER.
Setting the connection_name parameter when executing CHANGE MASTER.

default_master_connection

SET default_master_connection = 'gandalf';
STOP SLAVE;
CHANGE MASTER TO 
   MASTER_PASSWORD='new3cret';
START SLAVE;

connection_name

STOP SLAVE 'gandalf';
CHANGE MASTER 'gandalf' TO 
   MASTER_PASSWORD='new3cret';
START SLAVE 'gandalf';

Options

Using Configurable Defaults

In MariaDB 12.3, when managing multiple replication channels, you can define shared settings in your my.cnf file. For example:

# Global defaults for all replication channels
replication_ssl_ca   = /etc/mysql/ssl/ca.pem
replication_ssl_cert = /etc/mysql/ssl/client-cert.pem
replication_ssl_key  = /etc/mysql/ssl/client-key.pem
replication_use_gtid = slave_pos

By specifying the DEFAULT keyword, the specific replication channel inherits these global values automatically:

-- Setting up a specific channel using global defaults
CHANGE MASTER 'primary_node_1' TO
  MASTER_HOST = '10.0.0.5',
  MASTER_SSL_CA = DEFAULT,
  MASTER_SSL_CERT = DEFAULT,
  MASTER_SSL_KEY = DEFAULT,
  MASTER_USE_GTID = DEFAULT;

START REPLICA 'primary_node_1';

Connection Options

MASTER_USER

The MASTER_USER option for CHANGE MASTER defines the user account that the replica will use to connect to the primary.

This user account will need the REPLICATION REPLICA privilege on the primary.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_USER='repl',
   MASTER_PASSWORD='new3cret';
START SLAVE;

The maximum length of the MASTER_USER string is 128 characters.

The maximum length of the MASTER_USER string is 96 characters.

MASTER_PASSWORD

The MASTER_PASSWORD option for CHANGE MASTER defines the password that the replica will use to connect to the primary as the user account defined by the MASTER_USER option.

For example:

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_PASSWORD='new3cret';
START SLAVE;

The maximum length of the MASTER_PASSWORD string is 32 characters. The effective maximum length of the string depends on how many bytes are used per character and can be up to 96 characters.

Due to MDEV-29994, the password can be silently truncated to 41 characters when MariaDB is restarted. For this reason, it is recommended to use a password that is shorter than this.

MASTER_HOST

The MASTER_HOST option for CHANGE MASTER defines the hostname or IP address of the primary.

If you set the value of the MASTER_HOST option to the empty string, then that is not the same as not setting the option's value at all. If you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command will fail with an error. In MariaDB 5.3 and before, if you set the value of the MASTER_HOST option to the empty string, then the CHANGE MASTER command would succeed, but the subsequent START SLAVE command would fail.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_HOST='dbserver1.example.com',
   MASTER_USER='repl',
   MASTER_PASSWORD='new3cret',
   MASTER_USE_GTID=slave_pos;
START SLAVE;

If you set the value of the MASTER_HOST option in a CHANGE MASTER command, then the replica assumes that the primary is different from before, even if you set the value of this option to the same value it had previously. In this scenario, the replica will consider the old values for the primary's binarylog file name and position to be invalid for the new primary. As a side effect, if you do not explicitly set the values of the MASTER_LOG_FILE and MASTER_LOG_POS options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable GTID mode for replication by setting the MASTER_USE_GTID option to some value other than no in the statement, then these values will effectively be ignored anyway.

Replicas cannot connect to primaries using Unix socket files or Windows named pipes. The replica must connect to the primary using TCP/IP.

The maximum length of the MASTER_HOST string is 255 characters.

The maximum length of the MASTER_HOST string is 60 characters.

MASTER_PORT

The MASTER_PORT option for CHANGE MASTER defines the TCP/IP port of the primary.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_HOST='dbserver1.example.com',
   MASTER_PORT=3307,
   MASTER_USER='repl',
   MASTER_PASSWORD='new3cret',
   MASTER_USE_GTID=slave_pos;
START SLAVE;

If you set the value of the MASTER_PORT option in a CHANGE MASTER command, then the replica assumes that the primary is different from before, even if you set the value of this option to the same value it had previously. In this scenario, the replica will consider the old values for the primary's binarylog file name and position to be invalid for the new primary. As a side effect, if you do not explicitly set the values of the MASTER_LOG_FILE and MASTER_LOG_POS options in the statement, then the statement will be implicitly appended with MASTER_LOG_FILE='' and MASTER_LOG_POS=4. However, if you enable GTID mode for replication by setting the MASTER_USE_GTID option to some value other than no in the statement, then these values will effectively be ignored anyway.

Replicas cannot connect to primaries using Unix socket files or Windows named pipes. The replica must connect to the primary using TCP/IP.

MASTER_CONNECT_RETRY

The MASTER_CONNECT_RETRY option for CHANGE MASTER defines how many seconds that the replica will wait between connection retries. The default is 60.

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_CONNECT_RETRY=20;
START SLAVE;

Starting with MariaDB 12.3, this option accepts the DEFAULT keyword. When set to DEFAULT, the value is taken from the replication_connect_retry system variable.

MASTER_RETRY_COUNT

The MASTER_RETRY_COUNT option limits the number of connection attempts (i.e., Connects_Tried in SHOW REPLICA STATUS). For example:

STOP SLAVE;
CHANGE MASTER TO
  MASTER_RETRY_COUNT=1; # attempt only once; do not retry if it fails
START SLAVE;

Setting this option resets the Connects_Tried statistic in SHOW REPLICA STATUS to 0.

The default is the --master-retry-count option, which be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

The MASTER_RETRY_COUNT option for CHANGE MASTER is only supported by MariaDB 12.0.1 and later and by MySQL. Please use the --master-retry-count option instead, which be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
master_retry_count=4294967295

Starting with MariaDB 12.3, this option accepts the DEFAULT keyword. When set to DEFAULT, the value is taken from the replication_retry_connect system variable.

MASTER_BIND

The MASTER_BIND option for CHANGE MASTER is only supported by MySQL 5.6.2 and later and by MySQL NDB Cluster 7.3.1 and later. This option is not supported by MariaDB. See MDEV-19248 for more information.

The MASTER_BIND option for CHANGE MASTER can be used on replicas that have multiple network interfaces to choose which network interface the replica will use to connect to the primary.

MASTER_HEARTBEAT_PERIOD

The MASTER_HEARTBEAT_PERIOD option for CHANGE MASTER can be used to set the interval in seconds between replication heartbeats. Whenever the primary's binary log is updated with an event, the waiting period for the next heartbeat is reset.

This option's interval argument has the following characteristics:

It is a decimal value with a range of 0 to 4294967 seconds.
It has a resolution of hundredths of a second.
Its smallest valid non-zero value is 0.001.
Its default value is the value of the slave_net_timeout system variable divided by 2.
If it's set to 0, then heartbeats are disabled.

Heartbeats are sent by the primary only if there are no unsent events in the binary log file for a period longer than the interval.

Starting with MariaDB 12.3, this option accepts the DEFAULT keyword. When set to DEFAULT, the value is taken from the replication_heartbeat_period system variable.

If the RESET SLAVE statement is executed, then the heartbeat interval is reset to the default.

If the slave_net_timeout system variable is set to a value that is lower than the current heartbeat interval, then a warning will be issued.

TLS Options

The TLS options are used for providing information about TLS. The options can be set even on replicas that are compiled without TLS support. The TLS options are saved to either the default master.info file or the file that is configured by the master_info_file option, but these TLS options are ignored unless the replica supports TLS.

See Replication with Secure Connections for more information.

Starting with MariaDB 12.3, the DEFAULT keyword is accepted by the TLS options, enabling replication channels to share a central configuration specified in the server's option file. When DEFAULT is used, values are derived from corresponding system variables (e.g., MASTER_SSL_CA derives from replication_ssl_ca). See Default System Variables for a full list of supported variables.

MASTER_SSL

The MASTER_SSL option for CHANGE MASTER tells the replica whether to force TLS for the connection. The valid values are 0, 1 , or DEFAULT (available since 12.3). Required to be set to 1 for the other MASTER_SSL* options to have any effect.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL=1;
START SLAVE;

Starting with MariaDB 12.3, this option accept DEFAULT keyword. If DEFAULT is used, the value is derived from replication_ssl.

MASTER_SSL_CA

The MASTER_SSL_CA option for CHANGE MASTER defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS. This option requires that you use the absolute path, not a relative path.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

See Secure Connections Overview: Certificate Authorities (CAs) for more information. The maximum length of MASTER_SSL_CA string is 511 characters.

This option accepts a path or DEFAULT (available since 12.3). If DEFAULT is used, the value is inherited from replication_ssl_ca.

MASTER_SSL_CAPATH

The MASTER_SSL_CAPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for TLS. This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the openssl rehash command.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CAPATH='/etc/my.cnf.d/certificates/ca/',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

See Secure Connections Overview: Certificate Authorities (CAs) for more information. The maximum length of MASTER_SSL_CA_PATH string is 511 characters.

This option accepts a path or DEFAULT (available since 12.3). If DEFAULT is used, the value is inherited from replication_ssl_capath.

MASTER_SSL_CERT

The MASTER_SSL_CERT option for CHANGE MASTER defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

The maximum length of MASTER_SSL_CERT string is 511 characters.

This option accepts a path or DEFAULT (available since 12.3). If DEFAULT is used, the value is inherited from replication_ssl_cert.

MASTER_SSL_CRL

The MASTER_SSL_CRL option for CHANGE MASTER defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This option requires that you use the absolute path, not a relative path.

This option is only supported if the server was built with OpenSSL. If the server was built with yaSSL, then this option is not supported. See TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1,
   MASTER_SSL_CRL='/etc/my.cnf.d/certificates/crl.pem';
START SLAVE;

See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information. The maximum length of MASTER_SSL_CRL string is 511 characters.

Starting with MariaDB 12.3, this option accepts the DEFAULT keyword. When set to DEFAULT, the value is derived from the replication_ssl_crl system variable.

MASTER_SSL_CRLPATH

The MASTER_SSL_CRLPATH option for CHANGE MASTER defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for TLS. This option requires that you use the absolute path, not a relative path. The directory specified by this variable needs to be run through the openssl rehash command.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1,
   MASTER_SSL_CRLPATH='/etc/my.cnf.d/certificates/crl/';
START SLAVE;

See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information. The maximum length of MASTER_SSL_CRL_PATH string is 511 characters.

This option accepts the DEFAULT keyword (available since 12.3). When set to DEFAULT, the value is inherited from the replication_ssl_crlpath system variable.

MASTER_SSL_KEY

The MASTER_SSL_KEY option for CHANGE MASTER defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

The maximum length of MASTER_SSL_KEY string is 511 characters.

This option supports a path or DEFAULT keyword (available since 12.3). If DEFAULT is used, the value is inherited from replication_ssl_key.

MASTER_SSL_CIPHER

The MASTER_SSL_CIPHER option for CHANGE MASTER defines the list of permitted ciphers or cipher suites to use for TLS. Besides cipher names, if MariaDB was compiled with OpenSSL, this option could be set to "SSLv3" or "TLSv1.2" to allow all SSLv3 or all TLSv1.2 ciphers. Note that the TLSv1.3 ciphers cannot be excluded when using OpenSSL, even by using this option. See Using TLSv1.3 for details.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1,
   MASTER_SSL_CIPHER='TLSv1.2';
START SLAVE;

The maximum length of MASTER_SSL_CIPHER string is 511 characters.

Starting with MariaDB 12.3, this option supports the DEFAULT keyword. When set to DEFAULT, the value is derived from the replication_ssl_cipher system variable.

MASTER_SSL_VERIFY_SERVER_CERT

The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables server certificate verification. This option is enabled by default.

The MASTER_SSL_VERIFY_SERVER_CERT option for CHANGE MASTER enables server certificate verification. This option is disabled by default.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_SSL_CERT='/etc/my.cnf.d/certificates/server-cert.pem',
   MASTER_SSL_KEY='/etc/my.cnf.d/certificates/server-key.pem',
   MASTER_SSL_CA='/etc/my.cnf.d/certificates/ca.pem',
   MASTER_SSL_VERIFY_SERVER_CERT=1;
START SLAVE;

See Secure Connections Overview: Server Certificate Verification for more information.

Starting with MariaDB 12.3, this option accepts the DEFAULT keyword. When set to DEFAULT, the value is inherited from the replication_ssl_verify_server_cert system variable.

Binary Log Options

These options are related to the binary log position on the primary.

MASTER_LOG_FILE

The MASTER_LOG_FILE option for CHANGE MASTER can be used along with MASTER_LOG_POS to specify the coordinates at which the replica's I/O thread should begin reading from the primary's binary logs the next time the thread starts.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4;
START SLAVE;

The MASTER_LOG_FILE and MASTER_LOG_POS options cannot be specified if the RELAY_LOG_FILE and RELAY_LOG_POS options were also specified.

The MASTER_LOG_FILE and MASTER_LOG_POS options are effectively ignored if you enable GTID mode for replication by setting the MASTER_USE_GTID option to some value other than no in the statement.

MASTER_LOG_POS

The MASTER_LOG_POS option for CHANGE MASTER can be used along with MASTER_LOG_FILE to specify the coordinates at which the replica's I/O thread should begin reading from the primary's binary logs the next time the thread starts.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4;
START SLAVE;

The MASTER_LOG_FILE and MASTER_LOG_POS options cannot be specified if the RELAY_LOG_FILE and RELAY_LOG_POS options were also specified.

The MASTER_LOG_FILE and MASTER_LOG_POS options are effectively ignored if you enable GTID mode for replication by setting the MASTER_USE_GTID option to some value other than no in the statement.

Relay Log Options

These options are related to the relay log position on the replica.

RELAY_LOG_FILE

The RELAY_LOG_FILE option for CHANGE MASTER can be used along with the RELAY_LOG_POS option to specify the coordinates at which the replica's SQL thread should begin reading from the relay log the next time the thread starts.

The CHANGE MASTER statement usually deletes all relay log files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing relay log files are kept.

When you want to change the relay log position, you only need to stop the replica's SQL thread. The replica's I/O thread can continue running. The STOP SLAVE and START SLAVE statements support the SQL_THREAD option for this scenario. For example:

STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
   RELAY_LOG_FILE='slave-relay-bin.006',
   RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;

When the value of this option is changed, the metadata about the replica's SQL thread's position in the relay logs will also be changed in the relay-log.info file or the file that is configured by the relay_log_info_file system variable.

The RELAY_LOG_FILE and RELAY_LOG_POS options cannot be specified if the MASTER_LOG_FILE and MASTER_LOG_POS options were also specified.

RELAY_LOG_POS

The RELAY_LOG_POS option for CHANGE MASTER can be used along with the RELAY_LOG_FILE option to specify the coordinates at which the replica's SQL thread should begin reading from the relay log the next time the thread starts.

The CHANGE MASTER statement usually deletes all relay log files. However, if the RELAY_LOG_FILE and/or RELAY_LOG_POS options are specified, then existing relay log files are kept.

STOP SLAVE SQL_THREAD;
CHANGE MASTER TO
   RELAY_LOG_FILE='slave-relay-bin.006',
   RELAY_LOG_POS=4025;
START SLAVE SQL_THREAD;

The RELAY_LOG_FILE and RELAY_LOG_POS options cannot be specified if the MASTER_LOG_FILE and MASTER_LOG_POS options were also specified.

GTID Options

MASTER_USE_GTID

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:

current_pos - Replicate in GTID mode and use gtid_current_pos as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, MASTER_DEMOTE_TO_SLAVE= option instead.
replica_pos - Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary.
no - Don't replicate in GTID mode.

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:

current_pos - Replicate in GTID mode and use gtid_current_pos as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, MASTER_DEMOTE_TO_SLAVE= option instead.
slave_pos - Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary.
no - Don't replicate in GTID mode.

The MASTER_USE_GTID option for CHANGE MASTER can be used to configure the replica to use the global transaction ID (GTID) when connecting to a primary. The possible values are:

current_pos - Replicate in GTID mode and use gtid_current_pos as the position to start downloading transactions from the primary. Using to transition to primary can break the replication state if the replica executes local transactions due to actively updating gtid_current_pos with gtid_binlog_pos and gtid_slave_pos. Use the new, safe, MASTER_DEMOTE_TO_SLAVE= option instead.
slave_pos - Replicate in GTID mode and use gtid_slave_pos as the position to start downloading transactions from the primary. From MariaDB 10.5.1, replica_pos is an alias for slave_pos.
no - Don't replicate in GTID mode.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_USE_GTID = current_pos;
START SLAVE;

Or:

STOP SLAVE;
SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
   MASTER_USE_GTID = slave_pos;
START SLAVE;

Starting with MariaDB 12.3, this option supports the DEFAULT keyword. If set to DEFAULT, the value is inherited from the replication_use_gtid system variable.

MASTER_DEMOTE_TO_SLAVE

Used to transition a primary to become a replica. Replaces the old MASTER_USE_GTID=current_pos with a safe alternative by forcing users to set Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos once at CHANGE MASTER TO time. If gtid_slave_pos is morerecent than gtid_binlog_pos (as in the case of chain replication), the replication state should be preserved.

For example:

STOP SLAVE;
CHANGE MASTER TO
   MASTER_DEMOTE_TO_SLAVE = 1;
START SLAVE;

MASTER_DEMOTE_TO_SLAVE is not available.

Also see Replication filters.

IGNORE_SERVER_IDS

The IGNORE_SERVER_IDS option for CHANGE MASTER can be used to configure a replica to ignore binary log events that originated from certain servers. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of server_id values. For example:

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_SERVER_IDS = (3,5);
START SLAVE;

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_SERVER_IDS = ();
START SLAVE;

DO_DOMAIN_IDS

The DO_DOMAIN_IDS option for CHANGE MASTER can be used to configure a replica to only apply binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of gtid_domain_id values. Duplicate values are automatically ignored. For example:

STOP SLAVE;
CHANGE MASTER TO 
   DO_DOMAIN_IDS = (1,2);
START SLAVE;

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

STOP SLAVE;
CHANGE MASTER TO 
   DO_DOMAIN_IDS = ();
START SLAVE;

The DO_DOMAIN_IDS option and the IGNORE_DOMAIN_IDS option cannot both be set to non-empty values at the same time. If you want to set the DO_DOMAIN_IDS option, and the IGNORE_DOMAIN_IDS option was previously set, then you need to clear the value of the IGNORE_DOMAIN_IDS option. For example:

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_DOMAIN_IDS = (), 
   DO_DOMAIN_IDS = (1,2);
START SLAVE;

The DO_DOMAIN_IDS option can only be specified if the replica is replicating in GTID mode. Therefore, the MASTER_USE_GTID option must also be set to some value other than no in order to use this option.

IGNORE_DOMAIN_IDS

The IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used to configure a replica to ignore binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the replica’s relay log, and they will not be applied by the replica.

The option's value can be specified by providing a comma-separated list of gtid_domain_id values. Duplicate values are automatically ignored. For example:

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;

If you would like to clear a previously set list, then you can set the value to an empty list. For example:

STOP SLAVE;
CHANGE MASTER TO 
   IGNORE_DOMAIN_IDS = ();
START SLAVE;

The DO_DOMAIN_IDS option and the IGNORE_DOMAIN_IDS option cannot both be set to non-empty values at the same time. If you want to set the IGNORE_DOMAIN_IDS option, and the DO_DOMAIN_IDS option was previously set, then you need to clear the value of the DO_DOMAIN_IDS option. For example:

STOP SLAVE;
CHANGE MASTER TO 
   DO_DOMAIN_IDS = (), 
   IGNORE_DOMAIN_IDS = (1,2);
START SLAVE;

The IGNORE_DOMAIN_IDS option can only be specified if the replica is replicating in GTID mode. Therefore, the MASTER_USE_GTID option must also be set to some value other than no in order to use this option.

Delayed Replication Options

MASTER_DELAY

The MASTER_DELAY option for CHANGE MASTER can be used to enable delayed replication. This option specifies the time in seconds (at least) that a replica should lag behind the primary up to a maximum value of 2147483647, or about 68 years. Before executing an event, the replica will first wait, if necessary, until the given time has passed since the event was created on the primary. The result is that the replica will reflect the state of the primary some time back in the past. The default is zero, no delay.

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_DELAY=3600;
START SLAVE;

Changing Option Values

If you don't specify a given option when executing the CHANGE MASTER statement, then the option keeps its old value in most cases. Most of the time, there is no need to specify the options that do not need to change. For example, if the password for the user account that the replica uses to connect to its primary has changed, but no other options need to change, then you can just change the MASTER_PASSWORD option by executing the following commands:

STOP SLAVE;
CHANGE MASTER TO 
   MASTER_PASSWORD='new3cret';
START SLAVE;

There are some cases where options are implicitly reset, such as when the MASTER_HOST and MASTER_PORT options are changed.

Option Persistence

The values of the MASTER_LOG_FILE and MASTER_LOG_POS options (i.e. the binary log position on the primary) and most other options are written to either the default master.info file or the file that is configured by the master_info_file option. The replica's I/O thread keeps this binary log position updated as it downloads events only when MASTER_USE_GTID optionis set to NO. Otherwise the file is not updated on a per event basis.

The master_info_file option can be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
master_info_file=/mariadb/myserver1-master.info

The values of the RELAY_LOG_FILE and RELAY_LOG_POS options (i.e. the relay log position) are written to either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable. The replica's SQL thread keeps this relay log position updated as it applies events.

The relay_log_info_file system variable can be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
relay_log_info_file=/mariadb/myserver1-relay-log.info

GTID Persistence

If the replica is replicating binary log events that contain GTIDs, then the replica's SQL thread will write every GTID that it applies to the mysql.gtid_slave_pos table. This GTID can be inspected and modified through the gtid_slave_pos system variable.

If the replica has the log_slave_updates system variable enabled and if the replica has the binary log enabled, then every write by the replica's SQL thread will also go into the replica's binary log. This means that GTIDs of replicated transactions would be reflected in the value of the gtid_binlog_pos system variable.

Creating a Replica from a Backup

The CHANGE MASTER statement is useful for setting up a replica when you have a backup of the primary and you also have the binary log position or GTID position corresponding to the backup.

After restoring the backup on the replica, you could execute something like this to use the binary log position:

CHANGE MASTER TO
   MASTER_LOG_FILE='master2-bin.001',
   MASTER_LOG_POS=4;
START SLAVE;

Or you could execute something like this to use the GTID position:

SET GLOBAL gtid_slave_pos='0-1-153';
CHANGE MASTER TO
   MASTER_USE_GTID=slave_pos;
START SLAVE;

See Setting up a Replication Slave with mariadb-backup for more information on how to do this with mariadb-backup.

Example

The following example changes the primary and primary's binary log coordinates. This is used when you want to set up the replica to replicate the primary:

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

Description

Using Defaults for Replication Parameters

Default System Variables

Syntax

Description

Multi-Source Replication

default_master_connection

connection_name

Options

Using Configurable Defaults

Connection Options

MASTER_USER

MASTER_PASSWORD

MASTER_HOST

MASTER_PORT

MASTER_CONNECT_RETRY

MASTER_RETRY_COUNT

MASTER_BIND

MASTER_HEARTBEAT_PERIOD

TLS Options

MASTER_SSL

MASTER_SSL_CA

MASTER_SSL_CAPATH

MASTER_SSL_CERT

MASTER_SSL_CRL

MASTER_SSL_CRLPATH

MASTER_SSL_KEY

MASTER_SSL_CIPHER

MASTER_SSL_VERIFY_SERVER_CERT

Binary Log Options

MASTER_LOG_FILE

MASTER_LOG_POS

Relay Log Options

RELAY_LOG_FILE

RELAY_LOG_POS

GTID Options

MASTER_USE_GTID

MASTER_DEMOTE_TO_SLAVE

Replication Filter Options

IGNORE_SERVER_IDS

DO_DOMAIN_IDS

IGNORE_DOMAIN_IDS

Delayed Replication Options

MASTER_DELAY

Changing Option Values

Option Persistence

GTID Persistence

Creating a Replica from a Backup

Example

See Also