MySQL Cluster has a pretty cool feature that allows you to add new data nodes whilst the cluster is online, thus avoiding any downtime. This is incredibly useful for scaling out the data nodes and adding additional node groups. In this article, I’ll show how to add two new data nodes to an existing cluster that has two data nodes defined. I’ll also explain what needs to happen after the configuration change to ensure that any existing data is correctly partitioned across the new nodes.

NoOfReplicas is set to 2 - so there are 2 copies of each fragment of data. The hosts comprising the cluster before the changes are:

• mysql01.local - 192.168.122.40 - Management and API node 1 (ndb-nodeid=1)
• mysql02.local - 192.168.122.41 - Management and API node 2 (ndb-nodeid=2)
• mysql03.local - 192.168.122.42 - Data node 1 (ndb-nodeid=3)
• mysql04.local - 192.168.122.43 - Data node 2 (ndb-nodeid=4)

I’ll be adding the following to the cluster:

• mysql05.local - 192.168.122.44 - Data node 3 (ndb-nodeid=5)
• mysql06.local - 192.168.122.45 - Data node 4 (ndb-nodeid=6)

The two new nodes will form a new nodegroup, and we’ll end up with two nodegroups in our cluster - nodegroup 0 containing the original members (ID=3, ID=4) and the new nodegroup 1 containing the two new members (ID=5, ID=6).

Prior to making changes, verify that the existing cluster is operational:

Load in some test data. I’ll just create a clustered database with a single table, and add a few rows of data to it. This will enable us to verify partitioning after the new data nodes have been added:

If we run ndb_desc, we can see that the data has been partitioned across both existing data nodes, and that the appropriate fragments have been created for our two replicas:

Next, modify config.ini on both management servers and add in the appropriate configuration for the two new data nodes. My config.ini files are stored at /var/lib/mysql/config.ini:

Restart both management nodes so that the new configuration takes effect, with the --reload or --initial option. You’ll need to restart both at the same time so that the configurations do not differ across the two management nodes. Whilst this is occurring, the cluster will be operational as usual:

Verify that the new configuration is in effect by running ALL STATUS to show the status of ALL the data nodes:

Perform a rolling restart of the existing data nodes. Ensure that each data node has restarted fully and successfully before restarting the next one:

Whilst the nodes are restarting, you can enter <NODEID> STATUS as above and observe the start phases (0 through to 101) until the node is fully online.

Next, perform a rolling restart of any existing API nodes (for example: mysqld instances):

Now, the new data nodes can be started. Use the --initial option to ensure that the NDB filesystem is initialised. The new data nodes can be started at the same time:

Wait for the nodes to reach a started state by monitoring them in the ndb_mgm client using ALL STATUS:

Once all data nodes are online, issue SHOW:

As you can see, the new nodes are not placed into a nodegroup automatically. We must do this manually with the CREATE NODEGROUP command:

The new nodegroup is ready, and the data nodes have been added to the cluster without any downtime!

New data will be distributed around the new data nodes correctly, and the appropriate partitions (4 of them - 8 fragments as we have NoOfReplicas = 2 and 4 data nodes) will be created for new tables. But what about existing tables? Remember our clusterdb.testtable table that we created earlier? ndb_desc shows that this table has not been re-partitioned:

We need to run ALTER ONLINE TABLE <tablename> REORGANIZE PARTITION from an API node for this re-balancing to occur:

Let’s check again with ndb_desc:

Excellent - we now have four partitions correctly fragmented (scroll across!) across the four nodes.

You should also OPTIMIZE TABLE to clear up any wasted space (the other type of fragmentation…):

Remember - you’ll need to ALTER ... REORGANIZE PARTITION and OPTIMIZE all of your NDB tables. You can get a list of those tables from the INFORMATION_SCHEMA as follows:

Finally, check the memory usage across the cluster from an ndb_mgm client: