Mysql – Switch MySQL lag slave to new master and keep lagging (5.1)

binlogMySQLreplication

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.

Best Answer

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.

Related Question