There are quite a few MySQL operating models, for example: Master-Slave, Master-Master (Galera), Cluster, etc. But in my view, MySQL Cluster brings the most complete and comprehensive set of benefits.
MySQL Cluster overview
According to the definition from MySQL's homepage:
MySQL Cluster is a distributed database designed for scalability and high availability.
Put simply, MySQL has these capabilities:
- High scalability
- 99.999% availability
- Data integrity with backup capability between nodes.
- ....
Components of a MySQL Cluster
Data Node
A Data node's main role is storing data partitioned across the whole cluster — since a cluster is distributed and backs up data across itself, the number of Data nodes is always at least 2 or more for MySQL Cluster's benefit to actually kick in. On these data nodes, data gets cut into smaller data partitions, and these partitions have primary copies and secondary copies. How partitions and the number of replicas get divided will be discussed later in the post. The service running on a Data Node is called ndbd.
SQL Node
The SQL Node's role is receiving SQL query commands sent down from the application, then, through an API with its own ports and protocol (NDB API), sending the command to the Data Node to fetch data and return it back to the application or user. The service running on an SQL Node is mysqld.
Management Node
The Management Node is responsible for managing SQL Nodes and Data Nodes — management tasks here include things like initializing a new node, restarting a node, backing up data between nodes, dividing Node Groups (I'll explain this later), etc. Once a Cluster is running stably, the Management Node just plays an external monitoring role, so if the Management Node goes down, the cluster still operates normally. Of course, at that point you can't manage things like: adding, removing, editing nodes in the cluster.
Concepts: Node Group, Partition, Replica
Replica — Copy
While installing MySQL cluster, you'll have to configure one parameter: the number of replicas for a data partition in the cluster — usually this value is 2. This replica count directly affects the number of Node Groups. The higher the replica count, the higher the data integrity and availability — but everything has a downside: more replicas means more nodes needed to store data.
Node Group
The number of Node Groups is calculated with the formula Number of Node Groups = Number of Data Nodes / number of replicas — in a basic case (image above) with 2x2 data nodes and 2 replicas per partition, we get 2 Node Groups. The data nodes within 1 node group store the same data.
Partition — Section
Your data doesn't get stored entirely on any single node — it gets divided into smaller partitions. The number of partitions is calculated with the formula Number of partitions = Number of Data nodes * number of LDM threads
When using the default Ndbd Engine, the number of LDM threads on each node is 1, so => Number of partitions = Number of Data nodes
With the example above we see:
- Node Group 0 consists of: Node 1 and Node 2
- Node 1 contains partition 0 (primary partition) and partition 2 (secondary partition)
- Node 2 contains partition 0 (secondary partition) and partition 2 (primary partition)
- Node Group 1 consists of: Node 3 and Node 4
- Node 3 contains partition 1 (primary partition) and partition 3 (secondary partition)
- Node 4 contains partition 1 (secondary partition) and partition 3 (primary partition)
With data partitioned this way, the cluster keeps operating normally as long as just 1 single node in each Node Group is running. This guarantees the Cluster's data availability.
Trying it out
- If the management node goes down, does the cluster have an issue? No issue, as long as the cluster was already running normally
- If any random node goes down, does the cluster go down too? As long as just 1 node in each node group is still up, the cluster keeps operating normally.
- What if the number of up nodes isn't enough to complete the database? The system automatically detects this and sends a command to shut down all data nodes to protect the data.
- If I now turn on node 1 and node 3, write more data, then turn them off, then turn on node 2 and node 4 — will the data be wrong? The cluster will detect this mismatch (possibly via the timestamp field) and won't let these 2 nodes operate.
Wrapping up
You can also read more about the common errors when installing and deploying MySQL Clusters. Hope you picked up a bit of knowledge. Have a nice day!!!