Wednesday, July 6, 2016

Percona XtraDB Cluster with Pacemaker for HA

So, I've moved on from my last employer after a decade(ish).  I've landed in a shop that is heavily devops focused, but my first real task wasn't so much about that, but highly available MySQL servers.  The MySQL end of it ended up being not too bad, two nodes running Percona XtraDB Cluster, and a third system running garbd as an arbitrator.  Had some stumbles with encrypted tables (bad documentation I claim) but once running seemed to be pretty impervious to my ham-fisted attempts to break the cluster.  The hard part came when I was trying to figure out how to move the IP around.  The Percona blogs are a wealth of information, and I found this which would seem to be very straight forward.  Its not.  RedHat has changed the tools with which you configure the cluster since the writing of that blog post, so without further ado, here's my config so a) you don't have to spend the hours I did and b) so I don't have to re-learn this somewhere down the road.  The assumptions are a two node Percona Xtradb Cluster with the mysql_monitor from the linked Percona blog, and using PCS to configure the resources:

pcs resource create pxc_monitor ocf:percona:mysql_monitor user="clustercheckuser" password="password" pid="/var/run/mysqld/"
  socket="/var/lib/mysql/mysql.sock" \
  cluster_type="pxc" op monitor interval="5s" timeout="30s" OCF_CHECK_LEVEL="1"
pcs resource clone pxc_monitor cl_pxc_monitor meta clone-max="2" clone-node-max="1"
pcs resource create vip ocf:heartbeat:IPaddr2 params ip="" nic="eth0" op monitor interval="10s"
pcs constraint location vip rule id="require_read" readable eq 1