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
http://dev.mysql.com/doc/refman/5.7/en/slave-logs.html).

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 multi-threaded 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 added in MySQL 5.7.6 enables
you to choose which replication channel the statement applies to. If no
clause is set and no extra channels exist, the statement applies to the
default channel and behaves the same as versions of MySQL prior to
5.7.6. 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_NAME=host1, MASTER_PORT=3002 FOR CHANNEL channel2

When using multiple replication channels, if a CHANGE MASTER TO
statement does not have a channel defined using a FOR CHANNEL channel
clause an error is generated. See
http://dev.mysql.com/doc/refman/5.7/en/replication-channels.html 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:

o 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)

o 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 provide information about using SSL for the
connection. They correspond to the --ssl-xxx options described in
http://dev.mysql.com/doc/refman/5.7/en/secure-connection-options.html,
and
http://dev.mysql.com/doc/refman/5.7/en/replication-solutions-secure-con
nections.html. 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.

As of MySQL 5.7.3, the MASTER_SSL=1 is prescriptive, not advisory. When
given, the slave connection to the master must use SSL or the
connection attempt fails. Before 5.7.3, an SSL connection is permitted
but not required. This is analogous to the client-side meaning of the
--ssl command-line option; see
http://dev.mysql.com/doc/refman/5.7/en/secure-connection-options.html.

The MASTER_TLS_VERSION option specifies the encryption protocols
permitted by the master for slave connections. The value is like that
for the tls_version system variable: A comma-separated list containing
one or more protocol names. The protocols that can be named for this
option depend on the SSL library used to compile MySQL. For details,
see
http://dev.mysql.com/doc/refman/5.7/en/secure-connection-protocols-ciph
ers.html. This option was added in MySQL 5.7.10.

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 to MySQL
5.7, 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
http://dev.mysql.com/doc/refman/5.7/en/replication-delayed.html.

In MySQL 5.7.4 and later, 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 MySQL 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.

In MySQL 5.7.4 and later, 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.

If MASTER_AUTO_POSITION = 1 is used with CHANGE MASTER TO, the slave
attempts to connect to the master using the GTID-based replication
protocol. In MySQL 5.7.4 and later, this option can be employed by
CHANGE MASTER TO only if both the slave SQL and slave I/O threads are
stopped.

When using GTIDs, the slave tells the master which transactions it has
already received, executed, or both. To compute this set, it reads the
global value of gtid_executed and the value of the Retrieved_gtid_set
column from SHOW SLAVE STATUS. The GTID of the last transmitted
transaction is included in Retrieved_gtid_set only when the full
transaction is received. The slave computes the following set:

UNION(@@global.gtid_executed, Retrieved_gtid_set)

Prior to MySQL 5.7.5, the GTID of the last transmitted transaction was
included in Retrieved_gtid_set even if the transaction was only
partially transmitted, and the last received GTID was subtracted from
this set. (Bug #17943188) Thus, the slave computed the following set:

UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)

This set is sent to the master as part of the initial handshake, and
the master sends back all transactions that it has executed which are
not part of the set. If any of these transactions have been already
purged from the master's binary log, the master sends the error
ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the slave, and replication does
not start.

When GTID-based replication is employed, the coordinates represented by
MASTER_LOG_FILE and MASTER_LOG_POS are not used, and global transaction
identifiers are used instead. Thus the use of either or both of these
options together with MASTER_AUTO_POSITION causes an error.

Beginning with MySQL 5.7.1, you can see whether replication is running
with autopositioning enabled by checking the output of SHOW SLAVE
STATUS. (Bug #15992220)

gtid_mode must also be enabled before issuing CHANGE MASTER TO ...
MASTER_AUTO_POSITION = 1. Otherwise, the statement fails with an error.

To revert to the older file-based replication protocol after using
GTIDs, you can issue a new CHANGE MASTER TO statement that specifies
MASTER_AUTO_POSITION = 0, as well as at least one of MASTER_LOG_FILE or
MASTER_LOG_POS.

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 when neither the slave SQL thread nor the slave I/O thread is
stopped; if both threads are stopped, all relay log files are deleted
unless you 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)

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.

In MySQL 5.7, the master info repository and the output of SHOW SLAVE
STATUS provide the list of servers that are currently ignored. For more
information, see
http://dev.mysql.com/doc/refman/5.7/en/slave-logs-status.html, and
[HELP SHOW SLAVE STATUS].

In MySQL 5.7, 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.

In MySQL 5.7, CHANGE MASTER TO causes an implicit commit of an ongoing
transaction. See
http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html.

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:

o 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.

o 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.

o 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
http://dev.mysql.com/doc/refman/5.7/en/replication-solutions-switch.htm
l.

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. In MySQL 5.7.4
and later, a warning (ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO) is now
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.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 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;

URL: http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

