top of page



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_DELAY = interval
| MASTER_LOG_FILE = 'source_log_name'
| MASTER_LOG_POS = source_log_pos
| 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_TLS_VERSION = 'protocol_list'
| MASTER_TLS_CIPHERSUITES = 'ciphersuite_list'
| MASTER_PUBLIC_KEY_PATH = 'key_file_name'
| IGNORE_SERVER_IDS = (server_id_list)


[server_id [, server_id] ... ]

CHANGE MASTER TO changes the parameters that the replica server uses
for connecting to the source, 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 CHANGE
MASTER TO requires the REPLICATION_SLAVE_ADMIN privilege (or the
deprecated SUPER privilege).

You can issue CHANGE MASTER TO statements on a running replica without
first stopping it, depending on the states of the replication SQL
thread and replication I/O thread. The rules governing such use are
provided later in this section.

When using a multithreaded replica (in other words
slave_parallel_workers is greater than 0), stopping the replica can
cause "gaps" in the sequence of transactions that have been executed
from the relay log, regardless of whether the replica 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:


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

information to the replica about how to connect to its source:

o MASTER_HOST and MASTER_PORT are the host name (or IP address) of the
source server 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.
Trying to set MASTER_HOST to an empty string fails with an error.

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
.html#error_er_master_info). (Bug #11758581, Bug #50801)

o MASTER_USER and MASTER_PASSWORD are the user name and password of the
replication user 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; trying to use a password of
more than 32 characters causes CHANGE MASTER TO to fail.

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. In releases before MySQL 8.0.21, only set an empty
MASTER_USER user name if you need to clear previously used
credentials from the replication metadata repositories for security
purposes. Do not use the channel afterwards, due to a bug in these
releases that can substitute a default user name if an empty user
name is read from the repositories (for example, during an automatic
restart of a Group Replication channel). From MySQL 8.0.21, it is
valid to set an empty MASTER_USER user name and use the channel
afterwards if you always provide user credentials using the START
SLAVE statement or START GROUP_REPLICATION statement that starts the
replication channel. This approach means that the replication channel
always needs operator intervention to restart, but the user
credentials are not recorded in the replication metadata

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

REQUIRE_ROW_FORMAT (available as of MySQL 8.0.19) permits only
row-based replication events to be processed by the replication
channel. This option prevents the replication applier from taking
actions such as creating temporary tables and executing LOAD DATA
INFILE requests, which increases the security of the channel. Group
Replication channels are automatically created with REQUIRE_ROW_FORMAT
set, and you cannot change the option for those channels. For more
information, see

PRIVILEGE_CHECKS_USER (available as of MySQL 8.0.18) names a user
account that supplies a security context for the specified channel.
NULL, which is the default, means no security context is used. The use
of row-based binary logging is strongly recommended when
enforce this. For example, to start privilege checks on the channel
channel_1 on a running replica, issue the following statements:

mysql> STOP SLAVE FOR CHANNEL 'channel_1';
PRIVILEGE_CHECKS_USER = 'priv_repl'@'',
FOR CHANNEL 'channel_1';
mysql> START SLAVE FOR CHANNEL 'channel_1';

The user name and host name for the user account must follow the syntax
described in, and the
user must not be an anonymous user (with a blank user name) or the
CURRENT_USER. The account must have the REPLICATION_APPLIER privilege,
plus the required privileges to execute the transactions replicated on
the channel. For details of the privileges required by the account, see
ml. When you restart the replication channel, the privilege checks are
applied from that point on. If you do not specify a channel and no
other channels exist, the statement is applied to the default channel.

REQUIRE_TABLE_PRIMARY_KEY_CHECK (available as of MySQL 8.0.20) enables
a replica to select its own policy for primary key checks. When the
option is set to ON for a replication channel, the replica always uses
the value ON for the sql_require_primary_key system variable in
replication operations, requiring a primary key. When the option is set
to OFF, the replica always uses the value OFF for the
sql_require_primary_key system variable in replication operations, so
that a primary key is never required, even if the source required one.
When the REQUIRE_TABLE_PRIMARY_KEY_CHECK option is set to STREAM, which
is the default, the replica uses whatever value is replicated from the
source for each transaction.

o For multisource replication, setting REQUIRE_TABLE_PRIMARY_KEY_CHECK
to ON or OFF enables a replica to normalize behavior across the
replication channels for different sources, and keep a consistent
setting for the sql_require_primary_key system variable. Using ON
safeguards against the accidental loss of primary keys when multiple
sources update the same set of tables. Using OFF allows sources that
can manipulate primary keys to work alongside sources that cannot.

o When PRIVILEGE_CHECKS_USER is set, setting
REQUIRE_TABLE_PRIMARY_KEY_CHECK to ON or OFF means that the user
account does not need session administration level privileges to set
restricted session variables, which are required to change the value
of sql_require_primary_key to match the source's setting for each
transaction. For more information, see

