In the spirit of continuous improvement, I made another HA setup.
Same basic front-end with two webservers behind one HAProxy load balancer, but with way more fault tolerance behind them.
I used the guide I mentioned in the previous HA post to create this new setup.
Webservers look to 192.168.100.2 for a database connection. This IP can exist on one of three ProxySQL servers thanks to keepalived. keepalived does two things (at least in my setup): it checks to see if the ProxySQL process is running, and it talks to other keepalived servers. On the keepalived server that is the master, keepalived puts 192.168.100.2 on an interface. On keepalived slave servers, 192.168.100.2 is not assigned. On the master, if the ProxySQL process ceases to exist, keepalived removes 192.168.100.2 and one of the slave servers puts 192.168.100.2 on one of their interfaces. This happens in a matter of seconds. 192.168.100.2 goes away on the server that had it almost as soon as I kill ProxySQL and then magically shows up on one of the other ProxySQL servers. The technical term for this is a “virtual IP” but I think “conditional IP” is more appropriate. A servers ability to have that IP is conditional on 1) ProxySQL is running and 2) no other server has that IP. If both of those conditions exist, then that server can have that IP.
Because I’m using Galera instead of master-slave replication, I can distribute all queries from the webservers across all databases. In production, depending on the application, this may not be appropriate, but as an exercise for me, this is OK. From the little research I’ve done, it is possible to confuse a Galera cluster, but only under very high load, considerable latency between the nodes, and with conflicting queries. For my purposes, distributing my dinky queries across all three nodes hasn’t produced any issues (i.e. my test wordpress refreshes just fine).
My next idea is to apply keepalived treatment to the front end. That is, have two HAProxy load balancers in front of the webservers, and have keepalived pass the public IP back and forth between them as required.
Test wp on the new setup with http://ha2.travnewmatic.com/wp/