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_pos;
 
START SLAVE;
 
Replication Filter Options
 
IGNORE_SERVER_IDS
 
The IGNORE_SERVER_IDS option for CHANGE MASTER can be used
to configure a replication slave to ignore binary log events
that originated from certain servers. Filtered binary log
events will not get logged to the slave’s relay log, and
they will not be applied by the slave.
 
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 was first added
in MariaDB 10.1.2.
 
The DO_DOMAIN_IDS option for CHANGE MASTER can be used to
configure a replication slave 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 slave’s relay log, and they will not be
applied by the slave.
 
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 slave
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 was first
added in MariaDB 10.1.2.
 
The IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used
to configure a replication slave 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
slave’s relay log, and they will not be applied by the
slave.
 
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
slave 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 was first added in
MariaDB 10.2.3 to enable delayed replication.
 
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 replication slave should lag
behind the master. Before executing an event, the slave will
first wait, if necessary, until the given time has passed
since the event was created on the master. The result is
that the slave will reflect the state of the master 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 slave uses to connect
to its master 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 master) 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 slave's I/O thread keeps this binary log
position updated as it downloads events only when
MASTER_USE_GTID option
 is 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 slave'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 slave is replicating binary log events that contain
GTIDs, then the slave'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 slave has the log_slave_updates system variable
enabled and if the slave has the binary log enabled, then
every write by the slave's SQL thread will also go into the
slave'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 Slave from a Backup
 
The CHANGE MASTER statement is useful for setting up a slave
when you have a backup of the master and you also have the
binary log position or GTID position corresponding to the
backup.
 
After restoring the backup on the slave, 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 Mariabackup for more
information on how to do this with Mariabackup.
 
Example
 
The following example changes the master and master's
binary log coordinates.
This is used when you want to set up the slave to replicate
the master:
 
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;
 


URL: https://mariadb.com/kb/en/change-master-to/