How to install and configure a MySQL server cluster on CentOS 7

Title

  1. Prerequisites
  2. Step 1 – Configuring the Management Node
    1. A. Downloading MySQL Cluster Software
    2. B. Installing and removing packages
    3. C. Installing MySQL Cluster
    4. D. Setting up a MySQL cluster
    5. E. Start control unit
  3. Step 2 – Configuring in MySQL Cluster Data Nodes
    1. A. Login as root user and download the MySQL cluster software
    2. B. Installing and removing packages
    3. C. Install MySQL Cluster
    4. D. Configuring the data node
    5. E. Repeat step 2.A – 2.D on the db3 server.
  4. Step 3 – Configuring SQL Node
    1. A. Login and Boot MySQL Cluster
    2. B. Installing and removing packages
    3. C. Installing the MySQL Cluster
    4. D. Setting up SQL Node
    5. E. Repeat step 3.A – 3.D on the DB5 server.
  5. Step 4 – Cluster Monitor
  6. Step 5 – Testing the Cluster
  7. Output

MySQL Cluster is designed to provide a consistent MySQL database with high availability and low latency. The MySQL cluster technology is implemented through the NDB (Network DataBase) and NDB cluster storage engines and provides nonshared clustering and auto-sharding for MySQL database systems. In a nonshared architecture, each of the nodes has its own memory and disk, the use of shared storage such as NFS, SANs is not recommended and supported.

To implement a MySQL cluster, we need to install three types of nodes. Each node type will be installed on its own server. Components:

1. Management Node – NDB_MGMD / MGM
The cluster management server is used to manage another node in the cluster. We can create and configure new nodes, reboots, delete or backup nodes in the cluster from the management node.

2. HaveEvil Data – NDBD / NDB
This is the layer where the process of synchronization and replication of data between nodes takes place.

3. SQL Nodes – MYSQLD / API
Front-end servers that applications use to connect to the database cluster.

In this tutorial, I will walk you through the installation and configuration of a MySQL cluster on CentOS 7. We will set up a management node, two data nodes, and two SQL nodes.

Prerequisites

  • CentOS 7 is 64-bit.
  • 5 CentOS servers or virtual machines. I will use hostnames and IP addresses as shown below:
    • Control unit
      DB1 = 192.168.1.220
    • These nodes
      db2 = 192.168.1.221 db3 = 192.168.1.222
    • SQL nodes
      db4 = 192.168.1.223 DB5 = 192.168.1.224

Step 1 – Configuring the Management Node

The first step is to create a “Management Node” with CentOS 7 DB1 and IP 192.168.1.220. Make sure you are logged into db1 server as root user.

A. Download MySQL Cluster Software

I will be downloading it from MySQL site using wget. I am using here “Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86 64-bit) RPM Bundle” which is compatible with CentOS 7. Then extract the zip file.

cd ~
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

B. Installing and removing packages

Before installing the MySQL Cluster package, you need to install the Perl-Data-Dumper, which is required by the MySQL cluster server. And you need to uninstall MariaDB-LIBS before we can install MySQL Cluster.

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs

C. Install MySQL Cluster

Install MySQL Cluster package with rpm command:

cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Make sure there is no error.

D. Setting up MySQL Cluster

Create a new directory for config files. I will be using the directory “/ var / lib / mysql-cluster“.

mkdir -p /var/lib/mysql-cluster

Then create a new configuration file for cluster management called “config.ini” in the MySQL cluster directory.

cd /var/lib/mysql-cluster
vi config.ini

Paste in the following configuration:

[ndb_mgmd default]
# Directory for MGM node log files
DataDir=/var/lib/mysql-cluster
 
[ndb_mgmd]
#Management Node db1
HostName=192.168.1.220
 
[ndbd default]
NoOfReplicas=2      # Number of replicas
DataMemory=256M     # Memory allocate for data storage
IndexMemory=128M    # Memory allocate for index storage
#Directory for Data Node
DataDir=/var/lib/mysql-cluster
 
[ndbd]
#Data Node db2
HostName=192.168.1.221
 
[ndbd]
#Data Node db3
HostName=192.168.1.222
 
[mysqld]
#SQL Node db4
HostName=192.168.1.223
 
[mysqld]
#SQL Node db5
HostName=192.168.1.224

Save the file and exit.

E. Start Control Node

Next, start the control node with the command below:

ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini

The result should be similar to this:

MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.102016-10-08 18:26:05 [MgmtSrvr] INFO – The default config directory ‘/ usr / mysql-cluster’ does not exist. Trying to create it… 2016-10-08 18:26:05 [MgmtSrvr] INFO – Successfully created config directory

The management node starts up, you can now use the ndb_mgm command to monitor the node:

ndb_mgm
show

Start control node

You can see that the control node was started with: MySQL-6.6 and ndb-7.4.

Step 2 – Configuring in MySQL Cluster Data Nodes

We will be using 2 CentOS servers for data nodes.

  1. db2 = 192.168.1.221
  2. db3 = 192.168.1.222

A. Log in as root user and download the MySQL cluster software

