1

I am just a developer, who doesn't have deep knowledge in DBA. So need experts suggestions in this. I have two offline databases in two cities, I want to make a synchronization between two databases using a third database which will be stay alway online in a live server.

My problem is like this, Location A database is in New York. Location B database is in Canada. And they are in local machine, so no connection between them. Now I need to show the changes of Location A's to Location B and Location B's to Location A. Yeah this can be solve by MASTER-MASTER replication. Also i have found a way to solve autoincrement indexes issue, by separating index numbers in different servers.

But I need another live server C where Location A's and Location B's change will reflect. So some users need not to access Location A or Location B for looking into changes. I do not also want's to use RING replication. because if one goes down then rest of the RING will be broken.

In summery I want to make the replication like this

enter image description here

Need help about replication process.

Thanks in advance,

Nixon

  • What version are you running? Does it have "multi-source" replication? – Rick James Dec 1 '17 at 18:28
  • it's 5.7. I am still developing the solutions. So I'm open for the suggestions – nixon1333 Dec 3 '17 at 4:15
0

One solution that could work is a Galera cluster with one node in read-only mode. (See e.g. here for info about geo-distributed Galera cluster.) Galera cluster is a (virtually) synchronous multi-master solution. MariaDB and Percona provide this technology, as does Galera themselves. There is one problem: Since you would be writing to more than one node, there is a higher likelihood of experiencing deadlocks / certification failures, including on commit. (See this article for an explanation.) However, this is something your application could handle if you take care when developing it.

Be sure to review the Galera cluster limitations before you decide on this technology. Some of the most notable requirements are: A Linux OS, it only supports the InnoDB storage engine, and all tables must have an explicit primary key.

MySQL Group Replication is a similar technology, but this wouldn't be suitable for geo-distributed database nodes as in your use-case.

|improve this answer|||||
  • 1
    Even writing to one node, you can get deadlocks. The only difference is that you must check for errors also on COMMIT. InnoDB and mandatory PK are not big constraints. Galera has a way to handle "Critical read". Another discussion. – Rick James Dec 3 '17 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.