An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave must be "rolled forward" to provide all the latest data. The SHOW SLAVE…

Question

Options

• ASTOP SLAVE; SET GLOBAL master_delay=0; START SLAVE;
• BSTOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = 'hostname-relay-bin.00004', RELAY_LOG_POS = 1383;
• CSTOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;
• DSTOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; RELAY_LOG_FILE = 'hostname-relay-bin.00004', RELAY_LOG_POS = 1383;

• A
  6% (1)
• B
  6% (1)
• C
  69% (11)
• D
  19% (3)

Explanation

Option C works because when using delayed replication, the slave has already received all relay log events from the master - it just hasn't applied them yet due to the artificial delay. Setting MASTER_DELAY=0 via CHANGE MASTER TO removes that delay, and START SLAVE allows the SQL thread to immediately apply all buffered relay log events, rolling the slave forward to current.

Why the distractors fail:

• A is wrong because SET GLOBAL master_delay is not valid MySQL syntax - the delay is a replication parameter changed only via CHANGE MASTER TO.
• B is wrong on two counts: it omits START SLAVE, and explicitly re-specifying the current relay log position serves no purpose - it doesn't remove the delay, so the slave would still wait before applying events.
• D has a syntax error: the RELAY_LOG_FILE and RELAY_LOG_POS clauses appear after a semicolon, making them a separate malformed statement. All CHANGE MASTER TO options must appear in a single comma-separated statement.

Memory tip: Think "received ≠ applied" - in delayed replication the relay logs are already there, so you only need to unlock them by zeroing the delay (MASTER_DELAY=0), not reposition anything.

No community discussion yet for this question.