_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/