Keepalived for Source Failover: Percona XtraDB Cluster to Percona Server for MySQL

In this article, we will demonstrate how to achieve asynchronous replication automatic source failover when our replica is a Percona Server for MySQL (PS) and the source is a Percona XtraDB Cluster (PXC) cluster, using virtual IP (VIP) managed by Keepalived.

Let us consider our architecture below with async replication from PXC to Percona Server for MySQL:

Our goal is to set node3 as the async replication primary source. Then, upon failure, VIP can move to node2 and then node1 when node2 also fails. Once node3 becomes available again, the virtual IP should come back to node3.

Why not use the below MySQL built-in functionality instead of Keepalived?

• • Asynchronous Replication Connection Failover – Automatic source list 
    • • Asynchronous_connection_failover_add_managed
        • • managed_type: The type of managed service that the asynchronous connection failover mechanism must provide for this server. The only value currently accepted is GroupReplication.

• • Automatic Asynchronous Replication Connection Failover
    • • It stays connected to whatever host it was connected to, which will not work if PXC wsrep_local_state_comment is not in a “Synced” state like when we encountered this bug that it became an “inconsistent” state and the replica connected to it will not failover to other healthy PXC node. In this case PXC, clustercheck used by Keepalived can help us check the health of a node for proper failover.

Process

Assumptions:

• • We already have a PXC cluster to PS async replication working with the below IPs:

• • We already have PXC-Keepalived set-up shown above with VIP that we will dedicate for replication use. The setup guide is in the blog Using Keepalived for HA on top of Percona XtraDB Cluster.

• • Replica user was granted privilege from any of the PXC nodes.

Prerequisites:

• • Enable log_replica_updates on all PXC nodes to ensure that all the transactions from all nodes will be written to all nodes’ binary log, making anynode qualified to act as async replication source.

• • GTID enables replication for automatic replication positioning and correct failover.

Steps:

1) Edit then verify the priority with the highest to lowest value from node3, node2, node1:

Sample configuration from node1:

2) Restart Keepalived on all nodes and check the status.

Check:

We can see that node3 is the PRIMARY with VIP while the rest are in the BACKUP state.

3) Simulate a running write application.

4) Check the replica’s current state using the below bash function:

Check replica status:

5) Reconfigure the replica to use the VIP(192.168.122.170):

6) Verify the replica. Source_Host is now set to VIP:

7) Test failover

7.1) Stop mysqld

7.2) Check the Keepalive state:

Node2 is the new PRIMARY while node3 changed to FAULT state.

7.3) Check the VIP transferred to node2:

7.4) Check the replica. You may have to wait for at least the value of SOURCE_CONNECT_RETRY, which is, by default, 60 seconds.

The Source_Server_Id, Source_Log_File, Relay_Source_Log_File, and Source_UUID have changed.

The replica now has a successful failover to the new source (node2).

When node3 comes back online again, the VIP will also move back to node3 since it has the highest Keepalived priority, making it the asynchronous replication source again.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

&

Try Percona Distribution for MySQL today!