I need help to change the master from my slave. let's say i have 3 DB Server:

  • Master-1
  • Slave-2
  • Slave-3

Currently the Slave-2 and Slave-3 replicate from Master-1, i want to make Slave-2 into new Master and change Slave-3 replicate from Slave-2(New Master). How to change it in the safe way?

Thank you.


(This is a partial answer.) (My terminology uses the newly-preferred, politically correct, terms Primary and Replica.)

Currently: P1->R2->R3

  1. Stop writing to the Primary-1
  2. Wait for replication to get through to the Replicas. (This is likely to be finished "immediately".)
  3. Make Replica-2 the Primary-2 by allowing writes.
  4. Change clients to write to P2. (And, optionally, stop reading from P2.)

Now you have:
(P1 unused),
P2->R3 -- no action is needed to make that part of the replication work.

If you are going to make R4, it gets trickier. It is probably best to clone R3 to make R4. More...

To change one Replica to replicate from some other Replica (for example, one that was recently promoted to Primary), all the relevant servers needed to be caught up and not receiving traffic and replication turned off (SLAVE STOP). (This is possibly overkill.)

The next step depends on whether you have GTID enabled. If you do, it should be possible to use that to tell the slave where to pick up (and some of the steps above are overkill).

Without GTID, have the moved Replica (R4 in your example) needs a CHANGE MASTER to point to its new Primary at the correct spot. That, because of the STOPs will be conveniently at the start of some binlog. (I haven't done this in 15 years, so I am hazy on the details.)

  • Hi James, Thank you for the explanation, what about if i have this case: P1->R2 P1->R3 P1->R4 now R2 become P2(master) and i want R3 and R4 replicate from P2: P2->R3 P2->R4 can i replicate it straight from P2 or i should backup and restore P2 into R3 and R4? if can, may i ask the technical way for it? or just 'change master to P2' then start slave in R3 and R4?
    – bryan
    Oct 3 at 10:48
  • @bryan - I added a bunch. Please read other Q&As and documents.
    – Rick James
    Oct 3 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.