I have a mysql xtradb cluster environment with 5 nodes in master master structure. 3 of these nodes are located on dc1 and the other 2 are located on dc2. In addition to my current system, I want to position another server on DC2. I want the replication to be 3 hours behind on this server, and only reading will be done through this newly added node. Is it possible to implement this new architecture that I want to do? If possible, I would like some guidance on how to do it.
Percona XtraDB Cluster is exactly that, “a cluster”, which means data is the same on every node. If you want to intentionally lag replication, you must use native async replication. You can set up a 3rd MySQL server (non-PXC) in DC2. Configure it to replicate async from node4.dc2 and set SOURCE_DELAY=10800 The blue circles below are your 5-node PXC, and the orange circle is your async replica, 3 hrs delayed.
thanks @matthewb Is it possible to share the configuration example or document for the node on the server that is included in the cluster on DC2 and the Mysql server that will not be included in the cluster?
There isn’t really a config to share. The async node is just a regular Percona Server MySQL set up to be a replica.