MySQL

Install and Configure MySQL Cluster on CentOS 7

MySQL Cluster is designed to provide a MySQL compatible database with high availability and low latency. The MySQL Cluster technology is implemented through the NDB (Network DataBase) and NDBCLUSTER storage engines and provides shared-nothing clustering and auto-sharding for MySQL database systems. In the shared-nothing architecture, each of 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 have to install three types of nodes. Each node type will be installed on it’s own server. The components are:

1. Management Node – NDB_MGMD/MGM
The Cluster management server is used to manage the other node of the cluster. We can create and configure new nodes, restart, delete, or backup nodes on the cluster from the management node.

2. Data Nodes – NDBD/NDB
This is the layer where the process of synchronizing and data replication between nodes happens.

3. SQL Nodes – MySQLD/API
The interface servers that are used by the applications to connect to the database cluster.

Prerequisites
The OS is CentOS 7 – 64bit.

Management Node
db1 = 172.26.87.42
Data /SQL Nodes
db2 = 172.26.87.40
db3 = 172.26.87.41

Step 1 – Setup Management Node

yum -y install perl-Data-Dumper
yum -y remove mariadb-libs
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
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 🙂

Configure MySQL Cluster

mkdir -p /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi config.ini
[ndb_mgmd default]
# Directory for MGM node log files
DataDir=/data
 
[ndb_mgmd]
#Management Node db1
HostName=172.26.87.42
 
[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=/data
 
[ndbd]
#Data Node db1
HostName=172.26.87.41
 
[ndbd]
#Data Node db2
HostName=172.26.87.40
 
[mysqld]
#SQL Node db1
HostName=172.26.87.41
 
[mysqld]
#SQL Node db2
HostName=172.26.87.40

Start the Management Node

ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.10
2016-03-22 19:26:08 [MgmtSrvr] INFO     -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2016-03-22 19:26:08 [MgmtSrvr] INFO     -- Successfully created config directory

The management node is started, now you can use command “ndb_mgm” to monitor the node:

ndb_mgm
show

Step 2 – Setup the MySQL Cluster Data Nodes

We will use 2 CentOS servers for the Data Nodes.

db2 = 172.26.87.41
db3 = 172.26.87.40

Login to the db2 server with ssh.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

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

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

Configure Data Node

vi /etc/my.cnf
[mysqld]
ndbcluster
ndb-connectstring=172.26.87.42     # IP address of Management Node
 
[mysql_cluster]
ndb-connectstring=172.26.87.42     # IP address of Management Node

Save the file and exit.

mkdir -p /var/lib/mysql-cluster
ndbd

2016-03-22 19:35:56 [ndbd] INFO     -- Angel connected to '172.26.87.42:1186'
2016-03-22 19:35:56 [ndbd] INFO     -- Angel allocated nodeid: 2

Redo step 2.A – 2.D on db3 server.

3 – Setup SQL Node
This is step contains the setup for the SQL Node that provides the application access to the database. We use 2 CentOS servers for the SQL Nodes:

db4 = 172.26.87.41
db5 = 172.26.87.42

Login to the db2 server as root user And download 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
yum -y install perl-Data-Dumper
yum -y remove mariadb-libs
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

Configure the SQL Node

vi /etc/my.cnf
[mysqld]
ndbcluster
ndb-connectstring=172.26.87.42      # IP address for server management node
default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL
 
[mysql_cluster]
ndb-connectstring=172.26.87.42      # IP address for server management node

Save the file and exit the editor.

service mysql start

Redo step 3.A – 3.D on db3 server.

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

ndb_mgm
ndb_mgm> show

Another useful command is:

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

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

cd ~
cat .mysql_secret
# The random password set for the root user at Tue Mar 22 19:44:07 2016 (local time): qna3AwbJMuOnw23T

Now change the password with command below:

mysql_secure_installation

Type your old mysql password and then type the new one, press enter to confirm all.
If all is done, you can login to the MySQL shell with your password:

mysql -u root -p
After you logged in, create a new root user with host "@", so we will be able to access the MySQL from outside.
CREATE USER 'root'@'%' IDENTIFIED BY 'prabath@123';
select user, host, password from mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '[Encrypted PASSWORD]' WITH GRANT OPTION;

Leave a Reply

Your email address will not be published. Required fields are marked *