Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.3Kb
Man Pages (Zip) - 402.5Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.4.1.2 RESET MASTER Statement

Note

This statement is replaced in later versions of MySQL byRESET BINARY LOGS AND GTIDS, and should be considered deprecated. SeeRESET BINARY LOGS AND GTIDS Statement, in theMySQL 8.4 Manual, for more information.

RESET MASTER [TObinary_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 MASTER requires theRELOAD privilege.

For a server where binary logging is enabled (log_bin isON),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 (gtid_mode isON), issuingRESET MASTER resets the GTID execution history. The value of thegtid_purged system variable is set to an empty string (''), the global value (but not the session value) of thegtid_executed system variable is set to an empty string, and themysql.gtid_executed table is cleared (seemysql.gtid_executed Table). If the GTID-enabled server has binary logging enabled,RESET MASTER also resets the binary log as described above. Note thatRESET MASTER 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, seeResetting the GTID Execution History.

IssuingRESET MASTER without the optionalTO 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 at1. Use the optionalTO clause to start the binary log file index from a number other than1 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 anotherRESET MASTER statement with or without theTO clause. If you do not correct a value that is out of range, the server cannot be restarted.

The following example demonstratesTO clause usage:

RESET MASTER TO 1234;SHOW BINARY LOGS;+-------------------+-----------+-----------+| Log_name          | File_size | Encrypted |+-------------------+-----------+-----------+| source-bin.001234 |       154 | No        |+-------------------+-----------+-----------+
Important

The effects ofRESET MASTER without theTO clause differ from those ofPURGE BINARY LOGS in 2 key ways:

  1. RESET MASTER removesall 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.

  2. RESET MASTER isnot intended to be used while any replicas are running. The behavior ofRESET MASTER when used while replicas are running is undefined (and thus unsupported), whereasPURGE BINARY LOGS may be safely used while replicas are running.

See alsoSection 15.4.1.1, “PURGE BINARY LOGS Statement”.

RESET MASTER without theTO clause can prove useful when you first set up a source and replica, so that you can verify the setup as follows:

  1. Start the source and replica, and start replication (seeSection 19.1.2, “Setting Up Binary Log File Position Based Replication”).

  2. Execute a few test queries on the source.

  3. Check that the queries were replicated to the replica.

  4. When replication is running correctly, issueSTOP REPLICA followed byRESET REPLICA on the replica, then verify that no unwanted data from the test queries exists on the replica.

  5. Remove the unwanted data from the source, then issueRESET MASTER to 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.