MySQL Utilities

5.3 mysqlbinlogmove — Binary log relocate utility

This utility allows binary logs to be relocated to a different location in a simple and easy way. In particular, it moves existing binary logs to a specified location and updates the necessary server files (i.e., binary log index files) with the new location.

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 binary log files that are not in use are 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 determines the binary logs base filename and index file location from the server (depending on its version) and all binary log files are moved except the ones currently in use (with the higher sequence number). In order to move all binary logs including those in use, the --binlog-dir option must be used, which requires the MySQL server to be stopped.

By default, the utility only moves binary log files. If the server is performing the role of a slave or if you want to move both binary and relay log files, you must use the --log-type option with the desired value.

When the server --server is used, binary logs are flushed at the end of the move operation to refresh the cache. 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 filenames 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 that are not in use are moved (except when 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.

OPTIONS

mysqlbinlogmove accepts the following command-line options:

NOTES

By default, binary logs are flushed after moving the files when the --server option is used. In particular, FLUSH BINARY LOGS is executed after moving all binary log files and FLUSH RELAY LOGS after moving all relay log files. This flush operation is required to refresh the binary log data on the server, otherwise errors might occur or inconsistent information might be displayed regarding the moved files (without restarting the server). For example, when executing the following statements: SHOW BINLOG EVENTS and SHOW BINARY LOGS. Nevertheless, the flush operation also closes and reopens the binary log files. See FLUSH Syntax, for more information about the FLUSH statement. Recall that the --skip-flush-binlogs option can be used to skip the flush operation.

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 permits 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

This utility does not support remote access to binary log files and must be executed on the local server.

EXAMPLES

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

By default, the user account 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 user account used to execute the utility must have read and write access to the location of the binary log and index files as well as the destination directory to move the files.