The Ultimate Guide to High Availability with MariaDB

CHANGE MASTER TO

You are viewing an old version of this article. View the current version here.

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,..])

Contents

  1. Syntax
  2. Description
    1. connection_name
    2. MASTER_USER, MASTER_PASSWORD
    3. MASTER_HOST, MASTER_PORT
    4. MASTER_CONNECT_RETRY
    5. MASTER_SSL_*
    6. MASTER_BIND
    7. MASTER_HEARTBEAT_PERIOD
    8. MASTER_LOG_FILE, MASTER_LOG_POS
    9. RELAY_LOG_FILE, RELAY_LOG_POS
    10. MASTER_USE_GTID
    11. MASTER_DELAY
    12. IGNORE_SERVER_IDS
    13. DO_DOMAIN_IDS, IGNORE_DOMAIN_IDS
  3. Examples
  4. See Also

Description

CHANGE MASTER ... TO changes the parameters that the slave server uses for connecting to and communicating with the master server during replication. It also updates the contents of the master.info and relay-log.info files.

connection_name

MariaDB starting with 10.0.1

The connection_name option was added as part of multi-source replication added in MariaDB 10.0.1

If there is only one nameless master, or the default master (as specified by the default_master_connection system variable) is intended, connection_name can be omitted. If provided, the CHANGE MASTER ... TO statement will apply to the specified master. connection_name is case-insensitive.

MASTER_USER, MASTER_PASSWORD

MASTER_USER and MASTER_PASSWORD are the username and password respectively that the slave will use for connecting to the master.

MASTER_HOST, MASTER_PORT

MASTER_HOST and MASTER_PORT are the host name (or IP address) of the master host and its TCP/IP port.

Setting MASTER_HOST='' — that is, explicitly setting its value to an empty string — is not the same as not setting it at all. In MariaDB 5.5 and later, setting MASTER_HOST to an empty string will fail with an error. In earlier versions, setting this option to an empty string caused START SLAVE subsequently to fail.

If you specify MASTER_HOST or MASTER_PORT, the slave assumes that the master server is different from before (even if you specify a host or port value that is the same as the current value.) In this case, the old values for the master binary log name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

Note: Replication cannot use Unix socket files. You must be able to connect to the master server using TCP/IP.

MASTER_CONNECT_RETRY

MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries. The default is 60. The number of reconnection attempts is limited by the master-retry-count server option.

MASTER_SSL_*

The TLS options (MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_CRL (from MariaDB 10.0.0), MASTER_SSL_CRLPATH (from MariaDB 10.0.0), MASTER_SSL_KEY, MASTER_SSL_CIPHER), and MASTER_SSL_VERIFY_SERVER_CERT are used for providing information about using TLS for the connection. They can be changed even on slaves that are compiled without TLS support. They are saved to the master.info file, but are ignored unless you use a server that has TLS support enabled.

MASTER_SSL_VERIFY_SERVER_CERT is used for the --ssl-verify-server-cert option, available to clients, which causes the client to check the certificate sent to the client for the server's Common Name, verifying against the host name, and failing if there's a mismatch, preventing man-in-the-middle attacks. --ssl-verify-server-cert is disabled by default.

If you don't specify a given parameter, it keeps its old value, except as indicated in the following discussion. For example, if the password to connect to your master has changed, you just need to issue these statements to tell the slave about the new password:

STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication

There is no need to specify the parameters that do not change (host, port, user, and so forth).

MASTER_BIND

This option is only available in MySQL Cluster; it is not supported in mainline MariaDB and MySQL:

  • MASTER_BIND is for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master. It is also possible to determine which network interface is to be used in such cases by starting the slave mysqld process with the --master-bind option.

MASTER_HEARTBEAT_PERIOD

MASTER_HEARTBEAT_PERIOD is 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. interval is a decimal value with a range of 0 to 4294967 seconds and a resolution to hundredths of a second; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binlog file for a period longer than the interval.