(available as of MySQL 8.0.18) enable control over the use of
compression for connections to the source:

o MASTER_COMPRESSION_ALGORITHMS specifies the permitted compression
algorithms. The available algorithms are the same as for the
protocol_compression_algorithms system variable. The default value is

The value of MASTER_COMPRESSION_ALGORITHMS applies only if the
slave_compressed_protocol system variable is disabled. If
slave_compressed_protocol is enabled, it takes precedence over
MASTER_COMPRESSION_ALGORITHMS and connections to the source use zlib
compression if both source and replica support that algorithm.

o MASTER_ZSTD_COMPRESSION_LEVEL is the compression level to use for
connections that use the zstd compression algorithm. The permitted
levels are from 1 to 22, with larger values indicating increasing
levels of compression. The default zstd compression level is 3. The
compression level setting has no effect on connections that do not
use zstd compression.

For more information, see

Binary log transaction compression (available as of MySQL 8.0.20),
which is activated by the binlog_transaction_compression system
variable, can also be used to save bandwidth. If you do this in
combination with connection compression, connection compression has
less opportunity to act on the data, but can still compress headers and
those events and transaction payloads that are uncompressed. For more
information on binary log transaction compression, see

The MASTER_SSL_xxx options and the MASTER_TLS_xxx options specify how
the replica uses encryption and ciphers to secure the replication
connection. These options can be changed even on replicas that are
compiled without SSL support. They are saved to the source metadata
repository, but are ignored if the replica does not have SSL support
enabled. The MASTER_SSL_xxx and MASTER_TLS_xxx options perform the same
functions as the --ssl-xxx and --tls-xxx client options described in
ed-connection-options. The correspondence between the two sets of
options, and the use of the MASTER_SSL_xxx and MASTER_TLS_xxx options
to set up a secure connection, is explained in


To connect to the source using a replication user account that
authenticates with the caching_sha2_password plugin, you must either
set up a secure connection as described in
-connections.html, or enable the unencrypted connection to support
password exchange using an RSA key pair. The caching_sha2_password
authentication plugin is the default for new users created from MySQL
8.0 (for details, see
cation.html). If the user account that you create or use for
replication (as specified by the MASTER_USER option) uses this
authentication plugin, and you are not using a secure connection, you
must enable RSA key pair-based password exchange for a successful

To enable RSA key pair-based password exchange, specify either the
these options provides the RSA public key to the replica:

o MASTER_PUBLIC_KEY_PATH indicates the path name to a file containing a
replica-side copy of the public key required by the source for RSA
key pair-based password exchange. The file must be in PEM format.
This option applies to replicas that authenticate with the
sha256_password or caching_sha2_password authentication plugin. (For
sha256_password, MASTER_PUBLIC_KEY_PATH can be used only if MySQL was
built using OpenSSL.)

o GET_MASTER_PUBLIC_KEY indicates whether to request from the source
the public key required for RSA key pair-based password exchange.
This option applies to replicas that authenticate with the
caching_sha2_password authentication plugin. For connections by
accounts that authenticate using this plugin, the source does not
send the public key unless requested, so it must be requested or
specified in the client. If MASTER_PUBLIC_KEY_PATH is given and
specifies a valid public key file, it takes precedence over

MASTER_RETRY_COUNT options control how the replica recognizes that the
connection to the source has been lost and makes attempts to reconnect.

o The slave_net_timeout system variable specifies the number of seconds
that the replica waits for either more data or a heartbeat signal
from the source, before the replica considers the connection broken,
aborts the read, and tries to reconnect. The default value is 60
seconds (one minute).

o The heartbeat interval, which stops the connection timeout occurring
in the absence of data if the connection is still good, is controlled
by the MASTER_HEARTBEAT_PERIOD option. A heartbeat signal is sent to
the replica after that number of seconds, and the waiting period is
reset whenever the source's binary log is updated with an event.
Heartbeats are therefore sent by the source only if there are no
unsent events in the binary log file for a period longer than this.
The heartbeat interval interval is a decimal value having the range 0
to 4294967 seconds and a resolution in milliseconds; the smallest
nonzero value is 0.001. Setting interval to 0 disables heartbeats
altogether. The heartbeat interval defaults to half the value of the
slave_net_timeout system variable. It is recorded in the source
metadata repository and shown in the
replication_connection_configuration Performance Schema table.
Issuing RESET SLAVE resets the heartbeat interval to the default

Note that a change to the value or default setting of
slave_net_timeout does not automatically change the heartbeat
interval, whether that has been set explicitly or is using a
previously calculated default. A warning is issued if you set
@@GLOBAL.slave_net_timeout to a value less than that of the current
heartbeat interval. If slave_net_timeout is changed, you must also
issue CHANGE MASTER TO to adjust the heartbeat interval to an
appropriate value so that the heartbeat signal occurs before the
connection timeout. If you do not do this, the heartbeat signal has
no effect, and if no data is received from the source, the replica
can make repeated reconnection attempts, creating zombie dump

