At some point a user might want to change the location where the
binary log files are stored (by default in the
datadir). There are many
reasons for separating the binary log files from the database
data. These include fault tolerance, performance, and disk
management. For example, you may want to store the binary log
files on a different device.
You can use (or change) the
--log-bin startup option, but
simply changing this option is not enough and it is likely
result in errors when starting the MySQL server, especially for
replication scenarios. In fact, the existing binary log files
also need to be moved to the new location and/or the entries in
the respective index file updated in order to preserve any
replication topologies that rely on the binary log files.
Change the location for the binary log files on an existing MySQL server.
Executing this task manually can be tedious and error prone, since it requires existing binary log files to be moved to the new location and the binary log index file to be correctly updated. Fortunately, the mysqlbinlogmove utility can help us perform this task in an easy way, moving all files and automatically updating the respective index file entries appropriately.
Let's assume that a server is currently running and that it
was started with --log-bin=server-bin,
which means that the binary log files are created in the
datadir with the base
filename 'server-bin'. Let's also consider that the
datadir is /var/lib/mysql and
that the new target directory for the binary log files is
Stop the running MySQL server.
Start the mysqlbinlogmove utility and specify the source directory of the binary log files and the target directory.
shell> mysqlbinlogmove --binlog-dir=/var/lib/mysql \ /mysql/server/binlogs # # Moving bin-log files... # - server-bin.000001 # - server-bin.000002 # - server-bin.000003 # - server-bin.000004 # - server-bin.000005 # - server-bin.000006 # - server-bin.000007 # - server-bin.000008 # - server-bin.000009 # - server-bin.000010 # - server-bin.000011 # #...done. #
Restart the MySQL server with the new value for the
The above example illustrates how to change the binary log directory in an effortless way using the mysqlbinlogmove utility to move existing binary log files to the desired location.
option requires the restart of the MySQL server. Moreover, the
server also needs to be stopped in order to move all binary
log files correctly, otherwise an error might occur while
moving files currently in use by the server. In this case, to
relocate all available binary log files with the
mysqlbinlogmove utility, we simply need to
specify their source directory using the
and the target directory as an argument.
The binary log files are identified based on the default
filename format, i.e. with a base filename ending with '-bin'.
If a custom basename is used, not ending with '-bin', then the
option must be used to specify it.
In the above example, this option is not required because the
binary log basename 'server-bin' matches the default format.
Similarly, if a custom location or name is used for the binary
log index file, it must be specified using the
option. By default, the binary log index file is assumed to be
located in the specified source binary log directory and to
use the default filename format.
As we can see in the above example, the mysqlbinlogmove utility displays the list of all files that are moved. This helps to confirm that all files were relocated as expected.
The system user used to execute the utility must have read and write access to the source and destination directories in order to move the binary log files successfully.
By default, the utility only moves binary log files. Use the
--log-type option with
the value 'relay' to move relay log files, or 'all' to move
both binary log and relay log files.
The mysqlbinlogmove utility can also be
used to move some binary log files to a different location
while the server is still running. For example, suppose you
want to archive the binary log files or free some disk space
on the server's partition. In this case, the
--server option should
be used instead of the
like in the above example. The utility ensures that the binary
files that might be in use by the server (those with the
higher sequence number) are not moved.