Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 210.0Kb
Man Pages (Zip) - 319.1Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

13.4.2.1 CHANGE MASTER TO Syntax

CHANGE MASTER TO option [, option] ... [ channel_option ]

option:
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_RETRY_COUNT = count
  | MASTER_DELAY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | MASTER_AUTO_POSITION = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | 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_TLS_VERSION = 'protocol_list'
  | IGNORE_SERVER_IDS = (server_id_list)

channel_option:
    FOR CHANNEL channel

server_id_list:
    [server_id [, server_id] ... ]

CHANGE MASTER TO changes the parameters that the slave server uses for connecting to the master server, for reading the master binary log, and reading the slave relay log. It also updates the contents of the master info and relay log info repositories (see Section 16.2.4, “Replication Relay and Status Logs”). CHANGE MASTER TO requires the SUPER privilege.

Prior to MySQL 5.7.4, the slave replication threads must be stopped, using STOP SLAVE if necessary, before issuing this statement. In MySQL 5.7.4 and later, you can issue CHANGE MASTER TO statements on a running slave without doing this, depending on the states of the slave SQL thread and slave I/O thread. The rules governing such use are provided later in this section.

When using a multithreaded slave (in other words slave_parallel_workers is greater than 0), stopping the slave can cause gaps in the sequence of transactions that have been executed from the relay log, regardless of whether the slave was stopped intentionally or otherwise. When such gaps exist, issuing CHANGE MASTER TO fails. The solution in this situation is to issue START SLAVE UNTIL SQL_AFTER_MTS_GAPS which ensures that the gaps are closed.

The optional FOR CHANNEL channel clause enables you to name which replication channel the statement applies to. Providing a FOR CHANNEL channel clause applies the CHANGE MASTER TO statement to a specific replication channel, and is used to add a new channel or modify an existing channel. For example, to add a new channel called channel2:

CHANGE MASTER TO MASTER_HOST=host1, MASTER_PORT=3002 FOR CHANNEL 'channel2'

If no clause is named and no extra channels exist, the statement applies to the default channel.

When using multiple replication channels, if a CHANGE MASTER TO statement does not name a channel using a FOR CHANNEL channel clause, an error occurs. See Section 16.2.3, “Replication Channels” for more information.

Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. For example, if the password to connect to your MySQL master has changed, issue this statement to tell the slave about the new password:

CHANGE MASTER TO MASTER_PASSWORD='new3cret';

MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the slave about how to connect to its master:

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

    Note

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

    If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file 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.

    Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as not setting MASTER_HOST at all. Beginning with MySQL 5.5, trying to set MASTER_HOST to an empty string fails with an error. Previously, setting MASTER_HOST to an empty string caused START SLAVE subsequently to fail. (Bug #28796)

    Values used for MASTER_HOST and other CHANGE MASTER TO options are checked for linefeed (\n or 0x0A) characters; the presence of such characters in these values causes the statement to fail with ER_MASTER_INFO. (Bug #11758581, Bug #50801)

  • MASTER_USER and MASTER_PASSWORD are the user name and password of the account to use for connecting to the master.

    MASTER_USER cannot be made empty; setting MASTER_USER = '' or leaving it unset when setting a value for MASTER_PASSWORD causes an error (Bug #13427949).

    The password used for a MySQL Replication slave account in a CHANGE MASTER TO statement is limited to 32 characters in length; prior to MySQL 5.7.5, if the password was longer, the statement succeeded, but any excess characters were silently truncated. In MySQL 5.7.5 and later, trying to use a password of more than 32 characters causes CHANGE MASTER TO to fail. (Bug #11752299, Bug #43439)

    The text of a running CHANGE MASTER TO statement, including values for MASTER_USER and MASTER_PASSWORD, can be seen in the output of a concurrent SHOW PROCESSLIST statement. (The complete text of a START SLAVE statement is also visible to SHOW PROCESSLIST.)

The MASTER_SSL_xxx options, and the MASTER_TLS_VERSION option, specify how the slave uses encryption and ciphers to secure the replication connection. These options can be changed even on slaves that are compiled without SSL support. They are saved to the master info repository, but are ignored if the slave does not have SSL support enabled. The MASTER_SSL_xxx options perform the same functions as the --ssl-xxx options described in Section 6.4.2, “Command Options for Encrypted Connections”. The correspondence between the two sets of options, and the use of the MASTER_SSL_xxx and MASTER_TLS_VERSION options to set up a secure connection, is explained in Section 16.3.8, “Setting Up Replication to Use Encrypted Connections”.

As of MySQL 5.7.3, setting MASTER_SSL=1 means that the slave connection to the master must use SSL, or the connection attempt fails. Before 5.7.3, the use of an SSL connection by the slave was not enforced with MASTER_SSL=1. This is analogous to the client-side meaning of the --ssl command-line option; see Section 6.4.2, “Command Options for Encrypted Connections”.

MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries. The default is 60.

MASTER_RETRY_COUNT limits the number of reconnection attempts and updates the value of the Master_Retry_Count column in the output of SHOW SLAVE STATUS. The default value is 24 * 3600 = 86400. MASTER_RETRY_COUNT is intended to replace the older --master-retry-count server option, and is now the preferred method for setting this limit. You are encouraged not to rely on --master-retry-count in new applications and, when upgrading from versions earlier than MySQL 5.6, to update any existing applications that rely on it, so that they use CHANGE MASTER TO ... MASTER_RETRY_COUNT instead.

MASTER_DELAY specifies how many seconds behind the master the slave must lag. An event received from the master is not executed until at least interval seconds later than its execution on the master. The default is 0. An error occurs if interval is not a nonnegative integer in the range from 0 to 231−1. For more information, see Section 16.3.10, “Delayed Replication”.

From MySQL 5.7, a CHANGE MASTER TO statement employing the MASTER_DELAY option can be executed on a running slave when the slave SQL thread is stopped.

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.

The address configured with this option, if any, can be seen in the Master_Bind column of the output from SHOW SLAVE STATUS. If you are using slave status log tables (server started with --master-info-repository=TABLE), the value can also be seen as the Master_bind column of the mysql.slave_master_info table.

The ability to bind a replication slave to a specific network interface is also supported by NDB Cluster.

MASTER_HEARTBEAT_PERIOD sets 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 having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval.

Prior to MySQL 5.7.4, not including MASTER_HEARTBEAT_PERIOD caused CHANGE MASTER TO to reset the heartbeat period (Slave_heartbeat_period) to the default, and Slave_received_heartbeats to 0. (Bug #18185490)

If you are logging master connection information to tables, MASTER_HEARTBEAT_PERIOD can be seen as the value of the Heartbeat column of the mysql.slave_master_info table.

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

Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value.

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. 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. If you specify either of MASTER_LOG_FILE or MASTER_LOG_POS, you also cannot specify MASTER_AUTO_POSITION = 1 (described later in this section). If neither of MASTER_LOG_FILE or MASTER_LOG_POS is 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.

From MySQL 5.7, a CHANGE MASTER TO statement employing RELAY_LOG_FILE, RELAY_LOG_POS, or both options can be executed on a running slave when the slave SQL thread is stopped. Prior to MySQL 5.7.4, 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 log files are kept; the relay_log_purge global variable is set silently to 0. In MySQL 5.7.4 and later, relay logs are preserved if at least one of the slave SQL thread and the slave I/O thread is running; if both threads are stopped, all relay log files are deleted unless at least one of RELAY_LOG_FILE or RELAY_LOG_POS is specified.

RELAY_LOG_FILE can use either an absolute or relative path, and uses the same base name as MASTER_LOG_FILE. (Bug #12190)

When MASTER_AUTO_POSITION = 1 is used with CHANGE MASTER TO, the slave attempts to connect to the master using the GTID-based replication protocol. From MySQL 5.7, this option can be employed by CHANGE MASTER TO only if both the slave SQL and slave I/O threads are stopped. Both the slave and the master must have GTIDs enabled (GTID_MODE=ON, ON_PERMISSIVE, or OFF_PERMISSIVE on the slave, and GTID_MODE=ON on the master). Auto-positioning is used for the connection, so the coordinates represented by MASTER_LOG_FILE and MASTER_LOG_POS are not used, and the use of either or both of these options together with MASTER_AUTO_POSITION = 1 causes an error. If multi-source replication is enabled on the slave, you need to set the MASTER_AUTO_POSITION = 1 option for each applicable replication channel.

With MASTER_AUTO_POSITION = 1 set, in the initial connection handshake, the slave sends a GTID set containing the transactions that it has already received, committed, or both. The master responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the slave. This exchange ensures that the master only sends the transactions with a GTID that the slave has not already recorded or committed. If the slave receives transactions from more than one master, as in the case of a diamond topology, the auto-skip function ensures that the transactions are not applied twice. For details of how the GTID set sent by the slave is computed, see Section 16.1.3.3, “GTID Auto-Positioning”.

If any of the transactions that should be sent by the master have been purged from the master's binary log, or added to the set of GTIDs in the gtid_purged system variable by another method, the master sends the error ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the slave, and replication does not start. Also, if during the exchange of transactions it is found that the slave has recorded or committed transactions with the master's UUID in the GTID, but the master itself has not committed them, the master sends the error ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER to the slave and replication does not start. For information on how to handle these situations, see Section 16.1.3.3, “GTID Auto-Positioning”.

You can see whether replication is running with auto-positioning enabled by checking the Performance Schema replication_connection_status table or the output of SHOW SLAVE STATUS. Disabling the MASTER_AUTO_POSITION option again makes the slave revert to file-based replication, in which case you must also specify one or both of the MASTER_LOG_FILE or MASTER_LOG_POS options.

IGNORE_SERVER_IDS takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.

In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its master instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use.

If a CHANGE MASTER TO statement is issued without any IGNORE_SERVER_IDS option, any existing list is preserved. To clear the list of ignored servers, it is necessary to use the option with an empty list:

CHANGE MASTER TO IGNORE_SERVER_IDS = ();

Prior to MySQL 5.7.5, RESET SLAVE ALL has no effect on the server ID list. In MySQL 5.7.5 and later, RESET SLAVE ALL clears IGNORE_SERVER_IDS. (Bug #18816897)

If IGNORE_SERVER_IDS contains the server's own ID and the server was started with the --replicate-same-server-id option enabled, an error results.

The master info repository and the output of SHOW SLAVE STATUS provide the list of servers that are currently ignored. For more information, see Section 16.2.4.2, “Slave Status Logs”, and Section 13.7.5.34, “SHOW SLAVE STATUS Syntax”.

Invoking CHANGE MASTER TO causes the previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and MASTER_LOG_POS to be written to the error log, along with other information about the slave's state prior to execution.

CHANGE MASTER TO causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

In MySQL 5.7.4 and later, the strict requirement to execute STOP SLAVE prior to issuing any CHANGE MASTER TO statement (and START SLAVE afterward) is removed. Instead of depending on whether the slave is stopped, the behavior of CHANGE MASTER TO depends (in MySQL 5.7.4 and later) on the states of the slave SQL thread and slave I/O threads; which of these threads is stopped or running now determines the options that can or cannot be used with a CHANGE MASTER TO statement at a given point in time. The rules for making this determination are listed here:

  • If the SQL thread is stopped, you can execute CHANGE MASTER TO using any combination that is otherwise allowed of RELAY_LOG_FILE, RELAY_LOG_POS, and MASTER_DELAY options, even if the slave I/O thread is running. No other options may be used with this statement when the I/O thread is running.

  • If the I/O thread is stopped, you can execute CHANGE MASTER TO using any of the options for this statement (in any allowed combination) except RELAY_LOG_FILE, RELAY_LOG_POS, or MASTER_DELAY, even when the SQL thread is running. These three options may not be used when the I/O thread is running.

  • Both the SQL thread and the I/O thread must be stopped before issuing a CHANGE MASTER TO statement that employs MASTER_AUTO_POSITION = 1.

You can check the current state of the slave SQL and I/O threads using SHOW SLAVE STATUS.

For more information, see Section 16.3.7, “Switching Masters During Failover”.

If you are using statement-based replication and temporary tables, it is possible for a CHANGE MASTER TO statement following a STOP SLAVE statement to leave behind temporary tables on the slave. From MySQL 5.7, a warning (ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO) is issued whenever this occurs. You can avoid this in such cases by making sure that the value of the Slave_open_temp_tables system status variable is equal to 0 prior to executing such a CHANGE MASTER TO statement.

CHANGE MASTER TO is useful for setting up a slave when you have the snapshot of the master and have recorded the master binary log coordinates corresponding to the time of the snapshot. After loading the snapshot into the slave to synchronize it with the master, you can run CHANGE MASTER TO MASTER_LOG_FILE='log_name', MASTER_LOG_POS=log_pos on the slave to specify the coordinates at which the slave should begin reading the master binary log.

The following example changes the master server the slave uses and establishes the master binary log coordinates from which the slave begins reading. This is used when you want to set up the slave to replicate the master:

CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  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 log files 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;

The following table shows the maximum permissible length for the string-valued options.

Option Maximum Length
MASTER_HOST 60
MASTER_USER 96
MASTER_PASSWORD 32
MASTER_LOG_FILE 511
RELAY_LOG_FILE 511
MASTER_SSL_CA 511
MASTER_SSL_CAPATH 511
MASTER_SSL_CERT 511
MASTER_SSL_CRL 511
MASTER_SSL_CRLPATH 511
MASTER_SSL_KEY 511
MASTER_SSL_CIPHER 511
MASTER_TLS_VERSION 511

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.