Scroll to navigation

MYSQLBINLOGPURGE() MYSQLBINLOGPURGE()

NAME

mysqlbinlogpurge - Binary log purge utility

SYNOPSIS

mysqlbinlogpurge [options] master slaves

DESCRIPTION

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.

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.PP 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.PP 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.PP mysqlbinlogpurge cannot verify slaves that are not actively replicating and will stop and show an error if it finds a slave which is not actively replicating from the master. EXAMPLES.PP 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.PP 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.

COPYRIGHT

Copyright © 2006, 2016, Oracle and/or its affiliates. All rights reserved.

This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.

SEE ALSO

For more information, please refer to the MySQL Utilities and Fabric documentation, which is available online at http://dev.mysql.com/doc/index-utils-fabric.html

AUTHOR

Oracle Corporation (http://dev.mysql.com/).