How to Relocate the MySQL Binary Logs to New Location

The binary logs are created in the data directory by default in MySQL Server.

To have the binary logs in the data directory is not always the optimal location however. This could be for a range of reasons including:

• There is not enough space on the drive/partition to manage all the binary logs as well as data.
• The workload on the server performs better when the logs are on a separate drive to the data.
• Archiving the data.
• Administration and management of logs on large setups.

How to Move the Binary Logs
The binary logs are made up of two essential file types:

  1)  The log files - these record all the changes to data on the system
  2)  The index file - this file tracks the active log files on the system

Moving the log files to a new location involves the following steps:

1. Stop the mysqld service so no changes are being recorded.

# service mysqld stop

NOTE: Make sure that the process should be killed.

Eg: # ps -ef | grep -i mysql

2. Copy the log files including the .index file to new location.
# cp hostname-bin.log* /path/to/binlogs
# cp hostname-bin.index /path/to/binlogs

3. Do Changes in the /etc/my.cnf file.

[mysqld]
log-bin=/path/to/newdirecory/server-name-bin
log-bin-index=/path/to/newdirectory/server-name-bin.index

4. The logs will be created in the new directory as specified, but existing logs are still pointing to old path in the .index file. To fix the old logs we will need to edit the .index file and update the paths to new location.

Eg: # cat hostname-bin.index

./hostname-bin.000031
./hostname-bin.000032
./hostname-bin.000033

5. Start the mysql service.

# service mysqld start

6. Show Binary logs

mysql> SHOW BINARY LOGS;
+---------------------+------------+
| Log_name            | File_size  |
+---------------------+------------+
| hostname-bin.000008 |    1252142 |
| hostname-bin.000009 |  481551017 |
| hostname-bin.000010 |        126 |
| hostname-bin.000011 | 1074151674 |
| hostname-bin.000012 |  872672881 |
| hostname-bin.000013 |        126 |
| hostname-bin.000014 |        107 |
+---------------------+------------+

7. Once got confirmed remove the bin log files from data directory.

# cd /data/dir/
# rm hostnamep-bin.*