0

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.

2
  • How much downtime can you afford?
    – Rick James
    Commented Aug 31, 2022 at 23:45
  • @RickJames : We cannot afford downtime. Our architecture has 2 masters(Active and Passive) so while we upgrade the active, the requests would be diverted to the passive which would then be the active master. This is the only period that we can afford for taking down the master and switching the passive to become active and take requests.
    – msbeast
    Commented Sep 6, 2022 at 14:20

2 Answers 2

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.

1
  • This worked for me. Since our database is on MySQL version 5.6, I had to do an additional step before the change master statement :--> slave reset all; to clear the initial master configuration from the memory.
    – msbeast
    Commented Sep 12, 2022 at 13:56
0
  • 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.

3
  • The current MySQL version is 5.5 and I am planning to upgrade to 5.7. The plan for upgrade would be 5.5 --> 5.6 --> 5.7. The GTIDs is turned off and we are still on traditional replication using binary logs co-ordinates. During the upgrade on the Master, I will be doing a switchover on the slave that I tested above and it worked. I will be planning to move to clustering post version upgrade. Also, we have 3 DCs to support failover.
    – msbeast
    Commented Sep 12, 2022 at 14:00
  • @msbeast - I added more.
    – Rick James
    Commented Sep 12, 2022 at 16:28
  • Thank you, yes for now our plan is to upgrade to 5.7 and not 8.0. We are thoroughly testing for the changes specifically with new keywords/changing names/syntax.
    – msbeast
    Commented Sep 20, 2022 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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