How to set up a three-node MySQL 8 cluster on Debian 10

How to set up a three-node MySQL 8 cluster on Debian 10

MySQL is a free and open source relational database management system. It is used to store data for various purposes, including data warehousing, e-commerce, and logging applications. MySQL Cluster is a technology that provides scalability and availability at low cost.

In this tutorial, we will use one master node to store the configuration of the cluster and two data nodes to store the data of the cluster. The IP address of each node is as follows:

  • Master node or management node-104.245.33.61
  • Data node 1-104.245.32.195
  • Data node 2-69.87.218.169

prerequisites

  • Three servers running Debian 10, one for the management node and the other for the data node.
  • A root password is set on each server.

getting Started

First, you need to update all servers to the latest version. You can update everything with the following command:

apt-get update -y

After all servers are updated, you can proceed to the next step.

Install and set up MySQL Cluster Manager

First, you need to install the MySQL cluster management software package on the master node. By default, this package is not included in the Debian 10 default repository.Therefore, you need to download it from the official MySQL website website.

You can download it with the following command:

wget https://cdn.mysql.com//Downloads/MySQL-Cluster-8.0/mysql-cluster-community-management-server_8.0.24-1debian10_amd64.deb

After downloading the deb file, install it using the following command:

dpkg -i mysql-cluster-community-management-server_8.0.24-1debian10_amd64.deb

After the installation is complete, use the following command to create a directory to store the cluster configuration:

mkdir /var/lib/mysql-cluster

Next, use the following command to create a new configuration file:

nano /var/lib/mysql-cluster/config.ini

Add the following line:

[ndbd default]
NoOfReplicas=2  # Number of replicas

[ndb_mgmd]
# Management process options:
hostname=104.245.33.61 #IP of the MySQL Cluster Manager
datadir=/var/lib/mysql-cluster
[ndbd]
hostname=104.245.32.195 #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=69.87.218.169 #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=104.245.33.61 #IP of the MySQL Cluster Manager

Save and close the file when you are done, and then start the cluster manager with the following command:

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

You should get the following output:

MySQL Cluster Management Server mysql-8.0.24 ndb-8.0.24
2021-05-10 08:23:05 [MgmtSrvr] INFO     -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2021-05-10 08:23:05 [MgmtSrvr] INFO     -- Sucessfully created config directory

Now, use the following command to kill the running server:

pkill -f ndb_mgmd

Next, create a systemd service file for MySQL Cluster Manager to manage the service:

nano /etc/systemd/system/ndb_mgmd.service

Add the following line:

[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

Save and close the file, then reload the systemd daemon using the following command:

systemctl daemon-reload

Next, use the following command to start the cluster manager and enable it to start when the system restarts:

systemctl start ndb_mgmdsystemctl enable ndb_mgmd

You can now check the status of the service with the following command:

systemctl status ndb_mgmd

You should 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 Mon 2021-05-10 08:23:53 UTC; 6s ago
  Process: 984 ExecStart=/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini (code=exited, status=0/SUCCESS)
 Main PID: 985 (ndb_mgmd)
    Tasks: 12 (limit: 2359)
   Memory: 6.5M
   CGroup: /system.slice/ndb_mgmd.service
           ??985 /usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini

May 10 08:23:53 master systemd[1]: Starting MySQL NDB Cluster Management Server...
May 10 08:23:53 master ndb_mgmd[984]: MySQL Cluster Management Server mysql-8.0.24 ndb-8.0.24
May 10 08:23:53 master systemd[1]: Started MySQL NDB Cluster Management Server.

Once completed, you can proceed to the next step.

Install and configure data nodes

Next, we package the data node on other servers and configure it to communicate with MySQL Cluster Manager.

First, install the required dependencies on the two data nodes using the following commands:

apt-get install libclass-methodmaker-perl -y

After installing all dependencies, use the following command to download the latest version of the MySQL data node deb file:

wget https://cdn.mysql.com//Downloads/MySQL-Cluster-8.0/mysql-cluster-community-data-node_8.0.24-1debian10_amd64.deb

After the download is complete, use the following command to install the downloaded file:

dpkg -i mysql-cluster-community-data-node_8.0.24-1debian10_amd64.deb

By default, the data node stores all configurations in the /etc/my.cnf file.

Therefore, use the following commands to create a new /etc/my.cnf file on the two data nodes:

nano /etc/my.cnf

Add the cluster IP as follows:

[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=104.245.33.61 #IP of the MySQL Cluster Manager

Save and close the file, and then create a data directory on the two data nodes:

mkdir -p /usr/local/mysql/data

Now, start the data node with the following command:

ndbd

You should get the following output:

2021-05-10 08:27:13 [ndbd] INFO     -- Angel connected to '104.245.33.61:1186'
2021-05-10 08:27:13 [ndbd] INFO     -- Angel allocated nodeid: 2

Next, use the following command to kill the running ndbd process:

pkill -f ndbd

Next, use the following command to create a systemd service file for ndbd on the two data nodes:

nano /etc/systemd/system/ndbd.service

Add the following line:

[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 using the following command:

systemctl daemon-reload

Next, use the following command to start the ndbd service and enable it to start when the system restarts:

systemctl start ndbdsystemctl enable ndbd

You can also use the following command to check the status of the ndbd service:

systemctl status ndbd

You should 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 Mon 2021-05-10 08:28:28 UTC; 12s ago
  Process: 740 ExecStart=/usr/sbin/ndbd (code=exited, status=0/SUCCESS)
 Main PID: 741 (ndbd)
    Tasks: 46 (limit: 2359)
   Memory: 827.1M
   CGroup: /system.slice/ndbd.service
           ??741 /usr/sbin/ndbd
           ??742 /usr/sbin/ndbd

May 10 08:28:28 data1 systemd[1]: Starting MySQL NDB Data Node Daemon...
May 10 08:28:28 data1 ndbd[740]: 2021-05-10 08:28:28 [ndbd] INFO     -- Angel connected to '104.245.33.61:1186'
May 10 08:28:28 data1 ndbd[740]: 2021-05-10 08:28:28 [ndbd] INFO     -- Angel allocated nodeid: 2
May 10 08:28:28 data1 systemd[1]: Started MySQL NDB Data Node Daemon.

Install and configure MySQL server

Next, we will download and install the MySQL server and client packages on the MySQL cluster manager node.

First, use the following command to download the latest version of MySQL Cluster Bundle:

wget https://cdn.mysql.com/Downloads/MySQL-Cluster-8.0/mysql-cluster_8.0.24-1debian10_amd64.deb-bundle.tar

Next, use the following command to extract the downloaded file into the /opt directory:

tar -xvf mysql-cluster_8.0.24-1debian10_amd64.deb-bundle.tar -C /opt

Next, change the directory to /opt and install the required dependencies using the following command:

cd /optapt-get install libaio1 libmecab2 libnuma1 psmisc -y

Next, install the MySQL Common deb file using the following command:

dpkg -i mysql-common*

Next, install the MySQL client package using the following command:

dpkg -i mysql-cluster-community-client_8.0.24-1debian10_amd64.deb mysql-cluster-community-client-core_8.0.24-1debian10_amd64.deb mysql-cluster-community-client-plugins_8.0.24-1debian10_amd64.debdpkg -i mysql-client_8.0.24-1debian10_amd64.debdpkg -i mysql-cluster-community-server*

If you encounter any dependency errors, run the following command:

apt-get install -f

Next, install the MySQL server package using the following command:

dpkg -i mysql-server_8.0.24-1debian10_amd64.deb

During the installation process, you will be prompted to set the MySQL root password.

After installing the MySQL server, you need to edit the MySQL master configuration file and define the cluster IP:

nano /etc/mysql/my.cnf

Add the following line:

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=104.245.33.61  #IP of the MySQL Cluster Manager

Save and close the file when you are done. Next, restart the MySQL service and enable it to start when the system restarts:

systemctl restart mysqlsystemctl enable mysql

You can check the status of the MySQL server with the following command:

systemctl status mysql

You should see the following output:

? mysql.service - MySQL Cluster Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2021-05-10 08:35:04 UTC; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1950 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 1985 (mysqld)
   Status: "Server is operational"
    Tasks: 47 (limit: 2359)
   Memory: 372.0M
   CGroup: /system.slice/mysql.service
           ??1985 /usr/sbin/mysqld

May 10 08:35:01 master systemd[1]: Starting MySQL Cluster Community Server...
May 10 08:35:04 master systemd[1]: Started MySQL Cluster Community Server.

Once completed, you can proceed to the next step.

Test MySQL cluster

Now, we have successfully installed and configured a three-node MySQL cluster. Next, you need to verify that it is working properly.

First, log in to the MySQL Cluster Manager node, and then use the following command to log in to MySQL:

mysql -u root -p

Provide your MySQL root password, and then use the following command to check the cluster information:

mysql> SHOW ENGINE NDB STATUS G

If everything is ok, you should get the following output:

*************************** 1. row ***************************
  Type: ndbclus
  Name: connection
Status: cluster_node_id=4, connected_host=104.245.33.61, 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 ***************************
  Type: ndbclus
  Name: NdbRecAttr
Status: created=0, free=0, sizeof=88
*************************** 7. row ***************************
  Type: ndbclus
  Name: NdbApiSignal
Status: created=16, free=16, sizeof=144
*************************** 8. row ***************************
  Type: ndbclus
  Name: NdbLabel
Status: created=0, free=0, sizeof=200
*************************** 9. row ***************************
  Type: ndbclus
  Name: NdbBranch
Status: created=0, free=0, sizeof=32
*************************** 10. row ***************************
  Type: ndbclus
  Name: NdbSubroutine
Status: created=0, free=0, sizeof=72
*************************** 11. row ***************************
  Type: ndbclus
  Name: NdbCall
Status: created=0, free=0, sizeof=24
*************************** 12. row ***************************
  Type: ndbclus
  Name: NdbBlob
Status: created=0, free=0, sizeof=592
*************************** 13. row ***************************
  Type: ndbclus
  Name: NdbReceiver
Status: created=0, free=0, sizeof=128
*************************** 14. row ***************************
  Type: ndbclus
  Name: NdbLockHandle
Status: created=0, free=0, sizeof=48
*************************** 15. row ***************************
  Type: ndbclus
  Name: binlog
Status: latest_epoch=897648164875, latest_trans_epoch=820338753551, latest_received_binlog_epoch=0, latest_handled_binlog_epoch=0, latest_applied_binlog_epoch=0
15 rows in set (0.00 sec)

Now, exit from MySQL Shell using the following command:

mysql> exit

Now, we will perform another test to confirm that the cluster is functioning properly.

Use the following command to connect to the cluster management console:

ndb_mgm

You should see the following output:

-- NDB Cluster -- Management Client --
ndb_mgm>

Now, run the following command to check all data ndo:

ndb_mgm> SHOW

You should get the following output:

Connected to Management Server at: 104.245.33.61:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2	@104.245.32.195  (mysql-8.0.24 ndb-8.0.24, Nodegroup: 0, *)
id=3	@69.87.218.169  (mysql-8.0.24 ndb-8.0.24, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@104.245.33.61  (mysql-8.0.24 ndb-8.0.24)

[mysqld(API)]	1 node(s)
id=4	@104.245.33.61  (mysql-8.0.24 ndb-8.0.24)

To check the status of the first data node, run the following command:

ndb_mgm> 2 STATUS

You should see the following output:

Node 2: started (mysql-8.0.24 ndb-8.0.24)

To check the status of the second data node, run the following command:

ndb_mgm> 3 STATUS

You should see the following output:

Node 3: started (mysql-8.0.24 ndb-8.0.24)

in conclusion

Congratulations! You have successfully installed and set up a three-node MySQL cluster on the Debian 10 server. Now you can use this setting in a production environment to achieve scalability and availability.

Source

Sidebar