Score:0

Promote to Master a MySQL slave that has its own slaves

tg flag
MGS

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?

Score:0
ua flag

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.)

MGS avatar
tg flag
MGS
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)
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.