Usually application do not connect directly to Percona XtraDB Cluster, but go through a proxy – ProxySQL, for instance. However if only one proxy node is used it becomes a single point of failure. Not long ago Marco Tusa wrote about how to configure two ProxySQL nodes in front of XtraDB cluster. If deployed on EC2 instances it doesn’t work that way because Amazon doesn’t allow to assign secondary IP address on an interface.

This post describes how to configure highly available ProxySQL with keepalived, proxysql-tools and AWS Elastic Network Interface(ENI).
The application connects to a single Virtual IP. The VIP is assigned to ENI which is managed by keepalived. proxysql-tools moves ENI between ProxySQL instances and monitor health of XtraDB Cluster nodes.

Setup Percona XtraDB Cluster

Setting up XtraDB Cluster is pretty straightforward and not different from any other case.

MySQL configuration file on other nodes differs only in wsrep_node_address.

After this step, We can bootstrap our cluster. I have made in from first node by next command:

When the first node has been started, cluster status can be checked by:

When all nodes are running, wsrep_cluster_size will be equal to number of clusters.
Cluster setup is complete at this step.

Setup ProxySQL and KeepAlived

It’s worth noting ProxySQL instances and ENI must be on the same subnet.

First, We must to configure our ProxySQL instances for proxy request to cluster nodes.

Now, we’ll setup ProxySQL for work with our Galera nodes. I will use default hostgroup.

Now, we must create user for monitoring Percona XtraDB Cluster nodes in ProxySQL. You can do it on any node of cluster.

And update settings in proxysql:

After this changes, let’s try to see monitoring and ping logs:

The previous examples show that ProxySQL is able to connect and ping the nodes you added.
To enable monitoring of these nodes, load them at runtime:

Now, we must to create user that will work with our XtraDB Cluster nodes.
To add a user, insert credentials into mysql_users table:

To provide read/write access to the cluster for ProxySQL, add this user on one of the Percona XtraDB Cluster nodes:

proxysql-tools can monitor health of XtraDB Cluster nodes and take out of rotation failed nodes.

Install it to your proxysql instances using pip install proxysql-tools  and add it into ProxySQL scheduler:

Repeat steps above for another ProxySQL instance.
Now, ProxySQL instances are configured. But we haven’t added high availability yet.

The idea is following.

keepalived on each of ProxySQL nodes will monitor each other. When one of the nodes becomes active we will attach ENI to the active node and configure VIP on it. The application will use the VIP to connect to MySQL. No configuration changes are needed when the active ProxySQL node changes.

So, install KeepAlived on ProxySQL instances and create the network interface in AWS that will be in same subnet with ProxySQL instances.

proxysql-tools attaches network interface to an active node. Let’s configure it:
Create config file /etc/twindb/proxysql-tools.cfg and add content below:

Virtual IP is address of your ENI. After this, we must configure KeepAlived.

unicast_peer is the IP on eth0 of other ProxySQL node proxysql-tool with arguments aws notify_master attach ENI Virtual IP to the instance. On passive ProxySQL, config is same except unicast_peer:

And now you can start keepalived as service. One of the ProxySQL nodes will become active, /usr/local/bin/proxysql-tool aws notify_master will move the ENI to the active node and assign the VIP to it.