slave replication from XtraDB three nodes cluster

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

We have business requirement to replicate databases from XtraDB three nodes cluster to a single slave Percona, questions:

  1. Is this document suitable to setup slave replication from XtraDB cluster? [url]Percona XtraBackup
  2. our three nodes cluster have load balancer at the front, can I direct slave replication against that load balancer SQL IP instead of a specific XtraDB node IP? we don’t want to break slave replication if one of our XtraDB node go down.
  3. Since slave will have other development database, is it possible to just select / overwrite particular database from XtraDB without touching all other databases on slave node?
2

  1. Is this document suitable to setup slave replication from XtraDB cluster? [URL=“How to setup a slave for replication in 6 simple steps with Percona XtraBackup”]https://www.percona.com/doc/percona-...plication.html[/URL]
    Yes, this should suffice. Treat one node of the cluster as master async node of the async slave (standard PS)

  2. our three nodes cluster have load balancer at the front, can I direct slave replication against that load balancer SQL IP instead of a specific XtraDB node IP? we don’t want to break slave replication if one of our XtraDB node go down.
    we’d recommend to point to specific node IP instead of the load balancer, if the master node (PXC node) goes down, you should be able to reconfigure the slave to point to a different master node in the same PXC cluster

  3. Since slave will have other development database, is it possible to just select / overwrite particular database from XtraDB without touching all other databases on slave node?
    The filtering should be done on the slave side, read up on the following - replicate-wild-ignore-db or replicate-wild-ignore-table.

3

Hi,

  1. our three nodes cluster have load balancer at the front, can I direct slave replication against that load balancer SQL IP instead of a specific XtraDB node IP? we don’t want to break slave replication if one of our XtraDB node go down.
    we’d recommend to point to specific node IP instead of the load balancer, if the master node (PXC node) goes down, you should be able to reconfigure the slave to point to a different master node in the same PXC cluster.

As per your solution we have to reconfigure the salve again by taking the backup of the new master node. How we can avoid that?

4

You don’t have to fully reconfigure the slave in case it’s master node needs to be changed. If GTID is enabled in the cluster ( and in sync), position will be taken automatically.
If no GTID enabled, you can use this method to find new position:
[url]https://www.percona.com/blog/2013/06/21/changing-an-async-slave-of-a-pxc-cluster-to-a-new-master/[/url]