Task:
Installation of MySQL Cluster on Ubuntu 20.0.4 or Ubuntu 18.04.
Notes:
These instructions create a 3 node cluster. The two data nodes run the MySQL ndbd.
The manager node runs Cluster Manager, ndb_mgmd, and the MySQL server/client, mysqld and mysql, respectively.
Download installation files:
Get the MySQL DEB files from: dev.mysql.com/downloads/cluster
For the management node, we need:
- NDB Management Server: mysql-cluster-community-management-server-dbgsym_8.0.21-1ubuntu20.04_amd64.deb
For the data nodes, we need:
- NDB Data Node Binaries: mysql-cluster-community-data-node_8.0.21-1ubuntu20.04_amd64.deb
For RedHat or CentOS Linux:
- NDB Management Server: mysql-cluster-community-management-server-8.0.21-1.el8.aarch64.rpm
- NDB Data Node Binaries: mysql-cluster-community-data-node-8.0.21-1.el8.x86_64.rpm
Create MySQL Management and Data Nodes
Create 3 VMs:
- Create 1 VM: 1 CPU, 1024 MB memory, 60 GB thin disk
- - Name node: MySQLMgmt
- Create 2 VMs: 3 CPU, 5120 MB memory, 200 GB thin disk (or bigger depending on your disk space needs. The memory gives 1GB to overhead/misc and 4GB to the MySQL database replica)
- - Name node: MySQLData1 and MySQLData2
- Hosts:
- - Typically one VM per host. Use VM affinity or manually place.
- Network:
- - Create within a private network. Assuming this network has a NAT and firewall to the "public" web servers.
- Template/VM to Clone:
- - You can use Red Hat Linux, Ubuntu, or SUSE. In this scenario, we will use Ubuntu 20.04 VMs.
-- OS admin w/sudo account: myadmin, pwd: mypwd123#
Set-up the Mgmt Node:
1. Use wget to get the files directly from the dev.mysql.com repo, or download and transfer to the servers. Install with dpkg.
$ cd ~
$ mkdir tmp
$ cd tmp
<transfer the file or use wget>
$ sudo dpkg -i mysql-cluster-community-management-server-dbgsym_8.0.21-1ubuntu20.04_amd64.deb
2. Create a configuration file:
$ sudo mkdir /var/lib/mysql-cluster/
$ sudo vi /var/lib/mysql-cluster/
<a>
[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M
[ndb_mgmd]
# Management process options. Update the hostname below:
hostname=10.0.222.11
NodeId=1
datadir=/var/lib/mysql-cluster
[ndbd default]
# Node option defaults. The memory settings assume VMs with 4GB RAM, with swap-to-disk disabled
NoOfReplicas=2
datadir=/usr/local/mysql/data
# Performance variables below. See docs for when to use and not use.
LockPagesInMainMemory=1
DataMemory=3072M
IndexMemory=384M
ODirect=1
NoOfFragmentLogFiles=300
MaxNoOfConcurrentOperations=100000
MaxNoOfTables=1024
MaxNoOfOrderedIndexes=256
# thread mgmt
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
RedoBuffer=32M
# backup compression
CompressedLCP=1
CompressedBackup=1
[ndbd]
# Data 1 node options. Update the hostname below:
hostname=10.0.222.21
NodeId=2
[ndbd]
# Data 2 node options. Update the hostname below:
hostname=10.0.222.22
NodeId=3
[mysqld]
# SQL node options. Hostname is same as mgmt name since on same mgmt node.
hostname=10.0.222.11
<esc>:wq, to save
Note:
See the MySQL docs for parameters.
3. Create the systemd service startup/shutdown.
$ sudo vi /etc/systemd/system/ndb_mgmd.service
<a>
[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
<esc>:wq, to save
Start the service:
$ sudo systemctl daemon-reload
$ sudo systemctl start ndb_mgmd.service
$ sudo systemctl status ndb_mgmd.service
< view output>
If there is an error, check the service file created above. If it is running okay, let's enable it to start on all reboots.
$ sudo systemctl enable ndb_mgmd.service
4. Update the firewall to allow the data nodes:
$ sudo ufw allow from 10.0.222.21
< Rule added>
$ sudo ufw allow from 10.0.222.22
< Rule added>
Set-up the Data Nodes:
1. Install the prerequisite package. Then use wget to get the files directly from the dev.mysql.com repo, or download and transfer to the servers. Install with dpkg.
$ sudo apt-get update
$ sudo apt-get install libclass-methodmaker-perl
<wait>
$ cd ~
$ mkdir tmp
$ cd tmp
<transfer the file or use wget>
$ sudo dpkg -i mysql-cluster-community-data-node_8.0.21-1ubuntu20.04_amd64.deb
2. Create a configuration file:
$ sudo vi /etc/my.cnf
<a>
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=10.0.22211 # location of cluster manager
<esc>:wq, to save
3. Make the MySQL data folder:
$ sudo mkdir -p /usr/local/mysql/data
4. Update the firewall to allow the management node and the "other" data node:
Note: Update the 22 to a 23 depending on which data server you are setting up.
$ sudo ufw allow from 10.0.222.11
< Rule added>
$ sudo ufw allow from 10.0.222.23
< Rule added>
5. Make the service file for ndbd. Start, verify, and enable the ndbd service.
$ sudo vi /etc/systemd/system/ndbd.service
<a>
[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
<esc>:wq, to save
Start the service:
$ sudo systemctl daemon-reload
$ sudo systemctl start ndbd.service
$ sudo systemctl status ndbd.service
< view output>
If there is an error, check the service file created above. If it is running okay, let's enable it to start on all reboots.
$ sudo systemctl enable ndbd.service
Set-up the MySQL Server and Client on the Management Node:
1. Install prerequisite packages. Then use wget to get the files directly from the dev.mysql.com repo, or download and transfer to the servers. Install with dpkg.
$ sudo apt-get update
$ sudo apt-get install libaio1 libmecab2
<wait>
$ cd ~/tmp/
$ mkdir mysqlinstall
<transfer the file or use wget>
$ tar -xvf mysql-cluster_8.0.21-1ubuntu20.04_amd64.deb-bundle.tar -C mysqlinstall/
$ cd mysqlinstall
Install the packages extracted from the tar file:
$ sudo dpkg -i mysql-common_8.0.21-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-cluster-community-client_8.0.21-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-client_8.0.21-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-cluster-community-server_8.0.21-1ubuntu18.04_amd64.deb
Note:
While installing the mysql-cluster-community-server, we'll be prompted to set the root password.
At the prompt, enter the password. Click OK, and re-enter the password again, and then hit OK again.
Install the MySQL binary next:
$ sudo dpkg -i mysql-server_8.0.21-1ubuntu18.04_amd64.deb
2. Append the following lines to the end of the local MySQL configuration.
$ sudo vi /etc/mysql/my.cnf
<a>
....
[mysqld]
# Options for mysqld process. Run the ndbcluster engine, and set the engine to default.
ndbcluster
storage_engine=NDBCLUSTER
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=198.51.100.2 # location of management server
<esc>:wq, to save
3. The deb installation likely already as the default MySQL instance running, so we need to restart it.
$ sudo systemctl restart mysql
Confirm running, and enable if not enabled.
$ sudo systemctl status mysql
<view output>
$ sudo systemctl enable mysql
Verify the MySQL Cluster Is Working
1. Login as the root SQL user and test the cluster via show engine.
$ mysql -u root -p
<enter the root password set above>
mysql> SHOW ENGINE NDB STATUS \G
(You should see something similar to the following)
*************************** 1. row ***************************
Type: ndbcluster
Name: connection
Status: cluster_node_id=4, connected_host=10.0.222.11, connected_port=1186, number_of_data_nodes=2, number_of_ready_data_nodes=2, connect_count=0
2. Create your database, tables, and verify the cluster is working okay.
mysql> CREATE DATABASE dbdeleteme;
mysql> USE dbdeleteme;
mysql> CREATE TABLE deleteme_table (name VARCHAR(20), value VARCHAR(20)) ENGINE=ndbcluster;
mysql> INSERT INTO deleteme_table (name,value) VALUES('Nicki','Amazing Person');
mysql> SELECT * FROM deleteme_table;
<we should see the Nicki - Amazing Person row returned>
mysql> DROP dbdeleteme;
mysql> quit;
previous page
|