1. Setup High Availability For ProxySQL via KeepAlived In AWS

Setup High Availability For ProxySQL via KeepAlived In AWS

Usually application don’t 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 a 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 monitors health of XtraDB Cluster nodes.

Setup Percona XtraDB Cluster

The XtraDB Cluster setup is pretty straightforward and not much 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 did this with the help of this 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 the number of clusters.
Cluster setup is complete at this step.

Setup ProxySQL and KeepAlived

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

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

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

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

Update settings in proxysql:

After these changes, let’s make sure the ProxySQL pings nodes:

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

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

To provide reading/writing 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 failed nodes of rotation.

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

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

Adding High Availability

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

Install KeepAlived on ProxySQL instances and create the network interface in AWS that will be in the same subnet as the ProxySQL instances.

proxysql-tools attaches the 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 the same, except unicast_peer:

Finally, 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.

Previous Post Next Post