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.
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?
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.
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.
@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.
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.
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.
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.
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.