[ClusterLabs] MySQL cluster with auto failover

[Antony Stone]

On Wednesday 06 September 2023 at 12:10:23, Damiano Giuliani wrote:

> Thanks for helping me.
> 
> I'm going to know more about Galera.
> What I don't like is seems I need many nodes, at least 3 for the cluster
> and then at least 2 other nodes for proxy.

You didn't mention anything about wanting a proxy service in your original 
posting, and there's no reason why a proxy can't run on the same machines as 
MySQL does.

As for requiring three nodes, you'll need that for pacemaker anyway.  Trying 
to run a 2-node cluster (of anything) as a production service is a disaster 
waiting to happen.  Read up about "split brain" if you do not know why.

> Asking for 5 VM is quite consuming.

What's your actual (functional) requirement here?

> As you told drbd can work only in 2 node cluster and disk replication is
> not dbms replication.
> Probably I'm going to try drbd on very small and low usage db.

The lower the usage (and therefore unrepresentative of typical production 
activity), the more likely it is that you'll think "this is working".

Assuming you mean to set up two MySQL servers each pointing at a synchronised 
DRBD storage volume on their local systems, the main thing I expect to go 
wrong is data cached in memory, perhaps during complex updates.

If instead you mean to have only one instance of MySQL running at any given 
time, and a failover involves stopping MySQL on the first node and then 
starting it on the second, then sure, that will work, but it introduces a 
(probably multi-second at the very least) delay during which no client 
requests can be processed during the failover, whereas a replicated Galera 
cluster (especially with something like ProxySQL in front of it) offers almost 
instantaneous switchover and therefore much reduced downtime in DB 
availability.

> More I know about MySQL  more postgresql seems have better replication at
> least for me.

So why not use Postgres?


Antony.

-- 
I don't know, maybe if we all waited then cosmic rays would write all our 
software for us. Of course it might take a while.

 - Ron Minnich, Los Alamos National Laboratory

                                                   Please reply to the list;
                                                         please *don't* CC me.