How To Change MySQL Binary Log File Location To Another Directory

By default, in most installation binary log files are located in MySQL default director “/var/lib/mysql/“.

Each data modification such as INSERT, UPDATE, DELETE and data definition such as ALTER, ADD, DROP statement that you perform in your server are record in log files. So each time when you make any these statements, you actually update both your data files and your log file. So there is high IO utilization that focused on a specific disk area. For better performance a common way is separate these files to two different disks.

Change MySQL Binary Log File Location To Another Directory

Follow the below steps to change MySQL binary log file location to another directory.

1. Edit the log-bin variable in my.cnf file located in /etc/my.cnf to log-bin=/new/directory/path/mysql-bin .

# vim /etc/my.cnf
......
.........
log-bin=/new/directory/path/mysql-bin
.....
.......

Save and close the file.

2. To minimize the number of moved files, purge as many files as you can ( PURGE BINLOG…).

3. Stop MySQL server.

# service mysqld stop

4. Now move the all mysql-bin file to new directory.

# mv /var/lib/mysql/mysql-bin.* /new/directory/path

5. Start MySQL server again.

# service mysqld start

That’s it.

Thanks:)