MySQL Utilities

5.4 mysqlbinlogpurge — Binary log purge utility

This utility enables you to safely purge (delete) 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.

Note

In order to determine which binary logs can be purged, mysqlbinlogpurge connects to the master. If the specified server is not the active master, mysqlbinlogpurge cannot determine which binary logs are still needed by the slaves.

You must provide the master's connection parameters with the --master option and each slave's connection parameters with the --slaves option. Alternatively, use the --discover-slaves-login option configured with the user name and password to connect to the slaves. In case the server is not a master, you must provide the connection parameters with the --server option.

The discover slaves option requires all slaves use the --report-host and --report-port server startup variables with the correct hostname and port. If these are missing or report the incorrect information, the slave may not be detected and thus not included in the operation of the utility. The discover slaves option ignores any slaves to which it cannot connect.

mysqlbinlogpurge attempts to determine the binary logs to purge by logging in to each server. If a slave is not actively participating in a replication topology, mysqlbinlogpurge does not purge any logs.

By default, mysqlbinlogpurge purges all the binary log files that are not in use. Use the --binlog option to override this behavior and configure the first binary log file to not purge.

mysqlbinlogpurge displays the list of binary log files that were purged. Use the --verbose option to see a list of the remaining available binary log files on the server and to display additional information when mysqlbinlogpurge executes, such as status of the I/O and SQL threads of each slave.

OPTIONS

mysqlbinlogpurge provides the following command-line options:

NOTES

If the server specified using the --server option is a master server and there are slaves connected, mysqlbinlogpurge displays an error and does not purge the binary logs that match the criteria specified.

LIMITATIONS

mysqlbinlogpurge cannot verify slaves that are finds a slave which is not actively replicating from the master.

EXAMPLES

Purge all binary log files not in use from a master, specifying the slaves to check:

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

Purge all binary log files not in use prior to a specific binary log file:

shell> mysqlbinlogpurge --master=root:root@localhost:3310 \
          --slaves=root:root@localhost:3311,root:root@localhost:3312,root:root@localhost:3313 \
          --binlog=mysql-bin.000027 -v
# Checking user permission to purge binary logs...
#
# Master active binlog file: mysql-bin.000031
# Checking slave: localhost@3311
# I/O thread is currently reading: mysql-bin.000031
# Checking slave: localhost@3312
# I/O thread is currently reading: mysql-bin.000031
# Checking slave: localhost@3313
# I/O thread is currently reading: mysql-bin.000031
# Range of binlog files available: from mysql-bin.000023 to mysql-bin.000031
# Latest binlog file replicated by all slaves: mysql-bin.000030
# Purging binary logs prior to 'mysql-bin.000027'
# Range of binlog files available: from mysql-bin.000027 to mysql-bin.000031
# Range of binlog files purged: from mysql-bin.000023 to mysql-bin.000026

Display a query statement you could use to manually purge all binary log files not in use from a server, without actually purging them by using the --dry-run option:

shell> mysqlbinlogpurge --server=root:root@localhost:3310 --dry-run
# To manually purge purge the binary logs Execute the following query:
PURGE BINARY LOGS TO 'mysql-bin.000004'

PERMISSIONS REQUIRED

By default, the user name you specified to connect to the server must have SUPER and REPLICATION SLAVE permissions to be able to purge the binary logs.