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 files. To use
CHANGE MASTER TO, the slave
replication threads must be stopped (use
STOP SLAVE if necessary).

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, 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

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)

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

In MySQL 5.5.20 and later, 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; if the password is longer, the
statement succeeds, but any excess characters are silently
truncated. This is an issue specific to MySQL Replication,
which is fixed in MySQL 5.7. (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 MASTER_BIND option is available in MySQL
Cluster NDB 7.2 and later, but is not supported in mainline
MySQL 5.5.

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.

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.

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

CHANGE MASTER TOdeletes 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.

Prior to MySQL 5.5, RELAY_LOG_FILE required
an absolute path. In MySQL 5.5, the path can be
relative, in which case the path is assumed to be relative to
the slave's data directory. (Bug #12190)

IGNORE_SERVER_IDS was added in MySQL 5.5.
This option 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.

When 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 = ();

RESET SLAVE
ALL has no effect on the server ID list. This issue is
fixed in MySQL 5.7. (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.

Beginning with MySQL 5.5.5, 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 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:

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

You can even use the second operation in a nonreplication setup
with a standalone, nonslave server for recovery following a
crash. Suppose that your server has crashed and you have
restored it from a backup. You want to replay the server's own
binary log files (not relay log files, but regular binary log
files), named (for example) myhost-bin.*.
First, make a backup copy of these binary log files in some safe
place, in case you don't exactly follow the procedure below and
accidentally have the server purge the binary log. Use
SET GLOBAL relay_log_purge=0 for additional
safety. Then start the server without the
--log-bin option, Instead, use
the --replicate-same-server-id,
--relay-log=myhost-bin (to make
the server believe that these regular binary log files are relay
log files) and --skip-slave-start
options. After the server starts, issue these statements:

The server reads and executes its own binary log files, thus
achieving crash recovery. Once the recovery is finished, run
STOP SLAVE, shut down the server,
delete the master.info and
relay-log.info files, and restart the
server with its original options.

Specifying the MASTER_HOST option (even with
a dummy value) is required to make the server think it is a
slave.

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