RESET MASTER [TO binary_log_file_index_number]
Use this statement with caution to ensure you do not lose any wanted binary log file data and GTID execution history.
RESET MASTER requires the
For a server where binary logging is enabled
RESET MASTER deletes
all existing binary log files and resets the binary log index
file, resetting the server to its state before binary logging
was started. A new empty binary log file is created so that
binary logging can be restarted.
For a server where GTIDs are in use
resets the GTID execution history. The value of the
gtid_purged system variable is
set to an empty string (
''), the global value
(but not the session value) of the
gtid_executed system variable
is set to an empty string, and the
mysql.gtid_executed table is cleared (see
mysql.gtid_executed Table). If the
GTID-enabled server has binary logging enabled,
RESET MASTER also resets the
binary log as described above. Note that
RESET MASTER is the method to
reset the GTID execution history even if the GTID-enabled server
is a replica where binary logging is disabled;
REPLICA has no effect on the GTID execution history.
For more information on resetting the GTID execution history,
see Resetting the GTID Execution History.
RESET MASTER without the optional
TO clause deletes all binary log files listed
in the index file, resets the binary log index file to be empty,
and creates a new binary log file starting at
1. Use the optional
clause to start the binary log file index from a number other
1 after the reset.
RESET MASTER with the
TO clause to specify a binary log file index
number to start from simplifies failover by providing a single
statement alternative to the
LOGS TO statements. Check that you are using a
reasonable value for the index number. If you enter an incorrect
value, you can correct this by issuing another
RESET MASTER statement with or
TO clause. If you do not correct
a value that is out of range, the server cannot be restarted.
The following example demonstrates
RESET MASTER TO 1234; SHOW BINARY LOGS; +-------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +-------------------+-----------+-----------+ | source-bin.001234 | 154 | No | +-------------------+-----------+-----------+
The effects of
TO clause differ from those of
PURGE BINARY LOGS in 2 key
RESET MASTERremoves all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of
.000001, whereas the numbering is not reset by
PURGE BINARY LOGS.
RESET MASTERis not intended to be used while any replicas are running. The behavior of
RESET MASTERwhen used while replicas are running is undefined (and thus unsupported), whereas
PURGE BINARY LOGSmay be safely used while replicas are running.
RESET MASTER without the
TO clause can prove useful when you first set
up a source and replica, so that you can verify the setup as
Start the source and replica, and start replication (see Section 17.1.2, “Setting Up Binary Log File Position Based Replication”).
Execute a few test queries on the source.
Check that the queries were replicated to the replica.
When replication is running correctly, issue
STOP REPLICAfollowed by
RESET REPLICAon the replica, then verify that no unwanted data from the test queries exists on the replica.
RESET MASTERon the source to clean up the test queries.
After verifying the setup, resetting the source and replica and ensuring that no unwanted data or binary log files generated by testing remain on the source or replica, you can start the replica and begin replicating.