Ubuntu Manpage: mysqlbinlogpurge - Binary log purge utility

Provided by: mysql-utilities_1.6.4-1build1_all

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.

· 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.

· 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/.

AUTHOR

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

                                                                               MYSQLBINLOGPURGE()

NAME

SYNOPSIS

DESCRIPTION

COPYRIGHT

SEE ALSO

AUTHOR