Syntax
------ 
CHANGE MASTER ['connection_name'] TO master_def [,
master_def] ...
 
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
 | MASTER_HEARTBEAT_PERIOD = interval
 | 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}
 | 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_USE_GTID = {current_pos|slave_pos|no}
 | IGNORE_SERVER_IDS = (server_id_list)
 | DO_DOMAIN_IDS = ([N,..])
 | IGNORE_DOMAIN_IDS = ([N,..])
 
Description
----------- 
The CHANGE MASTER statement sets the options that a
replication slave uses to connect to and replicate from a
replication master. 
 
MariaDB until 10.0.7
 
In MariaDB 10.0.7 and before, the relay_log_purge system
variable was silently set to 0 when CHANGE MASTER was
executed.
 
Multi-Source Replication
 
Multi-source replication was added in MariaDB 10.0.1.
 
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
 
Connection Options
 
MASTER_USER
 
The MASTER_USER option for CHANGE MASTER defines the user
account that the replication slave will use to connect to
the replication master.
 
This user account will need the REPLICATION SLAVE privilege
on the master.
 
For example:
 
STOP SLAVE;
 
CHANGE MASTER TO
 MASTER_USER='repl',
 MASTER_PASSWORD='new3cret';
 
START SLAVE;
 
MASTER_PASSWORD
 
The MASTER_USER option for CHANGE MASTER defines the
password that the replication slave will use to connect to
the replication master as the user account defined by the
MASTER_USER option.
 
For example:
 
STOP SLAVE;
 
CHANGE MASTER TO 
 MASTER_PASSWORD='new3cret';
 
START SLAVE;
 
MASTER_HOST
 
The MASTER_HOST option for CHANGE MASTER defines the
hostname or IP address of the replication master.
 
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. In MariaDB 5.5 and later, 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 slave assumes that the master is
different from before, even if you set the value of this
option to the same value it had previously. In this
scenario, the slave will consider the old values for the
master's binary
log file name and position to be invalid for the new master.
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.
 
Replication slaves cannot connect to replication masters
using Unix socket files or Windows named pipes. The
replication slave must connect to the replication master
using TCP/IP.
 
MASTER_PORT
 
The MASTER_PORT option for CHANGE MASTER defines the TCP/IP
port of the replication master.
 
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 slave assumes that the master is
different from before, even if you set the value of this
option to the same value it had previously. In this
scenario, the slave will consider the old values for the
master's binary
log file name and position to be invalid for the new master.
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.
 
Replication slaves cannot connect to replication masters
using Unix socket files or Windows named pipes. The
replication slave must connect to the replication master
using TCP/IP.
 
MASTER_CONNECT_RETRY
 
The MASTER_CONNECT_RETRY option for CHANGE MASTER defines
how many seconds that the slave will wait between connection
retries. The default is 60.
 
STOP SLAVE;
 
CHANGE MASTER TO 
 MASTER_CONNECT_RETRY=20;
 
START SLAVE;
 
The number of connection attempts is limited by the
master_retry_count option. It 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_retry_count=4294967295
 
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 yet supported by MariaDB. See
MDEV-19248 for more information.
 
The MASTER_BIND option for CHANGE MASTER can be used on
replication slaves that have multiple network interfaces to
choose which network interface the slave will use to connect
to the master.
 
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 master'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 master only if there are no
unsent events in the binary log file for a period longer
than the interval.
 
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 slaves 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 slave supports TLS.
 
See Replication with Secure Connections for more
information.
 
MASTER_SSL
 
The MASTER_SSL option for CHANGE MASTER tells the slave
whether to force TLS for the connection. The valid values
are 0 or 1.
 
For example:
 
STOP SLAVE;
 
CHANGE MASTER TO
 MASTER_SSL=1;
 
START SLAVE;
 
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. This option implies the MASTER_SSL option.
 
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.
 
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. This option implies the
MASTER_SSL option.
 
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.
 
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. This option implies the MASTER_SSL option.
 
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;
 
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.
 
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.
 
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_CRLPATH='/etc/my.cnf.d/certificates/crl/';
 
START SLAVE;
 
See Secure Connections Overview: Certificate Revocation
Lists (CRLs) for more information.
 
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. This
option implies the MASTER_SSL option.
 
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;
 
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. This
option implies the MASTER_SSL option.
 
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;
 
MASTER_SSL_VERIFY_SERVER_CERT
 
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.
 
Binary Log Options
 
These options are related to the binary log position on the
master.
 
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 slave's I/O thread should begin reading from the
master'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 slave's I/O thread should begin reading from the
master'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
slave.
 
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 slave'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 slave's SQL thread. The slave'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 slave'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 slave'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 slave's SQL thread. The slave'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 slave'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.
 
GTID Options
 
MASTER_USE_GTID
 
The MASTER_USE_GTID option for CHANGE MASTER was first added
in MariaDB 10.0.2 to enable replication with Global
Transaction IDs (GTIDs).
 
The MASTER_USE_GTID option for CHANGE MASTER can be used to
configure the slave to use the global transaction ID (GTID)
when connecting to a master. The possible values are:
current_pos - Replicate in GTID mode and use
gtid_current_pos as the position to start downloading
transactions from the master.
slave_pos - Replicate in GTID mode and use gtid_slave_pos as
the position to start downloading transactions from the
master.
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