We are very happy to introduce a new MySQL utility named “mysqlbinlogmove“, which is used to relocate binary log files. This utility is one of two new utilities included in MySQL Utilities release-1.6.0 Alpha. The other utility is “mysqlgrants“, which is used to display the privileges (grants) of database objects.
Note: I use “binary log” to refer to both “kinds” of binary log files (binlog and relay log files) in general, and use “binlog” to refer specifically to those that are not “relay log” files.
The mysqlbinlogmove utility allows you to move binary log files to a new location taking care of correctly updating the respective index file for you. This utility can be very useful if you want to change the location to store the binlog file and you want to move all of the binary log files. It is also handy to archive older binary log files to a new location thereby saving disk space in the server’s partition.
Here is a summary of the main features of the mysqlbinlogmove utility:
- Moves binary log files to a specified source directory, allowing to relocate all files to a new location. For example, with the server stopped before changing the –log-bin option.
- Moves binary log files using the server connection specified, allowing to easily archive files no longer in use based on the server settings (e.g., log_bin_basename, log_bin_index, relay_log_basename, relay_log_index).
- Allows you to specify the type of files to move, binlog files (default), relay log files, or both.
- Provides options to restrict the files to be move based on their sequence number or modified date.
The mysqlbinlogmove utility must be executed on the local server and it requires read and write access to the binary logs and index files, as well as the destination directory to move the files.
Here is an example of the execution of the utility to move all binlog files from a stopped server, simply specifying the source binlog directory and target destination:
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
Now, let’s look at another example to move all binary log files modified two days ago from a running slave, specifying the server connection instead of the source directory:
shell> mysqlbinlogmove --server=user:pass@localhost:3311 --log-type=all
# 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...
In this case, the –log-type option with the value ‘all’ was used to indicate that both binlog and relay log files will be moved, and the –modified-before option to restrict the files by their modified date.
In this example, it is also possible to observe the flush of binary logs. This operation is required in order to refresh the server’s data. The flush operation can be skipped with the –skip-flush-binlogs options.
Do you want to know more ?
Try it now and send us your feedback
MySQL Utilities release-1.6.0 alpha is available for download from the following links.
The documentation of MySQL Utilities can be obtained from the following link:
- Community users: http://bugs.mysql.com (MySQL Workbench: Utilities);
- Fabric, Sharding, HA, Utilities Forum: http://forums.mysql.com/list.php?144
Meet us at MySQL Central @ Oracle OpenWorld
Do you want to get your hands dirty and ask your questions directly to the MySQL Utilities team?
Participate in the Hands-on Lab session “DevOps Made Easy with MySQL Utilities” at the 2014 MySQL Central @ OpenWorld conference. See this link for details about the session:
MySQL Community Reception at Oracle OpenWorld: