The MySQL Utilities team is happy to introduce a new MySQL utility named  ‘mysqlbinlogpurge‘, which allows users to easily and safely purge binary logs on a master by determining the binary logs that are obsolete. This utility is included in MySQL Utilities
release-1.6.1 Alpha release along with ‘mysqlslavetrx‘ and ‘mysqlbinlogrotate‘.

This utility enables you to purge binary logs by ensuring that any files which are in use or required by any of the slaves in a replication topology are not deleted. This is achieved by
checking which binary logs have been read on each slave. This determines the minimal set of binary log files that can be purged.

The ‘mysqlbinlogpurge’ Utility automates the steps described in:

  http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html

to manually verify which binary logs are not required by any of the slaves.

The ‘mysqlbinlogpurge’ Utility can help you to quickly free some space on the master without increasing space for your current replication topology.

————-

By default, ‘mysqlbinlogpurge’ purges all of the the binary log files that are not in use. You can use the –binlog option to override this behaviour and configure the first binary log file to omit from the purge list. The ‘mysqlbinlogpurge’ is safe to use because it only purges the binary logs if all of the slaves are actively participating in a replication topology. You can use the –dry-run option to display a list of binary log files to be purged.

————

The ‘mysqlbinlogpurge’ utility requires the SUPER and REPLICATION SLAVE permissions for the connected user in order to be able to purge the binary logs.

——–

The following is an example of a purge operation of all the binary log files not in use from a master, specifying the slaves to check and displaying additional information in the process.

shell> mysqlbinlogpurge –master=root:root@localhost:3310 \
–slaves=root:root@localhost:3311,root:root@localhost:3312,root:root@localhost:3313 \
-vv
exec_util command=python -u ../scripts/mysqlbinlogpurge.py –master=root:root@localhost:3310 –slaves=root:root@localhost:3311,root:root@localhost:33
12,root:root@localhost:3313 -vv
# Checking user permission to purge binary logs…
#
# Master active binlog file: mysql-bin.000021
# Checking slave: localhost@3311
# I/O thread is currently reading: mysql-bin.000021
# File position of the I/O thread: 120
# Master binlog file with last event executed by the SQL thread: mysql-bin.000021
# I/O thread running: Yes
# SQL thread running: Yes
# Checking slave: localhost@3312
# I/O thread is currently reading: mysql-bin.000021
# File position of the I/O thread: 120
# Master binlog file with last event executed by the SQL thread: mysql-bin.000021
# I/O thread running: Yes
# SQL thread running: Yes
# Checking slave: localhost@3313
# I/O thread is currently reading: mysql-bin.000021
# File position of the I/O thread: 120
# Master binlog file with last event executed by the SQL thread: mysql-bin.000021
# I/O thread running: Yes
# SQL thread running: Yes
# Range of binlog files available: from mysql-bin.000016 to mysql-bin.000021
# Latest binlog file replicated by all slaves: mysql-bin.000020
# Latest not active binlog file: mysql-bin.000020
# Executing query PURGE BINARY LOGS TO ‘mysql-bin.000021’
# Binlog file available: mysql-bin.000021
# Range of binlog files purged: from mysql-bin.000016 to mysql-bin.000020

Upon completion, the utility will display additional information such as status of the I/O and SQL threads of each slave along with the list of binary logs purged and the remaining
binary logs available on the master.

MySQL Utilities release-1.6.1 alpha is available for download from the following links.

MySQL developers website: http://dev.mysql.com/downloads/tools/utilities/

Launchpad project:
https://launchpad.net/mysql-utilities

The documentation of MySQL Utilities can be obtained from the following link:

http://dev.mysql.com/doc/index-utils-fabric.html

Contributing Ideas:

– Community users:
http://bugs.mysql.com (MySQL Workbench: Utilities);

– Fabric, Sharding, HA, Utilities
Forum: http://forums.mysql.com/list.php?144