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?
