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;