o If the replica does need to reconnect, the first retry occurs
immediately after the timeout. MASTER_CONNECT_RETRY specifies the
interval between reconnection attempts, and MASTER_RETRY_COUNT limits
the number of reconnection attempts. If both the default settings are
used, the replica waits 60 seconds between reconnection attempts
(MASTER_CONNECT_RETRY=60), and keeps attempting to reconnect at this
rate for 60 days (MASTER_RETRY_COUNT=86400). These values are
recorded in the source metadata repository and shown in the
replication_connection_configuration Performance Schema table.
MASTER_RETRY_COUNT supersedes the --master-retry-count server startup

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

A CHANGE MASTER TO statement employing the MASTER_DELAY option can be
executed on a running replica when the replication SQL thread is

MASTER_BIND is for use on replicas that have 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. In the source
metadata repository table mysql.slave_master_info, the value can be
seen as the Master_bind column.

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

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

RELAY_LOG_FILE can use either an absolute or relative path, and uses
the same base name as MASTER_LOG_FILE. A CHANGE MASTER TO statement
employing RELAY_LOG_FILE, RELAY_LOG_POS, or both options can be
executed on a running replica when the replication SQL thread is
stopped. Relay logs are preserved if at least one of the replication
SQL thread and the replication 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. Note that the Group
Replication applier channel (group_replication_applier) has no I/O
thread, only a SQL thread. For this channel, the relay logs are not
preserved when the SQL thread is stopped.

replica attempts to connect to the source using the GTID-based
replication protocol. This option can be used with CHANGE MASTER TO
only if both the replication SQL thread and replication I/O thread are
stopped. Both the replica and the source must have GTIDs enabled
GTID_MODE=ON on the source). 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 replica, you need to set the
MASTER_AUTO_POSITION = 1 option for each applicable replication

With MASTER_AUTO_POSITION = 1 set, in the initial connection handshake,
the replica sends a GTID set containing the transactions that it has
already received, committed, or both. The source responds by sending
all transactions recorded in its binary log whose GTID is not included
in the GTID set sent by the replica. This exchange ensures that the
source only sends the transactions with a GTID that the replica has not
already recorded or committed. If the replica receives transactions
from more than one source, 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 replica is computed, see

If any of the transactions that should be sent by the source have been
purged from the source's binary log, or added to the set of GTIDs in
the gtid_purged system variable by another method, the source sends the
error ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the replica, and
replication does not start. The GTIDs of the missing purged
transactions are identified and listed in the source's error log in the
warning message ER_FOUND_MISSING_GTIDS. Also, if during the exchange of
transactions it is found that the replica has recorded or committed
transactions with the source's UUID in the GTID, but the source itself
has not committed them, the source sends the error
ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER to the replica and replication does
not start. For information on how to handle these situations, see

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

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


When global transaction identifiers (GTIDs) are used for replication,
transactions that have already been applied are automatically ignored,
so the IGNORE_SERVER_IDS function is not required and is deprecated. If
gtid_mode=ON is set for the server, a deprecation warning is issued if
you include the IGNORE_SERVER_IDS option in a CHANGE MASTER TO

The source metadata repository and the output of SHOW SLAVE STATUS
provide the list of servers that are currently ignored. For more
information, see, and

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:




A deprecation warning is issued if SET GTID_MODE=ON is issued when any
channel has existing server IDs set with IGNORE_SERVER_IDS. Before
starting GTID-based replication, check for and clear all ignored server
ID lists on the servers involved. The SHOW_SLAVE_STATUS statement
displays the list of ignored IDs, if there is one. If you do receive
the deprecation warning, you can still clear a list after gtid_mode=ON
is set by issuing a CHANGE MASTER TO statement containing the
IGNORE_SERVER_IDS option with an empty list.

Invoking CHANGE MASTER TO causes the previous values for MASTER_HOST,
error log, along with other information about the replica's state prior
to execution.

CHANGE MASTER TO causes an implicit commit of an ongoing transaction.

From MySQL 5.7, 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 replica is stopped, the
behavior of CHANGE MASTER TO depends on the states of the replication
SQL thread and replication I/O thread; 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 replication 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)
MASTER_AUTO_POSITION = 1 even when the SQL 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 replication SQL thread and
replication I/O thread using SHOW SLAVE STATUS. Note that the Group
Replication applier channel (group_replication_applier) has no I/O
thread, only a SQL thread.

For more information, see

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 replica. A warning
tml#error_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 replica when you have the
snapshot of the source and have recorded the source's binary log
coordinates corresponding to the time of the snapshot. After loading
the snapshot into the replica to synchronize it with the source, you
MASTER_LOG_POS=log_pos on the replica to specify the coordinates at
which the replica should begin reading the source's binary log.

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:


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




bottom of page