Tuesday, August 14, 2007

Stopping the slave exactly at a specified binlog position

Catching up on some articles on the Planet MySQL feed, I just read the post by Dathan on how to promote a slave to be master by using MASTER_POS_WAIT(). The MASTER_POS_WAIT() is an excellent function that allows you to wait until the slave reaches a point at or after the given binlog position. Observe that after the statement issuing a MASTER_POS_WAIT() returns, the slave threads are still running, so this means that even if a STOP SLAVE is issued immediately after the statement with MASTER_POS_WAIT(), it is bound to move a little more before actually stopping. For Dathan's situation, this is not necessary, but wouldn't it be great if you could stop a slave at exactly the position that you want? Well, that is possible.

There is another command that does exactly what you want, and that is START SLAVE UNTIL. The problem with this command is that it can only be executed when the slave threads have stopped, but that is trivial to do by just issuing a STOP SLAVE. In other words, instead of doing:

SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4);
SLAVE STOP;
like Dathan suggests, do:
STOP SLAVE;
START SLAVE UNTIL
    MASTER_LOG_FILE='dbmaster1-bin.000002', MASTER_LOG_POS=4;
SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4);

1 comment:

crz said...

Yeah, that's a great way to perform a roll forward recovery from binary logs if you have made a snapshot at some point (and marked positions). Nice option for those who had their databases dropped by someone and want to restore them up to the exact point where it happened.