[ClusterLabs] MySQL cluster with auto failover

[Antony Stone]

On Wednesday 06 September 2023 at 13:58:51, Damiano Giuliani wrote:

> What I miss is how my application can support the connection on a multi
> master where 3 ips are available simultaneously.
> 
> JDBCmysql driver or similar support a list name/ip of clustered nodes?
> Galera provide a unique cluster ip?
> 
> Whats the point to create another cluster(pacemaker) for the VIP alongside
> galera?

Simple - as you explained above, you can't assume that clients can cope with 
the idea of 3 IPs for the actual clustered machines - clients want a single IP 
to connect to, and *you* want to be sure that that IP is going to provide a 
working connection, even if one of the machines happens to have a problem.

So, you have:

machine A - real IP address 198.51.100.1
machine B - real IP address 198.51.100.2
machine C - real IP address 198.51.100.3

All machines run MySQL, ProxySQL and corosync / pacemaker.

Pacemaker manages a floating IP address 198.51.100.42.

ProxySQL on each machine listens for connections to 198.51.100.42 and passes 
requests on to 198.51.100.1, 198.51.100.2 and 198.51.100.3.  ProxySQL knows 
how to monitor the three real IPs to make sure they can handle queries, and if 
one stops working, it stops getting sent queries.

MySQL listens for connections on 198.51.100.1 on machine A, 198.51.100.2 on 
machine B and 198.51.100.3 on machine C.

Galera makes sure that all three servers have the same DB content, so a query 
sent to any will return the same result.

That gives you a single floating IP address 198.51.100.42 which all clients can 
connect to, that address is ensured by pacemaker to be on a working server, 
and ProxySQL ensures that a request to that address is passed to a working 
instance of MySQL.


Antony.

-- 
This email is intended for the use of the individual addressee(s) named above 
and may contain information that is confidential, privileged or unsuitable for 
overly sensitive persons with low self-esteem, no sense of humour, or 
irrational religious beliefs.

If you have received this email in error, you are required to shred it 
immediately, add some nutmeg, three egg whites and a dessertspoonful of caster 
sugar.  Whisk until soft peaks form, then place in a warm oven for 40 minutes.  
Remove promptly and let stand for 2 hours before adding some decorative kiwi 
fruit and cream.  Then notify me immediately by return email and eat the 
original message.

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