[ClusterLabs] MySQL cluster with auto failover

[Damiano Giuliani]

Everything is clear now.
So the point is to use pacemaker and create the floating vip and bind it to
sqlproxy to health check and route the traffic to the available and healthy
galera nodes.

Am I right?
It could be useful let pacemaker manage also galera services?

Do you have any guide that pack this everything together?

Thanks ur info's are so precious to me!

On Wed, Sep 6, 2023, 4:24 PM Antony Stone <Antony.Stone at ha.open.source.it>
wrote:

> 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.
> _______________________________________________
> Manage your subscription:
> https://lists.clusterlabs.org/mailman/listinfo/users
>
> ClusterLabs home: https://www.clusterlabs.org/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.clusterlabs.org/pipermail/users/attachments/20230906/343f087d/attachment.htm>