Let me give you a bit of background before jumping into the discussion. Our company is running two DC's in active active scenario, we spent a lot of money on getting specialized hardware to be available in each site and there is a dedicated MPLS link between both sites specifically for DB replication. Each DC is independent on all layers except the DB.

Our current DB setup is InnoDB Cluster single primary ( 3 nodes in DCA and 2 nodes in DCB) with group replication. Each app server in both DC's have mySQL router installed to connect to the R/W DB. Initially it worked well when the load was very less, but recently with the increase in load we are seeing delay in transactions when all five nodes are in the cluster and if we remove the 2 nodes of DCB from the cluster everything becomes very smooth. I had multiple meetings with Oracle support and they are forcing only one solution where we'll have innoDB cluster in DCA and one Async salve node in the DCB and in case of disaster we need to manually enable the slave server in the DCB and later we need to import the backup of DCB in to DCA to bring it back. What I'm looking to achieve is to have a solution where both DC's have their own master (R/W) DB and they can work independently and also keep syncing to each other and if the link between them goes down or one DC goes down the other one keep working and sync the data once it got connection? Is it something unrealistic in terms of design? or maybe it's not possible for mysql environment? what you guys do in these scenarios? should I look at a different product or DB design?

  • What is the latency between the two DCs? Did you consider Galera Clustering?
    – Rick James
    Commented Aug 23, 2021 at 18:43
  • With only 2 DCs, you have the potential for "split brain", hence the "manual" failover.
    – Rick James
    Commented Aug 23, 2021 at 18:44
  • Only NDB allows for "eventual consistency" after a network outage between 2 DCs. Galera allows for 3 active nodes (plus replicas) and automatic failover.
    – Rick James
    Commented Aug 23, 2021 at 18:46
  • latency is around 12ms. Currently it's InnoDB. So if we move to NDB, we can survive the scenario where the links goes down and both sides keep recording transactions? Commented Aug 23, 2021 at 23:08
  • Yes. But NDB is quite different to configure and code for. And you have code for what to do if there are conflicts after the network comes back up -- For example, the "first" to COMMIT could be the winner.
    – Rick James
    Commented Aug 24, 2021 at 0:30


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.