How to 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.

In this tutorial, I will guide you trough the installation and configuration of a MySQL Cluster with centOS 7. We will configure the management node, two data nodes, and two SQL nodes.

Prerequisites

  • The OS is CentOS 7 - 64bit.
  • 5 CentOS servers or virtual machines. I will use the hostnames and IP addresses as shown below:
    • Management Node
      db1 = 192.168.1.120
    • Data Nodes
      db2 = 192.168.1.121
      db3 = 192.168.1.122
    • SQL Nodes
      db4 = 192.168.1.123
      db5 = 192.168.1.124

Step 1 - Setup Management Node

The first step is to create the "Management Node" with CentOS 7 db1 and IP 192.168.1.120. Make sure you are logged into the db1 server as root user.

A. Download the MySQL Cluster software

I'll download it from the MySQL site with wget. I'm using the "Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit), RPM Bundle " here which is compatible with CentOS 7. Then extract the tar 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

Install MySQL Cluster package rpm.

B. Install and Remove Packages

Before you install the rpm package for MySQL Cluster, you need to install perl-Data-Dumper that is required by the MySQL-Cluster server. And you need to remove 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 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. Configure MySQL Cluster

Create a new directory for the configuration files. I will use the "/var/lib/mysql-cluster" directory.

mkdir -p /var/lib/mysql-cluster

Then create new configuration file for the cluster management named "config.ini" in the mysql-cluster directory.

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

Paste the configuration below:

[ndb_mgmd default]
# Directory for MGM node log files
DataDir=/var/lib/mysql-cluster
 
[ndb_mgmd]
#Management Node db1
HostName=192.168.1.120
 
