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,..])
The CHANGE MASTER
statement sets the options that a replication slave uses to connect to and replicate from a replication master.
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 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:
default_master_connection
system variable prior to executing CHANGE MASTER
. 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';
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:
0
to 4294967
seconds. 0.001
. slave_net_timeout
system variable divided by 2. 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.
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.
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.
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.
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;
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.
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;
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.
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
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.
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.
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;
CHANGE MASTER TO
.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/change-master-to/