Xtradb Cluster and Haproxy HA

[gilmcgrath]

We looking into haproxy to load balance mysql requests across an Xtradb Cluster. Although the DB cluster is HA, the Haproxy is not. Our cluster is an openstack/kvm environment, so we do have some limitations.

We have considered three technologies:

Heartbeat/pacemaker: This would work, but it is complicated and would depend on openstack to be up to reassign public IPs.

DNS Round Failover: This work as long as all nodes are up - but on failure you will always be redirected to failed IP some % of time. We know we do something more dynamic with DNS. But that too seems like it is getting more complicated.

Keepalived: According to our networking folks this will not work in our implementation of openstack/KVM becuase we can not dynamically assign IPs to interfaces.

Are there other alternatives that we have not looked at?

Does mysql client side have IP failover capabilities?

[jedmands]

I’m not sure that heartbeat is all that complicated. The pacemaker stuff is out of my realm for now. I will be upgrading to that in the near future,etc.

Standard heartbeat should be very easy and it takes care of the floating IP.
Just takes installation of heartbeat package included in EPEL or standard repos, config of /etc/ha.d/{haresources,ha.cf,auth_keys}, and sequentially starting the services.

I’d say you’ll spend more time setting up the dev environment than actually configuring or learning and then configuring heartbeat. It’s something to know on it’s own before moving to heartbeat/pacemaker anyways.

Setup 3 quick VM’s and give it a whirl.

Anyways my thought are:
2 x haproxy hosts running on something minimal and virtualized if you want.

• on the same 2 hosts you can have heartbeat running across the two to float the IP
  3 x percona mysql in xtradb cluster

someone reply and tell me if I am wrong, I am learning as well.