[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.121
 
[ndbd]
#Data Node db3
HostName=192.168.1.122
 
[mysqld]
#SQL Node db4
HostName=192.168.1.123
 
[mysqld]
#SQL Node db5
HostName=192.168.1.124

Save the file and exit.

E. Start the Management Node

Next start the management 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.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

Check cluster status.

You can see the management node has been started with: mysql-6.6 and ndb-7.4.

Step 2 - Setup the MySQL Cluster Data Nodes

We will use 2 CentOS servers for the Data Nodes.

  1. db2 = 192.168.1.121
  2. db3 = 192.168.1.122

A. Login as root user and download the MySQL Cluster software

Login to the db2 server with 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. Install and Remove Packages

Install perl-Data-Dumper and remove the mariadb-libs:

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

C. Install MySQL Cluster

Now we can install the MySQL Cluster packages for the Data Nodes with 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. Configure Data Node

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

vi /etc/my.cnf

Paste configuration below:

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

Save the file and exit.

Then create the new directory for the database data that we defined in the management node config file "config.ini".

mkdir -p /var/lib/mysql-cluster

Now start the data node/ndbd:

ndbd

results:

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

MySQL cluster node is online.

Data Node db2 connected to the management node ip 192.168.1.120.

E. Redo step 2.A - 2.D on db3 server.

As we have 2 data nodes, please redo the steps 2.A - 2.D on our second data node.

Step 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:

  1. db4 = 192.168.1.123
  2. db5 = 192.168.1.124

A. Log in and Download MySQL Cluster

Login to the db4 server as root user:

ssh [email protected]

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

B. Install and Remove Packages

Install perl-Data-Dumper and remove the mariadb-libs that conflict with MySQL Cluster.

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

C. Install MySQL Cluster

Install the MySQL Cluster server, client and shared package with the rpm commands 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. Configure the SQL Node

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

vi /etc/my.cnf

And paste configuration below:

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

Save the file and exit the editor.

Start the SQL Node by starting the MySQL server:

service mysql start

E. Redo step 3.A - 3.D on db5 server.

Please redo the steps 3.A - 3.D on the second SQL server (db5).

Step 4 - Monitor the Cluster

To see the cluster status, we have to log into the management node db1.

ssh [email protected]

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

ndb_mgm
ndb_mgm> show

Check the NDB clsuter state.

Another useful command is:

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

Step 5 - Testing the Cluster

To perform a test on our new MySQL Cluster, we have to login to the SQL Nodes db4 or db5 servers.

Login to the db4 server:

ssh [email protected]

Change the default MySQL password that stored in ".mysql_secret" file in root directory:

cd ~
cat .mysql_secret

this is my sample:

# 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 'aqwe123';

Replace aqwe123 with your own secure password! Now you can see the new root user with host "@" on the MySQL user list:

select user, host, password from mysql.user;

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

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

Grant priveliges.

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

This is just a sample result for testing the cluster data replication.

All nodes are online.

The MySQL Cluster has been setup successfully on CentOS 7 with 5 server nodes.

Conclusion

MySQL Cluster is a technology that provides High Availability and Redundancy for MySQL databases. It uses NDB or NDBCLUSTER as the storage engine and provides shared-nothing clustering and auto-sharding for MySQL databases.  To implement the cluster, we need 3 components: Management Node(MGM), Data Nodes (NDB) and SQL Nodes (API). Each of node must have its own memory and disk. It is not recommended to use network storage such as NFS. To install MySQL Cluster on a CentOS 7 minimal system, we have to remove the mariadb-libs package, mariadb-libs conflict with MySQL-Cluster-server and you have to install the perl-Data-Dumper package, it's needed by MySQL-Cluster-server. A MySQL Cluster is easy to install and configure on multiple CentOS servers.

Share this page:

Suggested articles

25 Comment(s)

Add comment

Comments

By: Krishna C
Reply  

Great walk through. What will happen if management fails. Can we build redundancy  for Management node.

By: Natsume
Reply  

You can use Haproxy as load balancer between MySQL server.

By: Bala
Reply  

HAProxy is too slow for MySQL Cluster when connected from application. Kindly suggest some configuration parameters which could help in making the performance better. 

By: Mikael Ronström
Reply  

If the management server fails the cluster will continue to work, it will however not be possible to restart a node since the management server is needed to startup the data nodes and mysql server nodes in MySQL Cluster. It is possible to have multiple management nodes. Management server also gets a lot of logging messages from other nodes which it reports in the cluster log (each node also reports log events in its own node log).

By: HIll
Reply  

I follow your toturial,but after the mysql service start,the management node cannot connect with the sql node.

By: Hill
Reply  

Thank you, I fogot to close the firewall.

By: Eacu
Reply  

well the best way is add a port in firewall, not close the firewall, I did and it worked.

By: Jeryl Cook
Reply  

Hi, great article...dumb question: performing an insert/update to the SQL nodes(slave nodes d4 or d5), the data is replicated guaranteed consistent across all configured slave nodes(on future reads?)

By: Urgen Sherpa
Reply  

Great how to's. I have 30GB each physical memory in 2 datanodes  and SSD 200GB each . but the database size(cumulative size of multiple db's) is around 55GB can I migrate my mysql database(myisam/innodb mix tables) to ndb cluster 

By: Aun
Reply  

Great article, my cluster is up and running now.A dumb question, to which server i need to point my code to ? I have a PHP site that will be connecting to mysql instance here, so which server i should be using in connection string? db3, db4?

By: Ivodio
Reply  

Excellent

By: Jitender
Reply  

Nice article, however i have some questions here. In my environment we have almost 1.5 TB of data and going for cluster. Now we can not fit all the 1.5 TB of data in memory. I just want to know how can we configure our data nodes or cluster so that my data files can reside on disk instead of memory.

I have also gone through some of links and per them, we need to create log file group and then create data files. Could you please provide some configurations link on this.

Thanks,

Jitender

By: Arjun
Reply  

https://www.howtoforge.com/tutorial/how-to-install-and-configure-mysql-cluster-on-centos-7/

This tutorial not connecting Mysql API, I am  working with centos 7.2.

 

By: Dylan
Reply  

You'll need to either disable SELinux, or more properly configure it as described in this blog post:

https://blogs.oracle.com/jsmyth/entry/connection_failures_between_nodes_in

