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.
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.
Run the mysqlbinlogpurge utility specifying the master connection string using the
--masteroption and either the
--slavesto indicate each slave connection string, or the
--discover-slaves-loginto 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'
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
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
The user requires the SUPER and REPLICATION SLAVE privileges to purge the binary log files.
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.