MySQL:
RESET SLAVE
Syntax:
RESET SLAVE [ALL] [channel_option]
channel_option:
FOR CHANNEL channel
RESET SLAVE makes the replica forget its position in the source's
binary log. This statement is meant to be used for a clean start: It
clears the replication metadata repositories, deletes all the relay log
files, and starts a new relay log file. It also resets to 0 the
replication delay specified with the MASTER_DELAY option to CHANGE
MASTER TO.
*Note*:
All relay log files are deleted, even if they have not been completely
executed by the replication SQL thread. (This is a condition likely to
exist on a replica if you have issued a STOP SLAVE statement or if the
replica is highly loaded.)
For a server where GTIDs are in use (gtid_mode is ON), issuing RESET
SLAVE has no effect on the GTID execution history. The statement does
not change the values of gtid_executed or gtid_purged, or the
mysql.gtid_executed table. If you need to reset the GTID execution
history, use RESET MASTER, even if the GTID-enabled server is a replica
where binary logging is disabled.
RESET SLAVE requires the RELOAD privilege.
To use RESET SLAVE, the replication SQL thread and replication I/O
thread must be stopped, so on a running replica use STOP SLAVE before
issuing RESET SLAVE. To use RESET SLAVE on a Group Replication group
member, the member status must be OFFLINE, meaning that the plugin is
loaded but the member does not currently belong to any group. A group
member can be taken offline by using a STOP GROUP REPLICATION
statement.
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 RESET SLAVE statement to a specific
replication channel. Combining a FOR CHANNEL channel clause with the
ALL option deletes the specified channel. If no channel is named and no
extra channels exist, the statement applies to the default channel.
Issuing a RESET SLAVE ALL statement without a FOR CHANNEL channel
clause when multiple replication channels exist deletes all replication
channels and recreates only the default channel. See
https://dev.mysql.com/doc/refman/8.0/en/replication-channels.html for
more information.
RESET SLAVE does not change any replication connection parameters,
which include the source's host name and port, the replication user
account and its password, the PRIVILEGE_CHECKS_USER account, the
REQUIRE_ROW_FORMAT option, and the REQUIRE_TABLE_PRIMARY_KEY_CHECK
option. If you want to change any of the replication connection
parameters, you can do this using a CHANGE MASTER TO statement after
the server start. If you want to remove all of the replication
connection parameters, use RESET SLAVE ALL. RESET SLAVE ALL also clears
the IGNORE_SERVER_IDS list set by CHANGE MASTER TO. When you have used
RESET SLAVE ALL, if you want to use the instance as a replica again,
you need to issue a CHANGE MASTER TO statement after the server start
to specify new connection parameters.
In the event of an unexpected server exit or deliberate restart after
issuing RESET SLAVE but before issuing START SLAVE, retention of the
replication connection parameters depends on the repository used for
the replication metadata:
o When master_info_repository=TABLE and relay_log_info_repository=TABLE
are set on the server (which are the default settings from MySQL
8.0), replication connection parameters are preserved in the
crash-safe InnoDB tables mysql.slave_master_info and
mysql.slave_relay_log_info as part of the RESET SLAVE operation. They
are also retained in memory. In the event of an unexpected server
exit or deliberate restart after issuing RESET SLAVE but before
issuing START SLAVE, the replication connection parameters are
retrieved from the tables and reapplied to the channel. This
situation applies from MySQL 8.0.13 for the source metadata
repository, and from MySQL 8.0.19 for the replica metadata
repository.
o If master_info_repository=FILE and relay_log_info_repository=FILE are
set on the server, or the MySQL Server release is earlier than those
specified above, replication connection parameters are only retained
in memory. If the replica mysqld is restarted immediately after
issuing RESET SLAVE due to an unexpected server exit or deliberate
restart, the connection parameters are lost. In that case, you must
issue a CHANGE MASTER TO statement after the server start to
respecify the connection parameters before issuing START SLAVE. Note
that the FILE setting for these options is deprecated, and will be
removed in a future release.
RESET SLAVE does not change any replication filter settings (such as
--replicate-ignore-table) for channels affected by the statement.
However, RESET SLAVE ALL removes the replication filters that were set
on the channels deleted by the statement. When the deleted channel or
channels are recreated, any global replication filters specified for
the replica are copied to them, and no channel specific replication
filters are applied. For more information see
https://dev.mysql.com/doc/refman/8.0/en/replication-rules-channel-based
-filters.html.
RESET SLAVE causes an implicit commit of an ongoing transaction. See
https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html.
If the replication SQL thread was in the middle of replicating
temporary tables when it was stopped, and RESET SLAVE is issued, these
replicated temporary tables are deleted on the replica.
RESET SLAVE does not reset the heartbeat period or
SSL_VERIFY_SERVER_CERT.
*Note*:
When used on an NDB Cluster replica SQL node, RESET SLAVE clears the
mysql.ndb_apply_status table. You should keep in mind when using this
statement that ndb_apply_status uses the NDB storage engine and so is
shared by all SQL nodes attached to the cluster.
You can override this behavior by issuing SET GLOBAL
@@ndb_clear_apply_status=OFF prior to executing RESET SLAVE, which
keeps the replica from purging the ndb_apply_status table in such
cases.
URL: https://dev.mysql.com/doc/refman/8.0/en/reset-slave.html
Example