MySQL 9.1 Release Notes
RESET BINARY LOGS AND GTIDS [TO binary_log_file_index_number]
Warning
Use this statement with caution to ensure you do not lose any wanted binary log file data and GTID execution history.
RESET BINARY LOGS AND GTIDS requires the
RELOAD privilege.
For a server where binary logging is enabled
(log_bin is
ON), RESET BINARY LOGS AND
GTIDS 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
(gtid_mode is
ON), issuing RESET BINARY LOGS AND
GTIDS 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
BINARY LOGS AND GTIDS also resets the binary log as
described above. Note that RESET BINARY LOGS AND
GTIDS is the method to reset the GTID execution
history even if the GTID-enabled server is a replica where
binary logging is disabled;
RESET
REPLICA has no effect on the GTID execution history.
For more information on resetting the GTID execution history,
see Resetting the GTID Execution History.
Issuing RESET BINARY LOGS AND GTIDS 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 TO
clause to start the binary log file index from a number other
than 1 after the reset.
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 BINARY LOGS AND GTIDS
statement with or without the TO clause. If
you do not correct a value that is out of range, the server
cannot be restarted.
The following example demonstrates TO clause
usage:
RESET BINARY LOGS AND GTIDS TO 1234;
SHOW BINARY LOGS;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| source-bin.001234 | 154 | No |
+-------------------+-----------+-----------+
Important
The effects of RESET BINARY LOGS AND GTIDS
without the TO clause differ from those of
PURGE BINARY LOGS in 2 key
ways:
RESET BINARY LOGS AND GTIDSremoves 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 byPURGE BINARY LOGS.RESET BINARY LOGS AND GTIDSis not intended to be used while any replicas are running. The behavior ofRESET BINARY LOGS AND GTIDSwhen used while replicas are running is undefined (and thus unsupported), whereasPURGE BINARY LOGSmay be safely used while replicas are running.
RESET BINARY LOGS AND GTIDS without the
TO clause can prove useful when you first set
up a source and replica, so that you can verify the setup as
follows:
Start the source and replica, and start replication (see Section 19.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 byRESET REPLICA(both on the replica), then verify that no unwanted data from the test queries exists on the replica. Following this, issueRESET BINARY LOGS AND GTIDS(also on the replica) to remove binary logs and and associated transaction IDs.Remove the unwanted data from the source, then issue
RESET BINARY LOGS AND GTIDSto purge any binary log entries and identifiers associated with it.
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.