From a practical point of view, the use of this utility is recommended before you change the binary log base directory to move all binary log files to the target location, avoiding errors on the server when started with the new --log-bin location. It is also useful to archive older binary log files to a different location, in order to save disk space in the current partition.

Note

In order to relocate all binary log files, the MySQL server must be stopped. This requirement is not needed if only some of binary log files are intended to be moved.

The user must provide the destination directory to move the binary log files as an argument and the server connection parameters with the --server option or the source location of the binary log files using the option --binlog-dir. When the --server option is used the utility will determine the binary logs basename and index file location from the server (depending on its version) and all binary log files will be moved except the ones currently in use (with the higher sequence number). In order to move all binary logs the the --binlog-dir option must be used, requiring the MySQL server to be stopped.

By default, the utility only moves binary log files. To move relay log files or both, the user must use the --log-type option with the desired value.

When the server --server is used by default binary logs are flushed at the end of the move operation, in order to reload the binary logs data (cache) on the server. Users can skip this step using the --skip-flush-binlogs option.

The utility always attempts to determine the necessary information (base filename, binary logs and index location) based on the available server's data or the default values. Nevertheless, custom values might be used and some variables might not be available for older server versions or simply the server connection might not provided. If custom file names are used, the user can specify them using the options --bin-log-index, --bin-log-basename, --relay-log-index, and --relay-log-basename, respectively for binary log and relay log files.

By default, all of the binary log files found are moved (except the ones currently in use if the --server option is used). The --sequence option can be used to restrict the files to move based on their sequence number. It is also possible to filter the files to move based on their modification date using the --modified-before option.

The utility displays the list of binary files that are moved. Users can also use the --verbose option to see additional information when the utility executes (e.g., used values for server variables).

Note

This utility was added in MySQL Utilities 1.6.0.

• --binlog-dir=<binlog_dir>

Source directory (full path) for the binary log files to move.

• --bin-log-basename=<binlog_basename>

Basename for the binary log files. If not available, it is assumed to be any name ended with '-bin'.

• --bin-log-index=<binlog_index>

Location (full path) of the binary log index file. If not specified, it is assumed to be located in the binary log directory.

• --help

Display a help message and exit.

• --license

Display license information and exit.

• --log-type=<log_type>

Type of the binary log files to move, i.e. binary log or relay log files. Supported values: 'bin' for binary log files, 'relay' for relay log files, 'all' for both binary log and relay log files.

Default = bin.

• --modified-before=<modified_before>

Specify the datetime or number of days to move binary log files with a modified date prior to the specified value. Accepts a datetime in the format yyyy-mm-ddThh:mm:ss or yyyy-mm-dd, or an non-negative integer indicating the number of elapsed days.

• --relay-log-basename=<relay_log_basename>

Basename for the relay log files. If not available, it is assumed to be any name ended with '-relay-bin'.

• --relay-log-index=<relay_log_index>

Location (full path) of the relay log index file. If not specified, it is assumed to be located in the binary log directory.

• --sequence=<sequence_number_list>

Comma-separated list of non-negative sequence integers or intervals to move binary files with a matching sequence number. Specified sequence number intervals are closed (i.e., include both endpoints) and defined simply using a dash between its endpoints. For Example: 3,5-12,16,21.

• --server=<server_connection>

Connection information for the server.

To connect to a server, it is necessary to specify connection parameters such as 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>]

• --skip-flush-binlogs

Skip the binary log flush operation to refresh server's internal information after moving the binary log files.

• --ssl-ca

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

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

• --ssl

Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

• --verbose, -v

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

• --version

Display version information and exit.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This will allow the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf). LIMITATIONS.PP This utility does not support remote access to binary log files and must be executed on the local server. EXAMPLES.PP Move available binary log files from a running server:

shell> mysqlbinlogmove --server=user:pass@localhost:3310 \
          /archive/binlog_dir
#
# Moving bin-log files...
# - server-bin.000001
# - server-bin.000002
# - server-bin.000003
# - server-bin.000004
# - server-bin.000005
#
# Flushing binary logs...
#
#...done.
#

Move all binary log files from a stopped server specifying the source binary log directory:

shell> mysqlbinlogmove --binlog-dir=/server/data \
          /new/binlog_dir
#
# Moving bin-log files...
# - server-bin.000001
# - server-bin.000002
# - server-bin.000003
# - server-bin.000004
# - server-bin.000005
# - server-bin.000006
#
#...done.
#

Move available relay log files from a running slave:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=relay /archive/slave/binlog_dir
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
# - slave-relay-bin.000011
# - slave-relay-bin.000012
# - slave-relay-bin.000013
# - slave-relay-bin.000014
# - slave-relay-bin.000015
# - slave-relay-bin.000016
#
# Flushing relay logs...
#
#...done.
#

Move available binary log and relay log files from a running slave skipping the flush step:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --skip-flush-binlogs \
          /archive/slave/binlog_dir
#
# Moving bin-log files...
# - slave-bin.000001
# - slave-bin.000002
# - slave-bin.000003
# - slave-bin.000004
# - slave-bin.000005
#
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
# - slave-relay-bin.000011
# - slave-relay-bin.000012
# - slave-relay-bin.000013
# - slave-relay-bin.000014
# - slave-relay-bin.000015
# - slave-relay-bin.000016
#
#...done.
#

Move available binary log files from a running slave matching the specified sequence numbers:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --sequence=2,4-7,11,13 \
          /archive/slave/binlog_dir
#
# Applying sequence filter to bin-log files...
#
# Moving bin-log files...
# - slave-bin.000002
# - slave-bin.000004
# - slave-bin.000005
# - slave-bin.000006
#
# Flushing binary logs...
#
#
# Applying sequence filter to relay-log files...
#
# Moving relay-log files...
# - slave-relay-bin.000002
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000011
# - slave-relay-bin.000013
#
# Flushing relay logs...
#
#...done.
#

Move available binary log files modified two days ago from a running slave:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --modified-before=2 \
          /archive/slave/binlog_dir
#
# Applying modified date filter to bin-log files...
#
# Moving bin-log files...
# - slave-bin.000001
# - slave-bin.000002
# - slave-bin.000003
#
# Flushing binary logs...
#
#
# Applying modified date filter to relay-log files...
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
#
# Flushing relay logs...
#
#...done.

Move available binary log files modified prior to the specified date from a running slave:

shell> mysqlbinlogmove --server=user:pass@localhost:3311 \
          --log-type=all --modified-before=2014-08-31 \
          /archive/slave/binlog_dir
#
# Applying modified date filter to bin-log files...
#
# Moving bin-log files...
# - slave-bin.000001
# - slave-bin.000002
# - slave-bin.000003
#
# Flushing binary logs...
#
#
# Applying modified date filter to relay-log files...
#
# Moving relay-log files...
# - slave-relay-bin.000001
# - slave-relay-bin.000002
# - slave-relay-bin.000003
# - slave-relay-bin.000004
# - slave-relay-bin.000005
# - slave-relay-bin.000006
# - slave-relay-bin.000007
# - slave-relay-bin.000008
# - slave-relay-bin.000009
# - slave-relay-bin.000010
#
# Flushing relay logs...
#
#...done.

PERMISSIONS REQUIRED.PP By default, the user used to connect to the server must have permissions to flush the binary logs, more precisely the RELOAD privilege is required, except if the flush step is skipped.

Additionally, the system user used to execute the utility must have read and write access to the location of the binary logs and index files, as well as the destination directory to move the files.