Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Problem

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.

Issues

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.

Question

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.

share|improve this question
1  
+1 for such a headbanging question –  RolandoMySQLDBA Apr 23 '13 at 21:04

1 Answer 1

up vote 2 down vote accepted

DISCLAIMER : Not a user of mha4mysql.

SUGGESTION #1

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
    • STOP SLAVE;
    • CHANGE MASTER TO MASTER_HOST='...',MASTER_LOG_FILE='...',MASTER_LOG_POS=...;
    • START SLAVE;

All manual steps. I'm sorry.

SUGGESTION #2

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

SUGGESTION #3

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.

share|improve this answer
    
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 '13 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. –  RolandoMySQLDBA Apr 23 '13 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 '13 at 21:02
    
OK since suggestions 2 and 3 sound plausible, I'll leave my answer available. –  RolandoMySQLDBA Apr 23 '13 at 21:03
    
Accepted, as we agreed on trying out solution #3. Will keep you posted :) –  favoretti Apr 24 '13 at 14:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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