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.5Kb
HTML Download (Zip) - 221.8Kb


MySQL Utilities 1.6 Manual  /  Manual Pages  /  mysqlbinlogmove — Binary log relocate utility

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:

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

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

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.


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