Replication Cluster

MySQL & MariaDB
1

Hello

I am currently using Percona MySQL 5.7 and want to upgrade to 8.0 - which I am exploring the best ways to do this.

I have three production servers.
Server1, Server2 and Server3.

Server1 → Server 2 → Server 3 → Server 1…etc

I can take one offline and upgrade it, but it will stop replication.
This made me curious, how do others handle this type of scenario so that replication can continue when one node is offline?

I’m guessing potentially something like Pacemaker but thought I’d explore?

2

Hello @Monk

Server1 → Server 2 → Server 3 → Server 1

This is known as “circular replication” and, to be blunt, there is not a single MySQL expert at Percona who will recommend this type of architecture. As you pointed out, stopping any member stops replication. This type of architecture is dangerous and provides no real HA.

You should invest time into learning proper HA/clustering technologies such as Percona XtraDB Cluster (PXC) or Group Replication (GR). You can do HA using async replication but your architecture needs to change to this:

          Server1
         /      \
        /        \
  Server2      Server3

You can use tools like ProxySQL and Orchestrator to manage connection routing, and replica promotion in the event of failure.

ProxySQL is also great when using PXC or GR to handle connection failover.

Honestly, if I was in your position, I would stand up a brand new PXC or GR using 8.0, perform a dump from Server1 and load that into the new cluster and configure one of the nodes in the new cluster as a replica of Server1. When you are read for cut-over, simply move the apps to the new cluster and then shut down Server1/2/3.

1 Like
3

Thank you Matthew, this is truly valuable.

I have never been happy with the replication cluster and I’ve looked at PXC a few times but never known how best to proceed and never had time to dig in to it properly.

We run on Ubuntu and we have several upgrades to take place across the full LAMP stack.

I would really welcome your insight more in to this. Can we pay for a service for your consultation on this?

To clarify, here, would you recommend I set up (for example), server 4, 5 and 6 all as blank MySQL 8.0 and then transfer data from Server 1 to Server 4?

4

Private message me your contact details (name, email, phone number, manager phone number) and I’ll get someone to reach out to you.

would you recommend I set up (for example), server 4, 5 and 6 all as blank MySQL 8.0 and then transfer data from Server 1 to Server 4?

Exactly. Yes. setup 4, 5, and 6 as a new MySQL 8.0 using Group Replication, then use mydumper to dump the contents from server 1, and use myloader to import into server 4.

5

Again, thank you - really appreciate your time.
I will drop you a PM now. - Edit @matthewb I am sorry, I don’t seem to be able to PM you anywhere?

This sounds good and I would presume we’d be able to also replicate from Server 1 to Server 4 for a while (which in turn would replicate to 5 and 6) - before we cut over.

6

Correct, using traditional async replication.