By: Leesang
Reply  

how to turn on tool to take picture that is as same as the above picture ??? i try to find solution but it is not ok.

By: Mahindra Varma
Reply  

I had followed all steps as defined above. Here i used to physical machine 4.115(Datanode1) and 4.116(Mysql node1) and install vm in each host machine 4.118(Datanode2) and 4.119(Mysql2) and one more VM in my machine 4.117(Management node).

After running ndb_mgm and show , Below is the output:

Connected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)]     2 node(s)id=2    @192.168.4.115  (mysql-5.6.28 ndb-7.4.10, starting, Nodegroup: 0)id=3 (not connected, accepting connect from 192.168.4.116)[ndb_mgmd(MGM)] 1 node(s)id=1    @192.168.4.117  (mysql-5.6.28 ndb-7.4.10)[mysqld(API)]   2 node(s)id=4 (not connected, accepting connect from 192.168.4.116)

id=5 (not connected, accepting connect from 192.168.4.115)

 

And My Config file is:

 

[ndb_mgmd default]

# Directory for MGM node log filesDataDir=/var/lib/mysql-cluster[ndb_mgmd]#Management Node mgmHostName=192.168.4.117[ndbd default]NoOfReplicas=2      # Number of replicasDataMemory=256M     # Memory allocate for data storageIndexMemory=128M    # Memory allocate for index storage#Directory for Data NodeDataDir=/var/lib/mysql-cluster[ndbd]#Data Node node1HostName=192.168.4.115[ndbd]#Data Node node3HostName=192.168.4.118[mysqld]#SQL Node node2HostName=192.168.4.116[mysqld]#SQL Node node5HostName=192.168.4.119

From One Data node i am getting success message as:

2016-11-23 03:25:20 [ndbd] INFO     -- Angel connected to '192.168.4.117:1186'2016-11-23 03:25:20 [ndbd] INFO     -- Angel allocated nodeid: 2

But from other one:

ndbd2016-11-23 03:41:33 [ndbd] INFO     -- Angel connected to '192.168.4.117:1186'2016-11-23 03:41:33 [ndbd] ERROR    -- Failed to allocate nodeid, error: 'Error: Could not alloc node id at 192.168.4.117 port 1186: Connection done from wrong host ip 192.168.4.118.'

 

Can you please help me out , I am not able to figure it out.

 

By: Vamsi Vakiti
Reply  

Thanks for the great post. I was able to create the cluster very quickly in no time.

However, I see 2 data nodes are in starting status. I tried bringing them up ndbd command by ssh'ing into those nodes.

And the status of sql nodes are (not connected, accepting connect from node4 & node5)

I researched about this error "Failed to allocate nodeid, error: 'Error: Could not alloc node id" and could not find anything useful.

Any help on this would be much appreciated.

By: sanjib
Reply  

Thanks a lot for this beautiful document. But still having issue in restoring a backup file in this cluster environment. SO please help me. backup file size is around 2GB.

Also Please share the document to configure MySQL Cluster GPL 7.5.

 

Thanks a lot again.

By: Nitish Moleshwari
Reply  

is mysql cluster gpl comes with the free licence?

By: faycal
Reply  

hello, 

the cluster works well, but i found out that my users created on my SQL Node 1 does't replicate into the SQL Node 2,

i didn't found why 

regards 

By: Eddie
Reply  

How did you add that GUI interface to this installation?

 

Please help Thank you!!

By: Rakesh
Reply  

Hi,

I am getting error when starting mysql server.

I had same my.cnf file as you have.

Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/gateway2.rev.com.pid).

By: Andrew H
Reply  

What is the password for root in step 5.

"Type your old mysql password and then type the new one, press enter to confirm all."

By: Luciano Gomes
Reply  

please what if i want to use two ndb_mgmd (MGM) how do i do?

By: Sain Abraham
Reply  

I had followed this article. In the Data node, I got - Angel connected. MySQL was also started. But in the management node, it is showing as not connected.