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.

I'm using the MySQL 5.6 Master/Slave replication, with Delay set for 2 hours.

My questions are :

Q1. What is the proper way to cancel the Delay at the slave - i.e. "roll forward" all the changes executed at the Master ?

Q2. Let's say that the Master became totally unavailable. What is the proper way to cancel the Delay and "roll forward" the Slave so, that it will apply all changes from Master ?

I've tried to : - stop slave ; - change master to master_delay = 0; - start slave ;

(At this moment - the Master is still unavailable, and Slave IO thread status is Connecting.) After the listed above steps - the relay log bin files are deleted at the Slave host, Delay value becomes 0, but the changes from the Master are NOT applied...

If anybody could please provide some useful tips - i'll very appreciate.

Best regards, Avi Vainshtein

share|improve this question

1 Answer 1

From the documentation...

CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay log files are kept; the relay_log_purge global variable is set silently to 0.

-- http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html

SHOW SLAVE STATUS will give you the current Relay_Log_File and Relay_Log_Pos even if the slave is stopped... so after looking up those values:

CHANGE MASTER TO MASTER_DELAY = 0, RELAY_LOG_FILE = 'xxxxx-relay-bin.######', RELAY_LOG_POS = ######;
START SLAVE;

Since delayed replication only delays playback from the relay log -- not the receiving of events from the master -- your relay log should have everything up until "now" (or whenever the master was actually lost), so this should bring your slave current.


One thing, though...

the relay_log_purge global variable is set silently to 0.

I'm not so sure about this behavior. I tested the above on 5.6.10 the variable did not change from 1 to 0. Also, there's no mention of this in the changelogs for 5.6.11 or 5.6.12. You may want to check this global variable before and after you issue the CHANGE MASTER TO and re-set it to ON if it does actually toggle.


Update: the source code, in sql/rpl_slave.cc, clarifies the confusion in the documentation.

It preserves the value of the global variable relay_log_purge.

my_bool save_relay_log_purge= relay_log_purge;

If either of these conditions are met...

if (lex_mi->relay_log_name)
if (lex_mi->relay_log_pos)

...relay_log_purge is set to 0 until the CHANGE MASTER TO is finished, at which point it gets set back to its previous value.

relay_log_purge= save_relay_log_purge;

So the global varible is silently set to 0, but then gets set back again to whatever it was before.

share|improve this answer
    
Michael, Many thanks for the answer - it did worked as you described. I can add that : - the value of relay_log_purge had never changed during all the steps, remained ON - the existing relay bin logs have been deleted only after the slave's IO thread has been connected to Master. Many thanks again and best regards. –  user25827 Jul 10 '13 at 10:34

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.