Senin, 14 Oktober 2024

MYSQL NDB-3node multi master

 

Setting Up a MySQL NDB Cluster: Step-by-Step Guide

https://medium.com/@umairhassan27/setting-up-a-mysql-ndb-cluster-step-by-step-guide-b7310492ef60
Umair Hassan
4 min read

MySQL NDB (MySQL Cluster) is a distributed database system known for its high availability and scalability. Oracle typically recommends a configuration with five nodes, consisting of one management node, two data nodes, and two SQL nodes. However, in this guide, we will demonstrate how to set up a simplified MySQL NDB Cluster with three nodes: one management node and two combined data and SQL nodes.

Let’s jump into the practical world now.

I am doing this on an Ubuntu servers, if you guys are planning to do it on a different OS make sure you download the right packages for this from the MySQL website: https://downloads.mysql.com/archives/cluster

Please note that I won’t explain every command in detail, assuming you have a basic grasp of MySQL and Linux commands.

Data Nodes:

  1. Update, upgrade & install required libraries your system packages:
1- apt-get update -y
2- apt-get upgrade -y
3- apt-get install libclass-methodmaker-perl -y
4- apt-get install libaio1 libmecab2 -y

2. Download MySQL NDB cluster deb packages for Node 1 and Node 2.

wget https://downloads.mysql.com/archives/get/p/14/file/mysql-cluster-community-client_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-cluster-community-server-core_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-cluster-community-data-node_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-common_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-cluster-community-server_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-cluster-community-client-core_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-client_8.0.19-1ubuntu18.04_amd64.deb
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-server_8.0.19-1ubuntu18.04_amd64.deb

3. Install the downloaded deb files on Node 1 and Node 2:

dpkg -i *.deb

4. Create data directories for MySQL nodes:

mkdir -p /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

5. Create an NDB service:
Define a systemd service for the NDB data node process

vi /etc/systemd/system/ndbd.service

[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

6. Enable the NDB service:

systemctl enable ndbd

7. Edit the my.cnf file on both nodes to configure the cluster connection:

vim /etc/mysql/my.cnf
#Add the specified lines under [mysqld] and [mysql_cluster].

 #Add below Lines
 [mysqld]
 ndbcluster
 ndb-connectstring=192.168.221.132 #IP of Managament Node

 [mysql_cluster]
 ndb-connectstring=192.168.221.132 #IP of Managament Node

Management Node:

  1. Download and Install the MySQL NDB Cluster Management Server deb package:
wget https://downloads.mysql.com/archives/get/p/14/file/mysql-cluster-community-management-server_8.0.19-1ubuntu18.04_amd64.deb
dpkg -i mysql-cluster-community-management-server_8.0.19-1ubuntu18.04_amd64.deb

2. Make Cluster Directory and Configure Configuration File on Management Node

On the Management Node, you need to create a directory to hold your NDB Cluster configuration file and then configure the file. Here’s how you can do it:

# Create the directory
mkdir -p /usr/mysql-cluster

# Create and edit the configuration file
vim /usr/mysql-cluster/config.ini

3. Inside the config.ini file, add the following configurations (you can customize them based on your requirements):

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2  # Number of replicas

[ndb_mgmd]
# Management process options:
hostname=192.168.221.132  # Hostname of the Management NOde
datadir=/usr/mysql-cluster  # Directory for the log files

[ndbd]
hostname=192.168.221.133  # Hostname/IP of the first data node
NodeId=2  # Node ID for this data node
datadir=/var/lib/mysql/  # Remote directory for the data files

[ndbd]
hostname=192.168.221.134  # Hostname/IP of the second data node
NodeId=3  # Node ID for this data node
datadir=/var/lib/mysql/  # Remote directory for the data files

[mysqld]
NodeId=4
hostname=192.168.221.133 #SQL Node 1

[mysqld]
NodeId=5
hostname=192.168.221.134 #SQL Node 2

3. Configure the ndb_mgmd service on the management node:

vi /etc/systemd/system/ndb_mgmd.service

[Unit]
Description=MySQL NDB Cluster Management Server
After=network.target auditd.service

[Service]
Type=forking
ExecStart=/usr/sbin/ndb_mgmd -f /usr/mysql-cluster/config.ini
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure

[Install]
WantedBy=multi-user.target

4. Enable the ndb_mgmd service:

systemctl enable ndb_mgmd
#Reload systemd to apply changes:
systemctl daemon-reload

5. Initialize the management node and start the data nodes one by one.

#On managament Node
ndb_mgmd --initial --config-file=/usr/mysql-cluster/config.ini

#Once it's started run the below command on each of the Data nodes
NDBD

#Below will be the output of the NDBD command
2023-02-16 04:28:00 [ndbd] INFO     -- Angel connected to '192.168.221.132:1186'
2023-02-16 04:28:00 [ndbd] INFO     -- Angel allocated nodeid: 2

6. Start the Database Service on each of the data + SQL node.

Service mysql start

7. Verify the Cluster status

#On the Management node run the below command
ndb_mgm -e show

#The output will be like below
Connected to Management Server at: 192.168.221.132:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.221.133  (mysql-8.0.19 ndb-8.0.19, Nodegroup: 0, *)
id=3    @192.168.221.134  (mysql-8.0.19 ndb-8.0.19, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.221.132  (mysql-8.0.19 ndb-8.0.19)

[mysqld(API)]   2 node(s)
id=4    @192.168.221.133  (mysql-8.0.19 ndb-8.0.19)
id=5    @192.168.221.134  (mysql-8.0.19 ndb-8.0.19)

Testing the Cluster:

  1. Connect to Node 1 using MySQL client:
    Connect to the one of the Database and create the database.
mysql -u root -p
mysql> create database this_is_from_Node1;
# You can also create a table with the engine NDB, and insert some rows into it for verification purpose.

2. Connect to Node 2 using MySQL client:
Now For verification purpose connect to the 2nd node of the MySQL and verify the DB.

mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
| this_is_from_Node1 |
+--------------------+

Conclusion:

In conclusion, you’ve now successfully learned how to set up a MySQL NDB Cluster, a robust and high-performance distributed database system. This guide has walked you through the essential steps, from updating your server to configuring data nodes and management nodes. You’re now equipped to harness the power of NDB Cluster for your applications.

If you encounter any issues or have questions during the setup process, don’t hesitate to seek assistance. Setting up a cluster can be complex, and it’s essential to ensure everything runs smoothly.

Remember, practice makes perfect, and the more you work with MySQL NDB Cluster, the more proficient you’ll become. So, dive into the world of distributed databases with confidence and explore the endless possibilities it offers for high availability and scalability in your projects.

For more insightful articles on MySQL and database management, follow Umair Hassan on LinkedIn and on Medium.

Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)