MySQL NDB Cluster (NDB) is truly amazing. High availability and durability built into its design. Blazingly fast. Online scalable and upgradable. NDB serves your critical applications or infrastructure with its proven five 9’s availability. It’s capable of running millions of transactions per second – with no downtime while increasing your database size, or during upgrades. These are all reasons why you find NDB serving some of most business critical workloads in the world.
But – high performance always comes at a cost. In this case a cost of administering. As NDB is scaled out across many hosts, and more capacity is added, the complexity and time needed to administer the database grows. With MySQL Cluster Manager, or MCM, these complex and time-consuming management tasks become a whole lot easier – allowing you, the DBA, to focus elsewhere.
This is the second blog on using MCM to manage NDB. While the first blog covered the basics of installing, and bootstrapping on a single host. In this blog we will cover
- multi-host setups
- backup and restore of NDB
- scaling NDB
- upgrading NDB
If you haven’t already read it, please head over to the first blog, MySQL Cluster Manager for MySQL NDB Cluster, and have a look. We’ll assume you did, and leave some details covered there out of this post.
Multi-host setup
Although --bootstrap minimizes the time for download to play, get NDB up and running quickly, a single-host cluster is honestly not really a cluster. We need more hosts!
As in the first blog we will use OCI hosts running OL8 for the examples in this blog. That said, MCM supports multiple platforms, as well as running on-premise.
We kick off two OCI hosts, lets call them lagoon and reef. Next we install our MCM and cluster packages, and start a mcmd on each of them. We then create a site, named s, across the two hosts
mcm> create site --hosts=lagoon,reef s;
+---------------------------+
| Command result |
+---------------------------+
| Site created successfully |
+---------------------------+
1 row in set (0.40 sec)
The list hosts command shows the host information for our newly created site
mcm> list hosts s;
+--------+-----------+---------+
| Host | Status | Version |
+--------+-----------+---------+
| lagoon | Available | 8.4.4 |
| reef | Available | 8.4.4 |
+--------+-----------+---------+
2 rows in set (0.20 sec)
Next we add the package – the location of the NDB binaries – and give the package a logical name. With this being the NDB 8.4 binaries, we get creative and call the package “8.4”.
mcm> add package --basedir=/export/home/tmp/foo/mcm-8.4.4-linux-glibc2.28-x86-64bit/cluster 8.4;
+----------------------------+
| Command result |
+----------------------------+
| Package added successfully |
+----------------------------+
1 row in set (0.20 sec)
With the package in place, we can create our cluster. We make it use our newly added “8.4” package using the --package option, and name our cluster “c”. The -R option specifies the processes and host distribution for the cluster, using a process@host notation. We round-robin across the hosts, adding one ndb_mgmd, two ndbmtd datanodes, two mysqld servers, and a ndbapi slot not for ndbapi clients. The ndbapi is given a wildcard hostname as it should be available from any host.
mcm> create cluster --package=8.4 -R ndb_mgmd@lagoon,ndbmtd@reef,ndbmtd@lagoon,mysqld@reef,mysqld@lagoon,ndbapi@* c;
+------------------------------+
| Command result |
+------------------------------+
| Cluster created successfully |
+------------------------------+
1 row in set (0.30 sec)
List clusters should now show our newly created cluster
mcm> list clusters s;
+---------+---------+
| Cluster | Package |
+---------+---------+
| c | 8.4 |
+---------+---------+
1 row in set (0.10 sec)
Configuration parameters can be altered as we please using the get and set commands. For this example we stick with the assigned defaults:
mcm> get -d port:mysqld mycluster;
+------+-------+----------+---------+----------+---------+-------+---------+
| Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment |
+------+-------+----------+---------+----------+---------+-------+---------+
| port | 3306 | mysqld | 146 | | | | |
| port | 3306 | mysqld | 147 | | | | |
+------+-------+----------+---------+----------+---------+-------+---------+
2 rows in set (0.10 sec)
Before the initial start of our cluster, the processes are all in state added
mcm> show status --process c;
+--------+----------+--------+--------+-----------+---------+
| NodeId | Process | Host | Status | Nodegroup | Package |
+--------+----------+--------+--------+-----------+---------+
| 145 | ndb_mgmd | lagoon | added | | 8.4 |
| 1 | ndbmtd | reef | added | n/a | 8.4 |
| 2 | ndbmtd | lagoon | added | n/a | 8.4 |
| 146 | mysqld | reef | added | | 8.4 |
| 147 | mysqld | lagoon | added | | 8.4 |
| 148 | ndbapi | * | added | | |
+--------+----------+--------+--------+-----------+---------+
6 rows in set (0.10 sec)
And after starting, the processes should all be in state running – except the empty ndbapi slot we added for any NDB tools we may want to run.
mcm> start cluster c;
+------------------------------+
| Command result |
+------------------------------+
| Cluster started successfully |
+------------------------------+
1 row in set (18.02 sec)
mcm> show status --process c;
+--------+----------+--------+---------+-----------+---------+
| NodeId | Process | Host | Status | Nodegroup | Package |
+--------+----------+--------+---------+-----------+---------+
| 145 | ndb_mgmd | lagoon | running | | 8.4 |
| 1 | ndbmtd | reef | running | 0 | 8.4 |
| 2 | ndbmtd | lagoon | running | 0 | 8.4 |
| 146 | mysqld | reef | running | | 8.4 |
| 147 | mysqld | lagoon | running | | 8.4 |
| 148 | ndbapi | * | added | | |
+--------+----------+--------+---------+-----------+---------+
6 rows in set (0.11 sec)
As your number of hosts and processes increase, using MCM becomes even simpler.
MySQL NDB Cluster backup and restore
Creating a cluster backup is really simple.
mcm> backup cluster c;
+-------------------------------+
| Command result |
+-------------------------------+
| Backup completed successfully |
+-------------------------------+
1 row in set (1.70 sec)
That’s it. MCM ran a NDB cluster backup for you.
Backups can be listed using the list backups command
mcm> list backups c;
+----------+--------+--------+----------------------+-------+---------+
| BackupId | NodeId | Host | Timestamp | Parts | Comment |
+----------+--------+--------+----------------------+-------+---------+
| 1 | 1 | reef | 2024-11-08 11:21:23Z | 1 | |
| 1 | 2 | lagoon | 2024-11-08 11:21:23Z | 1 | |
+----------+--------+--------+----------------------+-------+---------+
2 rows in set (0.30 sec)
But, what good is a backup if you cannot do restore? Restore takes the NDB backupid as its --backupid option. You can find the backup id in the list backups output. A word of caution is in order – this will permanently change the contents of your database. Then again, that is the idea of a restore.
mcm> restore cluster --backupid=1 c;
+--------------------------------+
| Command result |
+--------------------------------+
| Restore completed successfully |
+--------------------------------+
1 row in set (19.64 sec)
There is also functionality to backup and restore MCM itself, although you can read more about that in the MCM documentation.
Scaling MySQL NDB Cluster
As your usage of NDB increases and traffic ramps up you may eventually want to start scaling NDB. There are a number of ways you can scale NDB, and it is a topic in its own. To name a few, you may
- increase storage capacity for existing datanodes
- add mysqlds to existing hosts to increase read/write capacity
- add datanodes to existing hosts to increase storage capacity
- add hosts with more mysqlds to increase read/writes capacity
- add hosts with more datanodes increase storage capacity
But – its all simple with MCM!
Let us double the number of hosts, datanodes, and mysqlds in our example cluster. We fire up two additional OCI hosts, peer and barrier, install MCM and NDB as earlier, and start the mcmd process on the two new hosts.
We add the hosts pier and barrier to our existing site s
mcm> add hosts --hosts=pier,barrier s;
+--------------------------+
| Command result |
+--------------------------+
| Hosts added successfully |
+--------------------------+
1 row in set (1.90 sec)
mcm> list hosts s;
+---------+-----------+---------+
| Host | Status | Version |
+---------+-----------+---------+
| lagoon | Available | 8.4.4 |
| reef | Available | 8.4.4 |
| pier | Available | 8.4.4 |
| barrier | Available | 8.4.4 |
+---------+-----------+---------+
4 rows in set (0.40 sec)
Next update the package with the new install paths for the newly added hosts
mcm> add package --hosts=pier,barrier --basedir=/export/home/tmp/foo/mcm-8.4.4-linux-glibc2.28-x86-64bit/cluster 8.4;
+----------------------------+
| Command result |
+----------------------------+
| Package added successfully |
+----------------------------+
1 row in set (0.10 sec)
We can now add the desired processes to our existing cluster, using the process@host notation from create cluster. This is a somewhat time consuming task as it involves a full rolling restart of the existing cluster. The larger the size of the NDB database, the longer this may take.
mcm> add process -R ndbmtd@pier,ndbmtd@barrier,mysqld@pier,mysqld@barrier c;
+----------------------------+
| Command result |
+----------------------------+
| Process added successfully |
+----------------------------+
1 row in set (1 min 40.84 sec)
The newly added processes are now shown as added
mcm> show status --process c;
+--------+----------+---------+---------+-----------+---------+
| NodeId | Process | Host | Status | Nodegroup | Package |
+--------+----------+---------+---------+-----------+---------+
| 145 | ndb_mgmd | lagoon | running | | 8.4 |
| 1 | ndbmtd | reef | running | 0 | 8.4 |
| 2 | ndbmtd | lagoon | running | 0 | 8.4 |
| 3 | ndbmtd | pier | added | n/a | 8.4 |
| 4 | ndbmtd | barrier | added | n/a | 8.4 |
| 146 | mysqld | reef | running | | 8.4 |
| 147 | mysqld | lagoon | running | | 8.4 |
| 149 | mysqld | pier | added | | 8.4 |
| 150 | mysqld | barrier | added | | 8.4 |
| 148 | ndbapi | * | added | | |
+--------+----------+---------+---------+-----------+---------+
10 rows in set (0.10 sec)
The newly added processes are started using the --added option to the start process command
mcm> start process --added c;
+------------------------------+
| Command result |
+------------------------------+
| Process started successfully |
+------------------------------+
1 row in set (21.63 sec)
Checking the status now shows all our processes as running.
mcm> show status --process c;
+--------+----------+---------+---------+-----------+---------+
| NodeId | Process | Host | Status | Nodegroup | Package |
+--------+----------+---------+---------+-----------+---------+
| 145 | ndb_mgmd | lagoon | running | | 8.4 |
| 1 | ndbmtd | reef | running | 0 | 8.4 |
| 2 | ndbmtd | lagoon | running | 0 | 8.4 |
| 3 | ndbmtd | pier | running | 1 | 8.4 |
| 4 | ndbmtd | barrier | running | 1 | 8.4 |
| 146 | mysqld | reef | running | | 8.4 |
| 147 | mysqld | lagoon | running | | 8.4 |
| 149 | mysqld | pier | running | | 8.4 |
| 150 | mysqld | barrier | running | | 8.4 |
| 148 | ndbapi | * | added | | |
+--------+----------+---------+---------+-----------+---------+
10 rows in set (0.10 sec)
As shown – scaling out is really simple using MCM.
Upgrading MySQL NDB Cluster
As new versions of NDB become available we surely want to upgrade. For this example we will download, install, and upgrade to the 9.2 Innovation Release of MySQL NDB Cluster. The downloaded tarball is extracted to its own folder, alongside the existing cluster folder from earlier. We chose cluster-9.2 as the folder name, but you may name it as you please.
$> ls
cluster
cluster-9.2
mcm-8.4.4
...
Once installed, add the new package, and give it a name – in this case “9.2”
mcm> add package --basedir=/export/home/tmp/foo/mcm-8.4.4-linux-glibc2.28-x86-64bit/cluster-9.2 9.2;
+----------------------------+
| Command result |
+----------------------------+
| Package added successfully |
+----------------------------+
1 row in set (0.20 sec)
Upgrade is as easy as telling mcmd to use a different package for a given cluster.
mcm> upgrade cluster --package=9.2 c;
+--------------------------------------+
| Command result |
+--------------------------------------+
| Cluster version changed successfully |
+--------------------------------------+
1 row in set (3 min 27.98 sec)
The upgrade cluster command triggers a rolling restart of all the cluster processes, restarting them on the new version, while ensuring NDB is kept online throughout the process.
Using the ndb_mgm client we see that cluster was indeed upgraded to 9.2.0
$ ./cluster/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to management server at localhost port 1186 (using cleartext)
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1 @100.103.27.101 (mysql-9.2.0 ndb-9.2.0, Nodegroup: 0)
id=2 @100.103.28.238 (mysql-9.2.0 ndb-9.2.0, Nodegroup: 0)
id=3 @100.103.28.83 (mysql-9.2.0 ndb-9.2.0, Nodegroup: 1, *)
id=4 @100.103.24.226 (mysql-9.2.0 ndb-9.2.0, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=145 @100.103.28.238 (mysql-9.2.0 ndb-9.2.0)
[mysqld(API)] 5 node(s)
id=146 @100.103.27.101 (mysql-9.2.0 ndb-9.2.0)
id=147 @100.103.28.238 (mysql-9.2.0 ndb-9.2.0)
id=148 (not connected, accepting connect from any host)
id=149 @100.103.28.83 (mysql-9.2.0 ndb-9.2.0)
id=150 @100.103.24.226 (mysql-9.2.0 ndb-9.2.0)
Alternatively, we can verify by connecting to one of the mysqlds with the mysql client
$ ./cluster/bin/mysql --protocol=tcp -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 9.2.0-cluster MySQL Cluster Enterprise Server - Commercial
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'version';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| version | 9.2.0-cluster |
+---------------+---------------+
1 row in set (0.00 sec)
Upgrading MCM itself to a new version is simple and well documented in the “Upgrading MySQL Cluster Manager” chapter of the MCM documentation.
Security
MCM 8.4 handles configuration of TLS for NDB cluster. Head over to our blog post “TLS in MySQL NDB Cluster 8.4 with MCM 8.4” if you’re interested.
Summary
MySQL NDB Cluster is an awesome product – performant and scalable to your needs. With scalability comes an increasing management cost. However, with MySQL Cluster Manager we have taken away the complexity, offering a simple approach to common management tasks. The more hosts, and more cluster processes you have to manage, the greater the ease of use with MCM. Complex and time consuming manual operations such as backups, restores, rolling reconfigurations, scaling, and upgrades of MySQL NDB Cluster, become a breeze. Give it a go.
Enjoy!