How do we switchover a slave to another master instead of promoting the slave to be the master?

Question

We have a replication configuration wherein there are 2 Masters and 20 Replicas(Each master has 10 replicas). Now, we need to perform MySQL version upgrade on these instances. The approach for this would be to upgrade the replicas first followed by master. The concerns is while upgrading one of the master server(Lets say Master1), how do we switch the replicas pointing to Master 1 to Master 2. Promoting the slave to master wouldn't be a good approach as we have multiple replicas.

I read an article about Switching Sources During Failover wherein they suggested :

1. Stop Slave, Reset Master
2. Stop Slave, Change Master to ..

However, I am unable to get details/info as to how should we proceed with upgrades in our replication topology and what steps should we follow to do a seamless switchover of replicas.

Answer 1

During the upgrade, the replication to Master1 is going to be stopped replicating from Master2. For simplicity, and avoiding making errors between IO and SQL position, stop the IO thread first, wait until the SQL thread is caught up, then stop the SQL thread as well.

All the replica's off Master1 will have caught up.

The show slave status on Master1 will contain the position you'll need the replicas to continue from.

So when you change master to {master2} you'll be using the SQL positions that show slave status on Master1 showed, which are the binary log positions on Master2.

Answer 2

• What version of MySQL?
• Do you have GTIDs turned on?
• The 10 Replicas on the inactive Master (during the upgrade) will not receive replication. And, without GTIDs, it is clumsy to get them sync'd up again.

Dual-Primary is "old technology".

You should consider moving to "InnoDB Cluster" or "Galera Cluster". It takes care of most ht the issues you bring up, and does more things 'automatically'. Best is to have 3 data centers so that even the loss of one will not be an issue.

More (based on Comment)

Consider doing the upgrades all at once:

5.5 multi-primary -> 5.6 (one machine) -> 5.7 cluster.

That is, keep the 5.5 going until you have thoroughly tested the 5.7 cluster. Be ready to tear down and rebuild this structure.

The arrows in above are simple replication. One of the 3 nodes in 5.6 would receive the data and pass it on to the other tow nodes (in 3 DCs).

One thing that often happens in upgrades, especially for multi-version upgrades, is new keywords and/or syntax. Wherever possible, fix things all the way back in 5.5. (Adding backtics / changing names/syntax.)

You should plan for moving on to 8.0, but I do not recommend it yet. 5.7->8.0 is likely to have more hiccup-s than 5.5->5.6->5.7. And the hiccups may be quite different.