Hi,
I am using a 5 node percona mysql xtradb cluster. my database version is 8.0.36.
all my nodes are working as master. 3 nodes are working on dc1 and 2 nodes are working on dc.
I am keeping DC2 as a disaster solution.
all sessions are coming to a single node ip in dc1 that I have specified via dns. I change the dns address in case of DR test or outage. I experience interruptions in database service when there is a communication problem between nodes or when there is a communication problem between apis and the ip that I have specified via dns.
I have three questions to ask
1- is it possible to place a vip ip in front of the nodes and come to the database from this vip ip? if possible, how can I do it?
2- is there a significant and distinguishing difference between the master master structure and the master slave structure?
3- is there an architecture that and includes a dr solution, provides high availability and that you can recommend?
This is expected. All nodes in PXC must acknowledged replicated transactions. If the link between nodes is bad/laggy, then this ack will delay things.
The free way to make a VIP is using keepalived. A more modern solution is to use ProxySQL in front of PXC to manage connections. ProxySQL will keep a single point of connection, while moving the backend to different nodes in case of failures.
This terminology does not exist in PXC. All servers are ‘source’ servers, meaning you can write to any node at any time. This is not recommended, however. All servers synchronously replicate transactions.
3 PXC nodes in DC1, 3 PXC nodes in DC2, using traditional ASYNC replication between the two independent clusters. In your current scenario, if dc1 goes down, dc2 will also be down because in dc2 there are only 2/5 nodes remaining which is < 50% quorum. You can’t have 3 in dc2 because that would result in split brain. Best option is separate clusters, with replication between them to stay in sync. If dc1 goes down, dc2 is still up and functioning.
Hi @matthewb ,Thank you for your feedback and answers.
So in the last case, I understand that using 2 separate cluster structures, 3 nodes on dc 1 and 3 nodes on dc2, will solve my problem. If I am wrong, please correct me. I understand that I need to use group replication in this architecture. I made a small architecture drawing, I would like you to guide me again on this issue.
No. Group Replication is an alternate clustering solution to PXC. You either use Percona XtraDB Cluster, or Group Replication. Do not mix.
Here is a more accurate diagram. 2 Clusters, 3 nodes each. Async replication between Primary DC and DR DC (this keeps DR DC in sync with Primary). Applications connect to ProxySQL, which routes backend connections to correct node (node1 if write transaction, node2/3 if select). If Primary node1 goes down, ProxySQL routes to Primary node2.
If entire Primary goes down, applications switch to DR ProxySQL (manual step). Replication from DR back to Primary does not happen. Once you write to DR, in order to switch back to Primary, you must rebuild Primary from DR.
