How To Create a Multi-Node MySQL Cluster on Ubuntu 20.04. In this post, we will introduce MySQL Cluster, and its major components. Then, we will explain how to set up three node MySQL cluster on Ubuntu 20.04.
A collection of commodity components, providing scalability and availability at a low cost, clusters, store and process the information on commodity nodes. This way, creating a database cluster for high end enterprise applications is relatively effortless. Cluster database architecture distinguishes itself through data responsibilities shared among different computer nodes.
Let’s start this article blog How To Create a Multi-Node MySQL Cluster on Ubuntu 20.04.
What is MySQL Cluster?
First of all, MySQL Cluster is a distributed database that merges linear scalability and high availability to provide in memory real time access with transactional consistencies created for a mission critical application.
This fault tolerant in memory clustered database entirely runs on cost effective commodity hardware. This way, in memory database clustering is performed in shared nothing systems. This architecture further enables the system to work with the least specific hardware and software requirements.
Components of MySQL Cluster
MySQL Clusters constitute the following components:
Data Nodes
With the help of Data Nodes, you can store data in a MySQL cluster environment. Actually, it imitates data nodes, making them available whenever one or more storage nodes fail. After, the stored data is visible to every MySQL server connected to the cluster. Moreover, data nodes handle the entire database transactions. However, some MySQL data, like permissions and stored procedures, cannot be stored in the cluster and should be updated on every MySQL server attached to the cluster.
Management Client
Additionally, this client program manages your cluster. Basically, it delivers every administrative functionality, like starting and stopping nodes, getting status information, and creating backups.
Applications
Applications connect to the MySQL cluster exactly as they connect to the MySQL cluster through MySQL Server. So, if you use another MySQL storage engine, they will use group applications. However, your application may specifically need to handle cluster specific features.
Management Server Nodes
Besides, Administration Server nodes handle system configuration an used it to reconfigure the cluster. As a result, the server node management should only run during system start up and reconfiguration. Other components of the MySQL cluster can work without relying on the Central Management Server node.
MySQL Server Nodes
Certainly, these nodes run on a MySQL server that has access to cluster storage. Multiple MySQL servers can be connected to a cluster. Consequently, it provides redundancy and performance through parallel processing. When you perform updates on one MySQL server, they are immediately reflected on other servers connected to the cluster.
How To Create a Multi-Node MySQL Cluster on Ubuntu 20.04
In this section, we will explain how to create three node MySQL cluster on a system running Ubuntu 20.04 server.
To demonstrate this article, we will use the following setup:
Step 1 - Install and Configure Cluster Manager
First, you will need to download and install the MySQL Cluster Manager package on the manager node. Please download the latest version of MySQL Cluster Manager from the MySQL official download page.
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-8.0/mysql-cluster-community-management-server_8.0.31-1ubuntu20.04_amd64.deb
Once the download is completed, install the downloaded file using the following command:
dpkg -i mysql-cluster-community-management-server_8.0.31-1ubuntu20.04_amd64.deb
Next, create a cluster configuration file using the following command:
mkdir /var/lib/mysql-cluster
nano /var/lib/mysql-cluster/config.ini
Add the following lines to define your manager node and data nodes:
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
[ndb_mgmd]
# Management process options:
hostname=192.168.10.10 # Hostname of the manager
datadir=/var/lib/mysql-cluster # Directory for the log files
[ndbd]
hostname=192.168.10.11 # Hostname/IP of the first data node
NodeId=2 # Node ID for this data node
datadir=/usr/local/mysql/data # Remote directory for the data files
[ndbd]
hostname=192.168.10.12 # Hostname/IP of the second data node
NodeId=3 # Node ID for this data node
datadir=/usr/local/mysql/data # Remote directory for the data files
[mysqld]
# SQL node options:
hostname=192.168.10.10 # In our case the MySQL server/client is on the same Droplet as the cluster manager
Save and close the file then start the manager with the following command:
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
You will get the following output:
MySQL Cluster Management Server mysql-8.0.31 ndb-8.0.31
2022-10-23 04:31:49 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2022-10-23 04:31:49 [MgmtSrvr] INFO -- Sucessfully created config directory
As you can see, the MySQL Cluster Manager is installed and running. Now, kill the running server and create a systemd file for Cluster manager:
pkill -f ndb_mgmd
nano /etc/systemd/system/ndb_mgmd.service
Add the following configurations:
[Unit]
Description=MySQL NDB Cluster Management Server
After=network.target auditd.service
[Service]
Type=forking
ExecStart=/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
After, save and close the file then reload the systemd daemon to apply the changes:
systemctl daemon-reload
Following is to start and enable the Cluster Manager with the following command:
systemctl start ndb_mgmd
systemctl enable ndb_mgmd
As noted, you can also check the active status with the following command:
systemctl status ndb_mgmd
And you will get the following output:
● ndb_mgmd.service - MySQL NDB Cluster Management Server
Loaded: loaded (/etc/systemd/system/ndb_mgmd.service; disabled; vendor preset: enabled)
Active: active (running) since Sun 2022-10-23 04:32:36 UTC; 5s ago
Process: 1052 ExecStart=/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini (code=exited, status=0/SUCCESS)
Main PID: 1053 (ndb_mgmd)
Tasks: 12 (limit: 2347)
Memory: 2.4M
CGroup: /system.slice/ndb_mgmd.service
└─1053 /usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Oct 23 04:32:36 masternode systemd[1]: Starting MySQL NDB Cluster Management Server...
Oct 23 04:32:36 masternode ndb_mgmd[1052]: MySQL Cluster Management Server mysql-8.0.31 ndb-8.0.31
Oct 23 04:32:36 masternode systemd[1]: Started MySQL NDB Cluster Management Server.
Step 2 - Install and Configure Data Nodes
Step 2 is to download and install the MySQL Data Node package on both data nodes, so they can communicate with Cluster Manager.
Firstly, download the latest version of NDB Data Node Binaries from the MySQL official download page:
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-8.0/mysql-cluster-community-data-node_8.0.31-1ubuntu20.04_amd64.deb
Once the package is downloaded, install the required dependencies with the following command:
apt install libclass-methodmaker-perl
After that, install the downloaded binary with the following command:
dpkg -i mysql-cluster-community-data-node_8.0.31-1ubuntu20.04_amd64.deb
Further step is to create a data node configuration file:
nano /etc/my.cnf
Define the IP address of your Cluster Manager as shown below:
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.10.10 # location of cluster manager
Save and close the file then create a data directory:
mkdir -p /usr/local/mysql/data
Start the data node with the following command:
ndbd
You will get the following output:
2022-10-23 04:34:17 [ndbd] INFO -- Angel connected to '192.168.10.10:1186'
2022-10-23 04:34:17 [ndbd] INFO -- Angel allocated nodeid: 2
Here in the next step, kill the running data node with the following command:
pkill -f ndbd
Then, create a systemd service for data node with the following command:
nano /etc/systemd/system/ndbd.service
Add the following configuration:
[Unit]
Description=MySQL NDB Data Node Daemon
After=network.target auditd.service
[Service]
Type=forking
ExecStart=/usr/sbin/ndbd
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
Save and close the file then reload the systemd daemon to apply the changes:
systemctl daemon-reload
After that, start and enable the data node with the following command:
systemctl start ndbd
systemctl enable ndbd
You can check the status of the data node with the following command:
systemctl status ndbd
You will get the following output:
● ndbd.service - MySQL NDB Data Node Daemon
Loaded: loaded (/etc/systemd/system/ndbd.service; disabled; vendor preset: enabled)
Active: active (running) since Sun 2022-10-23 04:34:53 UTC; 6s ago
Process: 1302 ExecStart=/usr/sbin/ndbd (code=exited, status=0/SUCCESS)
Main PID: 1314 (ndbd)
Tasks: 46 (limit: 2347)
Memory: 761.5M
CGroup: /system.slice/ndbd.service
├─1314 /usr/sbin/ndbd
└─1315 /usr/sbin/ndbd
Oct 23 04:34:53 datanode1 systemd[1]: Starting MySQL NDB Data Node Daemon...
Oct 23 04:34:53 datanode1 ndbd[1302]: 2022-10-23 04:34:53 [ndbd] INFO -- Angel connected to '192.168.10.10:1186'
Oct 23 04:34:53 datanode1 ndbd[1302]: 2022-10-23 04:34:53 [ndbd] INFO -- Angel allocated nodeid: 2
Oct 23 04:34:53 datanode1 systemd[1]: Started MySQL NDB Data Node Daemon.
Step 3 - Install and Configure MySQL Server
In step 3 you will need to download and install the MySQL server and client packages on the Manager Node.
1. Download the latest version of the MySQL Cluster Server tar file from the MySQL official download page:
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-8.0/mysql-cluster_8.0.31-1ubuntu20.04_amd64.deb-bundle.tar
2. Create a install directory and extract the downloaded tar file inside the install directory:
mkdir install
tar -xvf mysql-cluster_8.0.31-1ubuntu20.04_amd64.deb-bundle.tar -C install/
3. Navigate to the install directory and install all .deb package files using the following command:
cd install
apt install libaio1 libmecab2
dpkg -i *.deb
apt-get install -f
4. Edit the MySQL configuration file:
nano /etc/mysql/my.cnf
5. Define your cluster manager as shown below:
[mysqld]
# Options for mysqld process:
ndbcluster # run NDB storage engine
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.10.10 # location of management server
6. Save and close the file then restart the MySQL service to apply the changes:
systemctl restart mysql
Step 4 - Test MySQL Cluster
At this point, the MySQL multi-node cluster is up and running. Now, its time to test it. First, log in to the MySQL shell using the following command:
mysql -u root -p
Once you are logged in, check the cluster status using the following command:
SHOW ENGINE NDB STATUS \G
You will get the following output:
*************************** 1. row ***************************
Type: ndbclus
Name: connection
Status: cluster_node_id=4, connected_host=192.168.10.10, connected_port=1186, number_of_data_nodes=2, number_of_ready_data_nodes=2, connect_count=0
*************************** 2. row ***************************
Type: ndbclus
Name: NdbTransaction
Status: created=2, free=2, sizeof=392
*************************** 3. row ***************************
Type: ndbclus
Name: NdbOperation
Status: created=4, free=4, sizeof=944
*************************** 4. row ***************************
Type: ndbclus
Name: NdbIndexScanOperation
Status: created=0, free=0, sizeof=1152
*************************** 5. row ***************************
Type: ndbclus
Name: NdbIndexOperation
Status: created=0, free=0, sizeof=952
*************************** 6. row ***************************
Next, exit from the MySQL shell using the following command:
EXIT;
In here, open the Cluster management console using the following command:
ndb_mgm
Once you are connected, run the following command to test test the cluster:
SHOW
You should get the following output:
Connected to Management Server at: 192.168.10.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.10.11 (mysql-8.0.31 ndb-8.0.31, Nodegroup: 0, *)
id=3 @192.168.10.12 (mysql-8.0.31 ndb-8.0.31, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.10.10 (mysql-8.0.31 ndb-8.0.31)
[mysqld(API)] 1 node(s)
id=4 @192.168.10.10 (mysql-8.0.31 ndb-8.0.31)
2 STATUS
Node 2: started (mysql-8.0.31 ndb-8.0.31)
That is it. Thank you for reading How To Create a Multi-Node MySQL Cluster on Ubuntu 20.04. We shall conclude now.
How To Create a Multi-Node MySQL Cluster on Ubuntu 20.04 Conclusion
In short, MySQL clusters replicates each other across multiple geographical built-in sites. Moreover, a shared nothing architecture with data locality awareness makes it a perfect choice for running on commodity hardware and globally distributed cloud infrastructure.
Why don’t you take a look at our blog with more MySQL content over here.
