Get missed transactions from slave

Question

I'm trying to write a disaster recovery plan, I have one way replication with 1 master(A) and 3 slaves(B,C,D)

     A 
    /|\
   / | \
  B  C  D

Let's say my master crash, and I loose all data on that server, I still have 3 slaves, and I'll promote the most up-to-date to be my new master, let say slave B is up to date and C and D are not, at the point I promote B to master my slaves have the follow result for SHOW SLAVE STATUS\G:

slaveB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 1476884

slaveC> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 78684

slaveD> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000001
        Exec_Master_Log_Pos: 746848

In order to promote B to master, I want to apply all missing transactions on C and D, so before B become the new master and start to receive queries from applications, I've all slaves on a consistent state, how can I find the missed transactions from C and D on binary logs from B (I've the --log-slave-updates option enabled on all server).

For legacy issues, the envoirement use MySQL 5.0

Answer 1

The thing you need to do to change, what I will call, the reference point for replication.

Since Servers B,C, and D have binlogs enabled, this will be handy.

You gave

slaveB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 1476884

slaveC> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 78684

Let's sync up ServerC to be a Slave to ServerB

STEP 01 : Stop all writes to ServerC

STOP SLAVE;

STEP 02 : On ServerC, run SHOW MASTER STATUS;

Let's say the output was master-bin.000003 position 12345678

STEP 03 : On ServerC, run

mysqlbinlog master-bin.000003 > latest-binlog.sql

STEP 04 : Locate position from STEP 03 and show 3 lines before and after

grep -C 3 12345678 latest-binlog.sql

Let's say the timestamp was "2013-09-03 01:23:45"

STEP 05 : Find the timestamp of that position in the binary logs of ServerB

Let's say the binary log on ServerB is master-bin.000004

STEP 06 : Dump position matching timestamp inside ServerB's binary log master-bin.000004

SQLFILE=dumpstuff.sql
BINLOG="master-bin.000004"
DT2="2013-09-03 01:23:45"
DT1="2013-09-03 01:00:00"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" > ${SQLFILE}

STEP 07 : Locate the timestamp inside SQLFILE and find the position

Let's say the position is 87654321

STEP 08 : Connect SlaveC to SlaveB

On SlaveC, run this

STOP SLAVE;
CHANGE MASTER TO master_log_file='master-bin.000004',master_log_pos=87654321;
START SLAVE;

That's it !!!

CAVEAT #1

Repeats all these same steps to sync ServerD to ServerB

CAVEAT #2

If you have done these steps and still don't trust the data, please download Percona Tools and use pt-table-checksum to verify each Slave against the new Master. If there are differences, you can run pt-table-sync to generate the SQL to repair the differences.

UPDATE 2013-09-05 12:28 EDT

You just made this comment

On Step 2,3 and 4 , I don't have access to master binlog, I guess the way to get the latest applied transaction on slaveC is to extract it (using the Relay_Master_Log_File and Exec_Master_Log_Pos from show slave status) from slaveC relay log. Am I right? Steps 5 to 8 seems to be correct.

You can use mysqlbinlog remotely using --read-from-remote-server. Besides, you do not go back to the old Master, ServerA. You are doing these things among the Slaves, making ServerB the new Master. If you are using some manged hosting company, those admins need to be involved in this process. At the very least, they could provide some SSH tunnel access to permit you to access the Linux box and all necessary folders permissions. No MySQL authentication is needed.

Answer 2

May I know if there is a reason why we need to find from binary logs instead of data folder sync (rsync)? Since the application have not started writing to B, I think, we have the luxury to even stop the MySQL server and run a clean rsync (data is too very big, maybe?). I have first hand experience following this method and it worked like a charm. When we read binlogs (read human intervention in counting the position), we may end up with a wrong position and in worst case, we'll be dealing with duplicate entries.