Mysql – What happens when a MySQL Cluster node is disconnected

MySQLmysql-clusterndbcluster

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?

Best Answer

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.

Related Solutions

Mysql – What’s the difference between master and slave nodes in MySQL Cluster

there are not Masters/Slaves in Cluster, the nodes are all active. That Master label is an internal thing and you don't have to take care about it.

@Tom: are you sure you know mysql cluster? To be a specific use case doesn't mean to be "strange". To be more precise, MySQL Cluster gives the best when you have a huge amount of small transactions, since is a parallel database. If you have a small number of big transactions, if not for you. But if you have small transactions you can distribute them between the nodes.

Massimo

MySQL – Best Disaster Recovery Setup for Percona XtraDB Cluster

In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other segments. This was specifically thought for geographical redundancy.

Before 5.6:

  ---------------
 /     /   \     \
A --- B --- D --- E   (A, B, C Local nodes; D,E Remote nodes; all talk to all)
 \   /     /     /
   C ------------

After 5.6 (Galera 3):

[ A --- B ] --- [ D --- E ]   (no redundant communication between segments)
[  \   /  ]
[    C    ]

In any case, think that the round-trip time will limit the minimum latency for each transaction, which means that using geographical replication will make the cluster much slower.

An alternative option that you can setup is combining Galera and standard replication, like this:

 A --- B ~~~ D --- E (--- : Galera replication; ~~~: MySQL replication )
  \   /
    C

Standard MySQL replication is way more efficient, as it does not wait for the "slaves" to confirm the changes (it does not use group communication). The disadvantage is clear: being asynchronous it does not guarantee that absolutely all changes applied to the primary cluster has been transmitted to the slave node or nodes. If the replications is well managed that should only mean less than 1 second of transactions, but this is certainly a no-go for banking applications or other applications where loss of information is impossible even if the whole datacenter fails. Whoever, that is something that most web applications and services can handle. On the negative aspect, standard replication has some disadvantages, specially if it is not well managed, like potential data drifts or extra latency due to it being single-threaded.

Best Answer

Related Solutions

Mysql – What’s the difference between master and slave nodes in MySQL Cluster

MySQL – Best Disaster Recovery Setup for Percona XtraDB Cluster

Related Question