We have the following scenario:

mysql setup

Set of slaves replicate from the master, separate standby master (a.k.a slave with binlogs) replicates from the same master; lag slave, powered by pt-slave-delay, replicates from the same source.

We also utilise mha4mysql to switch slaves from one master to another in case of need.

All would be dandy, if not for lag slaves.


Let's say lag slave is running 12 hours behind master. Switch lag slave from Master to Standby Master in such a way that failover occurs immediately (i.e. CHANGE MASTER is executed on lag slave the same time as on regular slaves) and that lag slave keeps lagging 12 hours.


While I know that slave keeps track of master binlog position in its own relay log, I can calculate master position to 12 hours in the past, however, as far as I understand it Standby Master (aka slave with binlogs) has no relation between own relay logs and own binlogs.


Is there a reliable way to calculate position of the Standby Master we need to switch to (discarding the rest of relay logs on lag slave, obviously) and restart replication from 12 hours behind, but on the new master?

NOTE we are running MySQL 5.1, so 5.5 (or 5.6 with GTIDs) aren't a solution for now unfortunately.

Thank you.


1 Answer 1


DISCLAIMER : Not a user of mha4mysql.


Hang the Lag Slave off from the Standby Server. How?

  • Step 01: Stop all Writes to the Master
  • Step 02: Run SHOW MASTER STATUS; on the StandByMaster
  • Step 03: Run mysqlbinlog against the binlog from Step 02
  • Step 04: Acquire timestamp from given position
  • Step 05: Subtract 43200 sec (12 hrs) from the Timestamp acquired
  • Step 06: Locate position just before timestamp acquired
  • Step 07: Using StandByMaster Host IP, Log File and Position, run this on Lag Slave

All manual steps. I'm sorry.


Setup the Lag Slave under one of the Slaves PERMANENTLY. You'll never have worry about moving the Lag Slave between Masters again.


Setup the Lag Slave under a Blackhole Slave. You'll never have worry about moving the Lag Slave between Masters again.

I hope these suggestions helped or at least got you thinking.

  • Suggestion #2 is so obvious it hurts that we didn't think of that one ourselves. Although as to Suggestion #1 - what if we have about 5000 qps on the servers? Timestamp is not with microseconds, how would you ensure reliability of the position?
    – favoretti
    Apr 23, 2013 at 20:55
  • This is just a shot in the dark, bur from the SHOW SLAVE STATUS\G of the Most Up-to-Date Slave after running STOP SLAVE;. When you run SHOW SLAVE STATUS\G, use Relay_Master_Log_File and Exec_Master_Log_Pos. They represent the position of the Last Binlog Event that was Executed on the Slave. Whatever timestamp you find, go locate all the occurrences of that timestamp and use the first one. Get the position from there. Apr 23, 2013 at 20:58
  • Heh, that might be an option, although I bet it's a bit far from being really reliable. Solution #3, however, seems also very appealing. Thank you very much for a different point of view :) I'd appreciate a lot if you kept this answer hanging around for a day or two :)
    – favoretti
    Apr 23, 2013 at 21:02
  • OK since suggestions 2 and 3 sound plausible, I'll leave my answer available. Apr 23, 2013 at 21:03
  • Accepted, as we agreed on trying out solution #3. Will keep you posted :)
    – favoretti
    Apr 24, 2013 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.