Login to the DB2 server using SSH:

ssh [email protected]

Then download the MySQL Cluster package and extract it:

cd ~
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

B. Installing and removing packages

Installing Perl-Data-Dumper and uninstalling MariaDB-LIBS:

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs

C. Install MySQL Cluster

We can now install MySQL cluster packages for data nodes using these rpm commands:

cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Make sure there is no error.

D. Configuring the data node

Create a new configuration file in the / etc directory using the vi editor:

vi /etc/my.cnf

Insert the following configuration:

[mysqld]
ndbcluster
ndb-connectstring=192.168.1.220     # IP address of Management Node
 
[mysql_cluster]
ndb-connectstring=192.168.1.220     # IP address of Management Node

Save the file and exit.

Next, create a new directory for the database data that we defined in the control node configuration file “config.ini”.

mkdir -p /var/lib/mysql-cluster

Now start the data node / NDBD:

ndbd

Results:

2016-10-08 19:35:56 [ndbd] INFO – Angel connected to ‘192.168.1.220:1186’2016-10-08 19:35:56 [ndbd] INFO – Angel allocated nodeid: 2

Data node configuration

The DB2 Data Node is connected to Intellectual Property Node 192.168.1.220.

E. Repeat step 2.A – 2.D on the db3 server.

Since we have 2 data nodes, please repeat steps 2.A – 2.d on our second data node.

Step 3 – Configuring SQL Node

This step contains the settings for the SQL Node, which gives the application access to the database. We are using 2 CentOS servers for SQL nodes:

  1. DB4 = 192.168.1.223
  2. DB5 = 192.168.1.224

A. Login and download MySQL Cluster

Log in to the db4 server as root user:

ssh [email protected]

And download the MySQL Cluster package:

cd ~
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

B. Installing and removing packages

Install perl-Data-Dumper and remove mariadb-libs to avoid conflict with MySQL Cluster.

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs

C. Install MySQL Cluster

Install MySQL Cluster server, client and package using the rpm command below:

cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

D. Setting up SQL Node

Create a new my.cnf file in the / etc directory:

vi /etc/my.cnf

Copy the configuration below:

[mysqld]
ndbcluster
ndb-connectstring=192.168.1.220       # IP address for server management node
default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL
 
[mysql_cluster]
ndb-connectstring=192.168.1.220       # IP address for server management node

Save the file and exit the editor.

Start SQL Node by starting the MySQL server:

service mysql start

E. Repeat step 3.A – 3.D on the DB5 server.

Please repeat steps 3.A – 3.D on the second SQL server (DB5).

Step 4 – Monitoring the Cluster

To see the status of the cluster, we must log into DB1 on the management node.

ssh [email protected].168.1.120

We can use the ndb_mgm command to see the cluster status:

ndb_mgm
ndb_mgm> show

Cluster monitoring

Another useful command:

ndb_mgm -e "all status"
ndb_mgm -e "all report memory"

Step 5 – Testing the Cluster

In order to run the test on our new MySQL Cluster, we must enter db4 or db5 of the SQL servers nodes.

Login to db4 server:

ssh [email protected]

Change in MySQL the default password stored in the “.mysql_secret” file in the root directory:

cd ~
cat .mysql_secret

this is my example:

# The random password set for the root user at Tue Mar 21 19:44:07 2016 (local time): qna4AwbJOuOnw13T

Now change your password using the command below:

mysql_secure_installation

Enter the old MySQL password and then enter the new one, press Enter to confirm.

If everything is done, you can enter the MySQL shell with the password:

mysql -u root -p

Once you are logged in, create a new root user for host “@” so that we can access MySQL from outside.

CREATE USER 'root'@'%' IDENTIFIED BY 'andreyex123';

Replace andreyex123 with your own strong password! Now you can see the new root user “@” in the MySQL user list:

select user, host, password from mysql.user;

And grant the new root user privileges: read and write access from the remote host:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*94CC7BF027327993D738E11...(Encrypted PASSWORD)' WITH GRANT OPTION;

Cluster testing

Now try to create a new database from db4 server and you will see a database on DB5 too.

This is just a sample for testing replication of cluster data.

MySQL Cluster has been successfully installed on CentOS 7 with 5 server nodes

MySQL Cluster has been successfully installed on CentOS 7 with 5 server nodes

Output

MySQL Cluster is a technology that provides high availability and redundancy for MySQL databases. It uses NDB or NDBCLUSTER as its storage engine and provides nonshared clustering and auto-sharding for MySQL databases. To implement a cluster, we need 3 components: Management Node (MGM), Data Nodes (NDB) and SQL Nodes (API). Each of the nodes must have its own memory and disk. It is not recommended to use network attached storage such as NFS. To install MySQL Cluster on a minimal CentOS 7 system, we have to remove the MariaDB-LIBS package, MariaDB-LIBS conflicts with the MySQL cluster server, and you must install the Perl-Data-Dumper package, this is required for the MySQL server cluster. MySQL cluster is easy to install and configure on multiple CentOS servers.

Sidebar