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.0Kb
HTML Download (Zip) - 221.2Kb


MySQL Utilities 1.6 Manual  /  Manual Pages  /  mysqlbinlogpurge — Binary log purge utility

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:

  • --version

    Show the program's version number.

  • --help

    Display the help message.

  • --server=server_connection

    Connection information for the server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --ssl

    Specifies if the server connection requires SSL. If an encrypted connection cannot be established, the connection attempt fails. By default set to 0, indicating that SSL is not required.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL certificate authorities.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --binlog=binlog

    Binary log filename to not to purge. All the binary log files prior to the specified file are removed.

  • --dry-run

    Run mysqlbinlogpurge without purging any binary log files, instead displaying a list of the unused binary log files which would be purged.

  • --discover-slaves-login=slave-login

    Supply a user name and password, in the form user[:passwd] or login-path, used for discovering slaves and relay slaves in the replication topology. For example, --discover=joe:secret uses 'joe' as the user name and 'secret' as the password for attempting to log in to each discovered slave.

  • --slaves=slave connections

    Connection information for slave servers. List multiple slaves in a comma-separated list. The list is evaluated literally, where each server is considered a slave of the master listed. Thus, all servers specified in this option must be slaves of the current master.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --master=connection

    Connection information for the master server.

    To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

    • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

    • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

    • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

  • --verbose, -v

    Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v is verbose, -vv is more verbose, -vvv is debug level.

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.


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