MySQL Cluster Self Healing And No Single Point Of Failure Feature

In this post we will see the unique features of MySQL Cluster i.e. Self healing and no single point of failure.

MySQL Cluster No Single Point Of Failure:

As we know that in MySQL Cluster, data nodes are logically grouped into nodegroups. All data nodes in a nodegroup contain the same data, kept in sync at all times. Different nodegroups contain different data. So in a single nodegroup, MySQL Cluster can sustain multiple node shutdown at the same time but at least one node must live in that nodegroup. Lets demonstrate this feature with a 4 replicas cluster where each nodegroup have 4 data nodes.

Lets create a MySQL Cluster with below environment.

• MySQL Cluster 8.0.22 version
• 2 Management servers
• 8 Data nodes servers
• 2 Mysqld servers
• 4 API nodes
• OS: Ubuntu 20.04 LTS
• NoOfReplicas = 4
  

Lets check the cluster status from the management client:




We can see that 8 nodes cluster is up and running with 4 replicas. Also we can see that each nodegroup has 4 data nodes. Lets kill two data nodes from each nodegroup and see if cluster is surviving or not.

Lets get the pid of data nodes (ID: 1, 2, 5, 6) and kill it (kill -9).

Lets check the status of the all the data nodes that we have killed. These nodes should be in disconnected state.

We can see that cluster is up and running with 4 nodes shutdown.Lets get the pid of one more data node from each nodegroup (ID: 3, 7) and kill it (kill -9).

Lets check the status of cluster after 2 more nodes down.

We can see that, with 2 nodes alive out of 8 data nodes, cluster is still up and running. Lets see if we can do any transactionss on this cluster.

Lets connect to the cluster from the mysql client and create a database, a table and insert few rows into the table.

Lets delete one row from the table 'actor' and check the table again. The idea here is to perform some ops on the cluster and check if cluster is still up and running.

At the end lets look at the cluster again.

So from the above demo, we can conclude that, until one node exist in each nodegroup, MySQL Cluster can survive from multiple nodes crash or down.

MySQL Cluster Self Healing:

To demonstrate this feature, lets create a MySQL Cluster with below environment.

• MySQL Cluster 8.0.22 version
• 2 Management Servers
• 4 Data Nodes Servers
• 2 Mysqld Servers
• 4 API Nodes
• OS: Ubuntu 20.04 LTS
• NoOfReplicas = 2

If you are wondering how to setup a MySQL Cluster, then please look into my
previous blog here.

The idea here is to stop/kill randomly any of the data node and then the data node should start automatically without user intervention.

Node crashed and restarted automatically:

Lets check the status of the cluster thru management client (bin/ndb_mgm):

We can see that cluster is up and running with 4 data nodes. Lets kill (-9) one of the data node say Data node Id#2.

The logic here is when data node starts, it actually initiates two processes. The first of these is called the “angel process” and the other one called “execution/worker process”. Angel process checks to see if the main ndbd or ndbmtd process (the primary data node process which actually handles the data) is running or not. If necessary, attempts to restart the worker process if it is configured to do so.

From the data node 2 (below), the PID:5977 is the angel process while PID:5978 is the worker process. In this demo, we will kill the worker process and see if it is restarting automatically or not.

Lets kill the worker process (PID: 5978) of data node 2 thru kill -9 command:

Now check the data node '2' state from the management client:

Node 2 is down now after killing the worker process. Lets wait for a moment and check the node status.

Data node 2 is in ‘starting’ state.

Now, data node '2' is up and running. So we saw that initially data node '2' was up and running, then we killed it and this node came again automatically. This is called self healing capability of MySQL Cluster.