I have to upgrade a MySQL replication topology from 5.7 to 8.0 without database downtime. It’s MySQL Community Edition.
There is one source and 5 replicas in the topology and connections happen via ProxySQL Cluster of 3 nodes. Out of the 5 replicas, 2 replicasserve application connections in read_only
mode. All the replicas replicate from the master A.
The Source is A, and the Replicas are - B, C, D, P1, P2. P1 and P2 are production replicas where application connections come.
I am confused on how do I plan the upgrade without downtime. If it was simple source-replica replication, it wouldn't have been a problem. This Percona blog provides step-by-step guide.
My understanding so far
- Upgrade slave B, also upgrade rest of the replicas in the topology
- Change ProxySQL configuration and remove source A from the topology and make new source B writeable in the ProxySQL,
- Stop application, modify the application connection parameters. Meanwhile let B catch
with A. Use
SHOW MASTER STATUS
onA
andSHOW REPLICA STATUS
onB
to verify the coordinates match. Once they catch up, noteSHOW MASTER STATUS
onB
. - Change replication for all the replicas to the new source B.
- Once the connections start coming to new source B, old source A is free to be upgraded.
- Once A is upgraded, I can bring it back in the topology to serve as a replica.
Please help me out understanding the process. I am getting confused because of additional slaves and production slaves as well.
Testing of MySQL version 8 is complete and there's green signal for upgrades. I am aware of the things that are supposed to be taken care of before the servers are upgraded and that there's no going back hence backup is important.