0

MySQL standby & chained replication

Having the current setup (not using GTID) (see link to image):

  • PRODUCTION NODES: MASTER (A) -> SLAVE (A1) + SLAVE (A2)
  • STANDBY NODES: -> SLAVE (B) (with log_slave_updates enabled) -> SLAVE (B1) + SLAVE (B2)

The standby nodes are meant to be promoted to the main production nodes if main MASTER (A) fails. So, if the MASTER (A) fails, we want to promote SLAVE (B) to master and nullify all (A) Nodes. So what we plan is the following commands for the this scenario:

  • MASTER (A) fails and cannot be recovered
  • SLAVE (B) execute: STOP SLAVE and RESET MASTER;
  • SLAVES (B1, B2) execute: STOP SLAVE, RESET SLAVE, START SLAVE;

Is this correct?

MGS
  • 1
  • 2

1 Answers1

0

In all the replication changes I have made, I have never used RESET. I don't know what it really does, or whether it is ever necessary.

I see no need to throw any commands at B or B1 and B2, other than to do whatever is needed to change the clients to see B as a Primary. (I assume you turned off readonly on B?

As for making use of A1 and A2, this is trickier. With GTID enabled, is should be possible to get them to figure out how to replicate from B. Without GTID, it might be expedient to clone B1 or B2 rather than figure out the binlog/relaylog positioning.

(In the future, dba.stackexchange.com would be a better site for questions like this one.)

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Thanks! Yes B would be set as super_read_only = 0 if Master (A) fails. All SLAVES (A) will be nullified and will become part of the future standby cluster (C) – MGS Oct 11 '22 at 14:25