Documentation Home
MySQL Utilities 1.6 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
HTML Download (TGZ) - 210.7Kb
HTML Download (Zip) - 223.1Kb


MySQL Utilities 1.6 Manual  /  ...  /  How do you purge obsolete binary log files safely?

3.1.2 How do you purge obsolete binary log files safely?

At some point MySQL servers may generate a significant number of binary log files that may consume considerable hard drive space and may be a good reason a user might want to delete them. In a replication scenario, it is necessary to know which binary log files can be deleted without breaking replication (you can safely archive or delete those that are not in use), since replication relies on the existence of the binary log files.

Users need to check each slave to determine the latest master binary log file and position (for anonymous replication). This information is then used to determine which of the binary logs on the master may be removed. That is, which files have been read by all of the slaves. If GTIDs are enabled, we would need to know the list of GTIDs read on the slave in order to determine which binary log files are not in use.

Objectives

The goal is to purge all unnecessary binary log files on the MySQL server in a replication topology.

Another important goal is to automate the operation. Executing this task manually can be tedious and error prone, since it requires getting data from and verifying each slave. The more slaves, the more complicated this becomes.

Fortunately, the mysqlbinlogpurge utility can help us to perform this task in an easy and safe manner by determining all the unnecessary files automatically on each slave and purging them on the master.

Let's assume that a master server is currently running on port 13001 and a few slaves are connected.

Example Execution

  1. Run the mysqlbinlogpurge utility specifying the master connection string using the --master option and either the --slaves to indicate each slave connection string, or the --discover-slaves-login to query the master for all registered slaves and use the user name and password specified to connect and determine the binary log files that can be purged.

    shell> mysqlbinlogpurge --master=root:root@localhost:13001 \
              --discover-slaves-login=rpl:rpl
    # Discovering slaves for master at localhost:13001
    # Discovering slave at localhost:13002
    # Found slave: localhost:13002
    # Discovering slave at localhost:13003
    # Found slave: localhost:13003
    # Discovering slave at localhost:13004
    # Found slave: localhost:13004
    # Latest binlog file replicated by all slaves: mysql-bin.000005
    # Purging binary logs prior to 'mysql-bin.000006'

Discussion

The previous example illustrates how to purge the binary log files from a master in a replication scenario.

We used the --master option for the master server but for the slaves, we provided the option --discover-slaves-login and the utility used the specified information to determinate the available binary log files on the server and for each slave verified the latest binary log file that has been loaded, finally purges the latest binary log that is not required for any of the slaves. if want

In example, all binary log files that the utility determinate that were not required by any of the slaves were purged, but the option --binlog can be used to specify the first binary log file to keep (not to purge) from the not required binary log files. The binary log files that remain on the master are from the indicated file to the current active binary log file.

As we can see in the example, the mysqlbinlogmove utility displays latest binary log file replicated by all slaves, and if you want the utility to display the current binary log file being read by the I/O thread, we can use the --verbose option.

Permissions Required

The user requires the SUPER and REPLICATION SLAVE privileges to purge the binary log files.

Tips and Tricks

The --dry-run option can be used to display only the latest binary log file matched by all the slaves without actually purge the binary log files. Use it along with -vv to display even more information such as the status of the SQL and I/O threads of each slave.


User Comments
Sign Up Login You must be logged in to post a comment.