3

I am trying to design a multi-master clustered MySQL database which will be spread through out the country.

The main reason for having a cluster is so that when the internet, in one location (or node), is disconnected, that location should still be able to read and modify the contents of the database locally. So that when the internet connection is restored, the rest of the cluster get updated.

I've went through the MySQL cluster documentation, however, I couldn't find this specific question. Is it possible to achieve this using MySQL cluster?

2
  • Note that the reincarnated cluster may receive updates before the network is healed. So: "The rest of the cluster gets updated" --> Instead: updated records go both directions, and conflict resolution decides who to believe.
    – Rick James
    Commented Dec 23, 2015 at 18:00
  • @RickJames, I can manage the conflict resolution in the application, however, could you tell me a bit about how the Cluster handles the reincarnated node's changes or link me to a document that could help me? Commented Dec 23, 2015 at 21:16

1 Answer 1

1

In short, MySQL Cluster isn't a good fit for your use case.

MySQL Cluster's shared-nothing architecture provides excellent protection against component-level failure. When nodes fail or become unresponsive, Cluster takes quick action to disable affected nodes. It does not focus on conflict resolution, instead works to avoid the "split-brain" problem where a single cluster begins processing work in parallel without coordination. This is exactly the opposite the use case you describe: Database activity continuing as normal when partitioned by the network, to be reconciled later when everything reconnects.

MySQL Cluster also typically does not fare well when nodes lack low-latency connections. Relying on WAN connections between nodes is a bad idea. MySQL Cluster does support asynchronous replication between data centers.

You might consider using multi-source replication in MySQL Server 5.7 as an alternative.

8
  • PayPal uses NDB with nodes scattered all around the world. They get 1-second latency.
    – Rick James
    Commented Dec 23, 2015 at 17:57
  • "Conflict resolution" is what makes NDB impervious to network issues, such as "split brain".
    – Rick James
    Commented Dec 23, 2015 at 17:58
  • Thanks for the answers and comments, although I'm not very much concerned about latency, however, @RickJames 's argument that PayPal also use NDB with a relatively low latency gives me some confidence in MySQL Cluster. Is it possible to use multi-master design with multi-source replication? All I saw was that it provides multi-slave replication from multiple masters. I would really appreciate if anyone can tell me if what I'm trying to achieve is possible with 5.7 multi-source replication. Commented Dec 23, 2015 at 21:10
  • Would you describe your application, the desired topology, etc. I want to understand the LAN/WAN issues, scaling needs, multi-source, multi-master, etc "requirements" before providing a half-baked answer. NDB Cluster, Galera Cluster, Multi-master, Multi-source replication, etc, are rare requirements. Mixing them together is even rarer. Let's make sure it is not unnecessary complexity for what you are trying to solve.
    – Rick James
    Commented Dec 23, 2015 at 22:06
  • @RickJames, my desired topology is something like this picture, drive.google.com/file/d/0BxJGYl8RCH1zOVpaeHZ1MDNqQ0k/…. The web server and every local server should have its own synced copy of the database. The reason for this is so that when internet connection is not possible in a location, the access to the system from one location would still be possible by using the local copy of the database. And when the internet connection is restored the rest of the databases should be synced with the changes in the "reconnected" local server. Commented Dec 25, 2015 at 10:15

Your Answer

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

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