Oracle Blog

Eclectic gatherings about data

Tuesday Jun 23, 2009

I came across a situation recently where I was asked if it was possible to edit a binary log to remove a part of it to restore onto a slave server. Now the choice of doing something like a hexedit did not seem appealing, and the more experienced might suggest that it is simply a matter of using mysqlbinlog with the --start-position and/or --stop-position options. However, the problem had arisen that required the binary log to played through the replication process onto the slave based on specific options in MySQL cluster, so using an SQL dump from the binary log was of no use.

Initially this may seem like a daunting task where you will have to find some specialist tool or delve into the deep recesses of the binary log format, but a much simpler solution was found. The replication process allows the slave to be started up to a specific point in the log files. The command is the START SLAVE UNTIL... statement as seen in the manual at:

http://dev.mysql.com/doc/refman/5.1/en/start-slave.html

This offers the ability to start up the slave or the io thread to some particular part of a binary log that is specified. Whether you use the io thread to get the specific details from the master binary log, or you use the SQL thread to limit the SQL statements that are executed on the slave.

All seems well with this approach until you find out that you need a section removed from the binary log not the end of the log. The START SLAVE UNTIL statement only allows for an ending point, there is no equivalent START SLAVE FROM... statement to specify a starting point. So how can we utilise the START SLAVE UNTIL... command in the replication process to allow us to restore a binary log, leaving out a window of the log statements?

The solution is to combine the START SLAVE UNTIL statement with the CHANGE MASTER TO statement to allow us to define a starting and stopping point in the replication process. Here is a simple outline of how it would work, assuming the master is still replicating and the slave is currently stopped, or using --skip-slave-start:

Start the slave using START SLAVE UNTIL MASTER_LOG_FILE='binary.00035',MASTER_LOG_POS=2500;

This will give us the initial replication of statements that we want up to the start of the window to be removed from the log.

The replication then stops at this position, we can then RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='binary.00035',MASTER_LOG_POS=3500;

This defines the starting point for our next section to replicate, or in another term it is the end of the window being removed from the log file.

We then start the replication process again, either with START SLAVE; if there is no other window, or we can continue the process all over again by using the START SLAVE UNTIL.. again.

This simple process will allow us to restore sections of the binary log without having to resort to any specialist tools or hexeditors of any sort. Again, this is not usually required when you can simply use the mysqlbinlog command with the --start-position and --stop-position options, but if you ever find yourself in the situation where you have to use replication and restore only part of the binary log, this can be an option.