high availability – Multi-Master Replication / Mysql Slave Replication from Multiple Masters with Same Schema Names

Requirement::

Between two datacenters (DC, DRC) databases should be seamlessly replicated withstanding the datacenter failures. Both the data centers will be having on-premise servers.

DC – Primary Datacenter, DRC – Disaster recovery center. Both the data centers will be up all the time. And the traffic will be routed to 70% to DC and 30% to DRC.

Currently, we are using Mysql 5.7 community version and our application is well tested with it.

The application will have on average of > 500-1000 inserts/updates per second and continuous read operations on all tables (>100). Could be more as well. Took below stats from Innodb status of our database from other location. But not sure how trustworthy these are

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
36 read views open inside InnoDB
Process ID=2417, Main thread ID=139811035186944, state: sleeping
Number of rows inserted 19946591703, updated 265822234, deleted 140784124, read 8314412966825
413.81 inserts/s, 98.84 updates/s, 0.69 deletes/s, 3024258.44 reads/s

Background::

At first, we thought of making master-master replication by keeping only one DC Database as the primary and the DRC as the standby by replicating in real-time (Both the datacenters will be connected using a Private link with good speed). this is because we thought to avoid Sequence issues when the connection is down between the datacenters. So DC App servers and DRC App servers will be connected to DC Database itself. When the DC Database goes down DRC Database will act as primary and vice-versa.

But the challenge is when the private link between the centers is down. The DC will be up and running but DRC will face downtime due to the unavailability of DC Database. Here we can make both DC-DRC acts as primary in their respective centers to avoid downtime, But if we done this there is a chance of having duplicate primary keys/replication issues when both the centers are up again and need to be in sync.

So currently we are considering the below approaches

Approach 1::

Enable Master-Master replication and keep only 1 master in Live by connecting both the center’s app servers to a single database. if the private link between both the centers is down. enable the replication over the internet by allowing connections to particular ip’s at the firewall level.

But what if the internet also goes down but the data center is up and running? :), we are stuck here for now.

Approach 2::

Maintain a separate sequence(CASE-ID – Auto increment) for all tables in both the databases DC-DRC and replicate all that data into a common database by following a common sequence for each table. Can this be done by following any MYSQL official/proven way?

In worst-case scenario, we are thinking to use an ETL Tool talend to sync the two databases data into a single database by following a common sequence for each table.

Approach 3::

Maintain different schema names/different sequences (CASE-ID – Auto increment) for both the datacenters and then sync both the database data into a single database using channel replication(Multi-source).

Sorry for the long post. I just want to explain the full scenario to get the best possible answer :).

Please suggest if anyone has a better approach to fulfilling the requirement, I am still a beginner in Database setup so please ignore if I said anything wrong above :). I am browsing all over the internet to find a similar scenario but couldn’t able to find it and we have very little time to go-live with production. So any solution/ suggestions are much appreciated.

Thank you!