MySQL: Relocating Slave Logs

On a master-slave configuration, MySQL creates binary logs on the master and events recorded in these files are read by the slave and stores them in relay logs. These log files are stored in the data directory by default. However, they can be relocated to another location for optimization purposes. 

We have a master-slave configuration set up on Linux and there was a need to move the log files to its own directory. With all the writing that MySQL is doing, it is best practice to move these log files out of the data directory. These are the files that I modified/copied/moved: 

•     <host-name>-relay-bin.XXXXXX
•     <host-name>-relay-bin.index
•     relay-log.info 

These files are in the data directory and we would like to move the <host-name>-relay-bin.* files to /var/log/mysql directory. These are the steps that I did:

1. Shutdown the slave. On my test bed, I ran the mysql.server command which comes with the MySQL installer.

shell> mysql.server stop

2. Add the following to your config file under the mysqld section, which is /etc/my.cnf. You might need root access to do this. My slave server name is server02.

relay-log=/var/log/mysql/server02-relay-bin

3. Move the relay files to the new location. My data directory is /var/lib/mysql.

shell> mv /var/lib/mysql/server02-relay-bin.* /var/log/mysql

4. Modify the entries in the /var/lib/mysql/relay-log.info to reflect the new directory. For example, the contents of my relay-log.info are:

./server02-relay-bin.000011

67884

server01-bin.000023

67735

9

All I did was change the first line to /var/log/mysql/server02-relay-bin.000011.

/var/log/mysql/server02-relay-bin.000011

67884

server01-bin.000023

67735

9

5. Modify the entries in the /var/log/mysql/server02-relay-bin.index (remember we've already moved these files). For example, if the contents of the server02-relay-bin.index are:

./server02-relay-bin.000010

./server02-relay-bin.000011

All I did was to change the (.) with /var/log/mysql:

/var/log/mysql/server02-relay-bin.000010

/var/log/mysql/server02-relay-bin.000011

6. Start the slave

shell> mysql.server start

At this point, the relay log files have been moved to the new location. Create a test table on the master and see if it replicates to the slave.