How can I make an Async Slave track all of the nodes in a Percona XtraDB Cluster?

Question

I'm seeing a lot of tutorials that claim I have to pick just one of the nodes in my master-master replicated PXC to act as master for an async slave using vanilla mysql replication, and if something goes wrong it's "easy to change the master to one of the other nodes." That's all well and good, but I don't want to be the one answering the pager at 2 a.m. to do a simple CHANGE MASTER TO because one of my nodes went down. Ideally the point of an HA cluster is that all but one node can go down and all dependents are none the wiser.

It occurred to me that with GTID replication I might be able to get away with vanilla mysql multi-source replication, simply listing each node in the master-master PXC as a master to the async slave. They all replicate the same gtid's, which would be idempotent on duplicate updates to the slave, right? Would that work?

Or: is there a better way?

UPDATE

Based on Rick James answer I feel I must clarify. The PXC cluster is in a single data center and is a three node HA setup all well and good on its own. The Async Slave would be an additional 4th node, out of data center and at the end of a high latency VPN/WAN link, not running PXC/Galera at all, but instead just a regular Percona Server instance replicating over vanilla GTID/Binlog Master/Slave replication against the PXC cluster. The goal is that it can treat each of the 3 nodes in the PXC cluster as a master. The problem is that the tutorials I read say I have to choose only one to be the master, and thus the single point of failure - not for the cluster, but for the role of "master status" for this 4th node async slave until somebody goes and changes which master it is tracking by hand. This is why I'm hoping to do multi-source vanilla replication treating each PXC node as its own master to the slave; as long as one stays up, the remote slave stays in sync.

I can not merely switch the slave to run PXC and make it a 4th distributed node in the cluster because it will not be replicating the entire database but only certain tables; because it is a lower power than the existing cluster nodes; and because I do not want to slow down the cluster (which is used in high-performance production and needs ACID compliance) with the slave (which is allowed to get out of sync a little bit).

Answer 1

You can get rid of the pager, but not if you expect >= 50% failure.

PXC (Galera), spread across 3 geographic locations, is an excellent HA configuration.

Optionally, one of them can be a "garbd" -- light weight, no data -- node only for providing a "quorum".

If any one server goes down, the other two declare that they have a quorum and continue running.

If the network goes down, and 2 servers are split from the 3rd, then the 2 declare quorum and the 1 gracefully fades away.

"Normally" you need to plan for a single point of failure. That is what the above provides.

If you really need to survive (without a midnight page) two failures, then you need 5 (or more) nodes in the cluster -- and place them in 5 geographic locations. (Else a tornado, earthquake, etc, could take out multiple nodes.)

I am unclear on what you mean by a "Slave tracking all nodes". Perhaps you want to set up two 3-node clusters with async replication from one to the other? That is possible, and it has some degree of failover.

Some people even put the 3 nodes in a single location at one end; the the other 3 at a single location at the other end. This gives them most of the HA, but without the dreaded latency you may experience between the 3+ nodes in a WAN cluster.

Depending on where your clients are, and which node each client prefers to talk to, a WAN Cluster may be both faster and more reliable than any non-Cluster configuration. (That is another discussion.)

As for "all but one node can go down" -- that is [probably] a theoretical impossibility, especially when you consider that "split brain" is one of the failure cases. The Cluster depends on discovering which subset of more than 50% of the nodes are still talking to each other.