Multi source replication between 2 PXC

Hi Team,
I am running 2 PXC with 3 nodes each one PXC as Primary and another as DR, with help of @Pep_Pla I was able to configure GTID-based async replication where primary PXC has 1 master node and DR pxc has 1 slave node configured.
But now I want to configure multi source replication, want to know if it is recommended or can cause some issue because I think all the nodes in Primary PXC will have the same GTIDs and as I read multi-source replication don’t have the capability to detect conflicts, so if I configure 3 or 2 nodes as a master in primary and one as a slave in DR, what can be the challenges and how to remove them with multi-source replication.

https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source-configuration.html

Thanks
Adi

1 Like

I just saw in multi-source replication each source should only replicate a single database and if all databases are the same on each source could it cause problems?

2 Likes

Hi Adi,

I need some clarification: Do you want several nodes from Primary PXC replicate to several nodes in the DR PXC? Something like this:

PrimaryPXC  Node1   Node2   Node3
              |       |       |
              V       V       V
DrPXC       NodeA   NodeB   NodeC

If you want to do this, this is not a good idea. You will generate a lot of conflicts. ALL events
will replicate across all replication channels, and the destination cluster must decide
which events apply. You can even have conflicts that either break replication
or impact the cluster.

Thanks

Pep

1 Like

Hi @Pep_Pla,

Sir, Thanks a lot for your reply, I want several nodes from primary pxc to replicate to single node on DR PXC.

In your above-provided example Node1 Node2 Node3 in Primary should only replicate to NodeA in DR and the other 2 nodes in DR (NodeB and NodeC) can be taken care by PXC replication from Node A in DR.

So if any of the node from Primary PXC goes down my replication will still work and NodeA in DR will keep getting data.

Thanks
Adi

1 Like

@Pep_Pla
Sir, I have gone through the doc provided by you but I think what I am aiming at and the capabilities of the below tool provided in the link are very complex compared to my requirement.

Please suggest.

Thanks
Adi

1 Like

Hi Adi,

Did you check the second link I provided? It does exactly what you want to do.

Pep

1 Like

Hi @Pep_Pla,
Yes, I read the below-mentioned link but it talks about how we can have multiple slaves using (Replication Manager NG) and have auto slave switch over but I am looking for multiple masters and a single slave.

Thanks
Adi

1 Like

You want to have multiple slaves to ensure that replication flow continues if one of the source servers crashes. This tool does exactly this: continue the replication flow if one of the source server crashes. This makes multi-source completely unneeded.

Multi-source between multiple nodes in the same PXC cluster to a single node in PXC is not recommended and would fix only one part of the equation: when the source node fails. But will not
fix the other part: when the replica fails.

Using some sort of replication manager will protect you from both cases.

1 Like

Hi @Pep_Pla
Thanks got it. What do you recommend as the best method if in case I have to switch to DR due to a complete Primary crash?
Eventually, I have to come back to primary ENV once it’s rebuilt or up. So should I use xtrabackup to move data from DR to Primary and then resume to Primary but in this case I think slave configuration will be restored to primary and when I have to start replication primary as master and DR as slave, will it break?
or
should I first make DR Master and Primary Slave and after switching to Primary, again make DR slave and Primary as a master?

Also, I will be encrypting my database on disk on both Primary and DR and will xtrabackup still work with encrypted PXC cluster nodes.

Thanks
Adi

1 Like

Hi,

If the primary goes down entirely and you switch to the DR, there are two situations:

  • Some transactions were missing.
  • All transactions were replicated.

You need to analyze transactions to decide what you do if some are missing. Otherwise,
you just need to point the former master to the DR and let it catchup. Once both environments
are at the same level, you can switch roles back.

Pep

1 Like

Thanks, Sir for your guidance.

2 Likes