MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
CHANGE MASTER TOoption
[,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 = 'source_log_name
' | MASTER_LOG_POS =source_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} | IGNORE_SERVER_IDS = (server_id_list
) }server_id_list
: [server_id
[,server_id
] ... ]
CHANGE MASTER TO
changes the
parameters that the replica uses for connecting to the
replication source server, for reading the source's binary log,
and reading the replica's relay log. It also updates the
contents of the replication metadata repositories (see
Section 17.2.2, “Relay Log and Replication Metadata Repositories”). CHANGE
MASTER TO
requires the
SUPER
privilege.
To use CHANGE MASTER TO
, the
replication threads must be stopped (use
STOP SLAVE
if necessary). In
MySQL 5.6.11 and later,
gtid_next
must also be set to
AUTOMATIC
(Bug #16062608).
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 source has changed, issue these statements to tell the replica 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
replica about how to connect to its source:
MASTER_HOST
and
MASTER_PORT
are the host name (or IP
address) of the source host and its TCP/IP port.
Replication cannot use Unix socket files. You must be able to connect to the source MySQL server using TCP/IP.
If you specify the MASTER_HOST
or
MASTER_PORT
option, the replica assumes
that the source 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 source's 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)
In MySQL 5.6.5 and later, 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 source.
If you specify MASTER_PASSWORD
,
MASTER_USER
is also required. The
password used for a replication user 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)
It is possible to set an empty user name by specifying
MASTER_USER=''
, but the replication
channel cannot be started with an empty user name. Only set
an empty MASTER_USER
user name if you
need to clear previously used credentials from the replica's
repositories for security purposes, and do not attempt to
use the channel afterwards. (Bug #13427949)
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_
options provide information about using SSL for the connection.
They correspond to the
xxx
--ssl-
options
described in Command Options for Encrypted Connections, and
Section 17.3.8, “Setting Up Replication to Use Encrypted Connections”. These
options can be changed even on replicas that are compiled
without SSL support. They are saved to the connection metadata
repository, but are ignored if the replica does not have SSL
support enabled. xxx
MASTER_SSL_CRL
and
MASTER_SSL_CRLPATH
were added in MySQL 5.6.3.
To help prevent sophisticated man-in-the-middle attacks, it is
important for the replica to verify the server’s identity.
With the additional
MASTER_SSL_
settings, the replica checks that the server’s certificate
is valid. To implement this level of verification, you must
first ensure that the CA certificate for the server is
reliably available to the replica, otherwise availability
issues will result.
xxx
MASTER_CONNECT_RETRY
specifies how many
seconds to wait between connect retries. The default is 60.
MASTER_RETRY_COUNT
, added in MySQL 5.6.1,
limits the number of reconnection attempts
and updates the value of the
Master_Retry_Count
column in the output of
SHOW SLAVE STATUS
(also added in
MySQL 5.6.1). 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.6.1 or later from
earlier versions of MySQL, 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 source the replica must lag. An event received from
the source is not executed until at least
interval
seconds later than its
execution on the source. 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 17.3.10, “Delayed Replication”.
This option was added in MySQL 5.6.0.
MASTER_BIND
is for use on replicas having
multiple network interfaces, and determines which of the
replica's network interfaces is chosen for connecting to the
source.
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 tables for the replication metadata repositories (the
server was 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 replica to a specific network interface was added in MySQL 5.6.2. This is also supported in MySQL NDB Cluster 7.3 and later.
MASTER_HEARTBEAT_PERIOD
sets the interval in
seconds between replication heartbeats. Whenever the source'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 source only if there are no unsent
events in the binary log file for a period longer than
interval
.
If you are logging source 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 replication I/O thread should begin reading from the source
the next time the thread starts.
RELAY_LOG_FILE
and
RELAY_LOG_POS
are the coordinates at which
the replication 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
. In MySQL 5.6.5 and later, 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 replica
uses the last coordinates of the replication SQL
thread before CHANGE MASTER
TO
was issued. This ensures that there is no
discontinuity in replication, even if the replication SQL thread
was late compared to the replication I/O thread, when you merely
want to change, say, the password to use.
MASTER_AUTO_POSITION
was added in MySQL
5.6.5. If MASTER_AUTO_POSITION = 1
is used
with CHANGE MASTER TO
, the replica attempts
to connect to the source using the GTID-based replication
protocol.
When using GTIDs, the replica tells the source 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
. Since the GTID
of the last transmitted transaction is included in
Retrieved_gtid_set
even if the transaction
was only partially transmitted, the last received GTID is
subtracted from this set. Thus, the replica computes the
following set:
UNION(@@GLOBAL.gtid_executed, Retrieved_gtid_set - last_received_GTID
)
This set is sent to the source as part of the initial handshake,
and the source 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 source's binary log, the
source sends the error
ER_MASTER_HAS_PURGED_REQUIRED_GTIDS
to the replica, 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.6.10, 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
.
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.
Prior to MySQL 5.6.2, RELAY_LOG_FILE
required
an absolute path. Beginning with MySQL 5.6.2, the path can be
relative, in which case it is assumed to be relative to the
replica's data directory. (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 source 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.
In MySQL 5.6, the connection metadata repository
and the output of SHOW SLAVE
STATUS
provide the list of servers that are currently
ignored. For more information, see
Section 17.2.2.2, “Replication Metadata Repositories”, and
Section 13.7.5.35, “SHOW SLAVE STATUS Statement”.
In MySQL 5.6, 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 replica's state
prior to execution.
In MySQL 5.6.7 and later, CHANGE MASTER TO
causes an implicit commit of an ongoing transaction. See
Section 13.3.3, “Statements That Cause an Implicit Commit”.
CHANGE MASTER TO
is useful for
setting up a replica when you have the snapshot of the source
and have recorded the source binary log coordinates
corresponding to the time of the snapshot. After loading the
snapshot into the replica to synchronize it with the source, you
can run CHANGE MASTER TO
MASTER_LOG_FILE='
on
the replica to specify the coordinates at which the replica
should begin reading the source's binary log.
log_name
',
MASTER_LOG_POS=log_pos
The following example changes the source server the replica uses and establishes the source's binary log coordinates from which the replica begins reading. This is used when you want to set up the replica to replicate the source:
CHANGE MASTER TO MASTER_HOST='source2.example.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='source2-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 replica has relay log files that
you want it to execute again for some reason. To do this, the
source 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='replica-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 |
16 |
MASTER_PASSWORD |
32 |
MASTER_LOG_FILE |
255 |
RELAY_LOG_FILE |
255 |
MASTER_SSL_CA |
255 |
MASTER_SSL_CAPATH |
255 |
MASTER_SSL_CERT |
255 |
MASTER_SSL_CRL |
255 |
MASTER_SSL_CRLPATH |
255 |
MASTER_SSL_KEY |
255 |
MASTER_SSL_CIPHER |
511 |