How to add VIPs to Percona XtraDB Cluster or MHA with Pacemaker

It is a rather frequent problem to have to manage Virtual IP addresses (VIPs) with a Percona XtraDB Cluster (PXC) or with MySQL master HA (MHA). In order to help solving these problems, I wrote a Pacemaker agent, mysql_monitor that is a simplified version of the mysql_prm agent. The mysql_monitor agent only monitors MySQL and set attributes according to the state of MySQL, the read-only variable, the slave status and/or the output of the clustercheck script for PXC. The agent can operate in 3 modes or cluster types: replication (default), pxc and read-only.

The simplest mode is read-only, only the state of the read_only variable is looked at. If the node has the read_only variable set to OFF, then the writer is set to 1 and reader attributes is set to 1 while if the node has read_only set to ON, the writer attributes will be set to 0 and the reader attribute set to 1.

In replication mode, the writer and reader attributes are set to 1 on the nodes where the read_only variable is set to OFF and on the nodes where the read_only variable is set to ON, the reader attribute is set according to the replication state.

Finally, in the PXC mode, both attributes are set according to the return code of the clustercheck script.

In all cases, if MySQL is not running, the reader and writer attributes are set to 0. The following table recaps the behavior in a more visual way, using readable for the reader attribute and writable for the write attribute:

Cluster type: Read-only Replication PXC
MySQL not OK readable = 0
writable = 0
Read-only = OFF readable = 1
writable = 1
readable = 1
writable = 1
Read-only = ON
Replication OK
readable = 1
writable = 0
readable = 1writable = 0 N/A
Read-only = ON
Replication Not OK
readable = 1
writable = 0
readable = 0writable = 0 N/A
Clustercheck OK N/A N/A readable = 1
writable = 1
Clustercheck not OK N/A N/A readable = 0
writable = 0


The agent can be found in the percona-pacemaker-agents github repository, more specifically here and the accompanying documentation here.

To get a feel how use it, here’s a sample pacemaker configuration for a PXC cluster:

The resulting cluster status with the attributes set looks like:

Nothing too exciting so far, let’s desync one of the nodes (pacemaker-1), setting the variable wsrep_desync=1. This gives us the following status:

… where, as expected, no VIPs are now on the desynced node, pacemaker-1. Using different VIPs for PXC is not ideal, the pacemaker IPAddr2 agent allows to create a clone set of an IP using the CLUSTERIP target of iptables. I’ll write a quick follow up to this post devoted to the use of CLUSTERIP.

Share this post

Comments (2)

  • User

    Why not just deallocate the VIP with wsrep_notify_command? What is the use case for your example?

    November 20, 2013 at 4:36 pm
  • hcymysql

    app — lvs —- PXC

    November 27, 2013 at 5:01 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.