Setting the interval to 0 disables heartbeats altogether. The default value interval is equal to the value of slave_net_timeout divided by 2.

Note: Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued.

Issuing RESET SLAVE resets the heartbeat interval to the default.

MASTER_LOG_FILE, MASTER_LOG_POS

  • MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the slave I/O thread should begin reading from the master the next time the thread starts. If you specify either of them, you cannot specify RELAY_LOG_FILE or
  • RELAY_LOG_POS. If neither of
  • MASTER_LOG_FILE or MASTER_LOG_POS are specified, the slave uses the last coordinates of the slave SQL thread before
  • CHANGE MASTER TO was issued. This ensures that there is no discontinuity in replication, even if the slave SQL thread was late compared to the slave I/O thread, when you merely want to change, say, the password to use.
MariaDB until 10.0.7

For MySQL and MariaDB versions before 10.0.8 the relay_log_purge global variable was set silently to 0.

  • CHANGE MASTER TO is useful for setting up a slave when you have the snapshot of the master and have recorded the log and the offset corresponding to it. After loading the snapshot into the slave, you can run
  • CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master',
  • MASTER_LOG_POS=log_offset_on_master on the slave.

RELAY_LOG_FILE, RELAY_LOG_POS

RELAY_LOG_FILE and RELAY_LOG_POS are the coordinates at which the slave SQL thread should begin reading from the relay log the next time the thread starts. If you specify either of MASTER_LOG_FILE or MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS.

CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay logs are kept;

MASTER_USE_GTID

MariaDB starting with 10.0.2

MariaDB has supported Global Transaction IDs since MariaDB 10.0.2. The MASTER_USE_GTID option configures the slave to use the global transaction ID when connecting to a master. See Global Transaction ID for details.

CHANGE MASTER TO MASTER_USE_GTID = current_pos

MASTER_DELAY

MariaDB starting with 10.2.3

MariaDB 10.2.3 introduced 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.

CHANGE MASTER TO master_delay=3600;

IGNORE_SERVER_IDS

Comma-delimited list of server ids that are to be ignored for replication purposes. To clear a previously submitted list, use an empty string inside the parenthesis, i.e. CHANGE MASTER TO IGNORE_SERVER_IDS = ().

DO_DOMAIN_IDS, IGNORE_DOMAIN_IDS

MariaDB starting with 10.1.2

MariaDB 10.1.2 introduced a mechanism to filter replication events on a slave based on the GTID domain ID by adding two CHANGE MASTER options:

DO_DOMAIN_IDS = ([N,..])
IGNORE_DOMAIN_IDS = ([N,..])
  1. 3DO_DOMAIN_IDS and IGNORE_DOMAIN_IDS both accept a list of comma-separated GTID domain IDs. If DO_DOMAIN_IDS is set, replication events that do not belong to the specified GTID domain ID(s) will get filtered out on slave. On the other hand, if IGNORE_DOMAIN_IDS is set, all the replication events which belong to the specified GTID domain ID(s) will get filtered out and thus not get applied on slave server. All the filtered events would also not get logged into the slave’s relay log. Some examples: 
CHANGE MASTER TO DO_DOMAIN_IDS=(1,2);
CHANGE MASTER TO IGNORE_DOMAIN_IDS=(1,2);

It is important to note that both lists cannot be non-empty at any given instant. For instance, a CHANGE MASTER command trying to set both the lists would fail with ER_MASTER_INFO error. Similarly, if one of the lists is non-empty and CHANGE MASTER is executed to set the other list, the command would fail. In this situation the non-empty list needs to be cleared in order to set the other list.

For example, say IGNORE_DOMAIN_IDS is currently set to (1,2), then in order to set DO_DOMAIN_IDS, IGNORE_DOMAIN_IDS must be cleared like so:

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

CHANGE MASTER also removes any duplicate domain IDs automatically from the list.

Examples

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;

The next example shows an operation that is less frequently employed. It is used when the slave has relay logs that you want it to execute again for some reason. To do this, the master need not be reachable. You need only use CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD):

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

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.