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


MySQL Utilities 1.6 Manual  /  ...  /  How do you change the location of the binary log files?

3.1.1 How do you change the location of the binary log files?

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.

Objectives

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 /mysql/server/binlogs.

Example Execution

  1. Stop the running MySQL server.

  2. 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.
    #

  3. Restart the MySQL server with the new value for the --log-bin option: --log-bin=/mysql/server/binlogs/server-bin.

Discussion

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.

Changing the --log-bin startup 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 --binlog-dir option 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 --bin-log-basename 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 --bin-log-index 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.

Permissions Required

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.

Tips and Tricks

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 --binlog-dir option 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.

The utility also provides options to filter the files to relocate based on their sequence number using the --sequence option, or their last modification date using the --modified-before option.


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