This guide will teach you how to setup and configure MySQL Cluster on CentOS 7 servers.
We need 3 types of nodes to setup the complete cluster. They are management node, data node & SQL node.
* Management node/server is used to manage other servers/nodes in the cluster, to add new node in the cluster, start/restart/delete nodes in the cluster.
* Data node/server is the main storage server of the database files, it replicate databases with other data nodes. This is the database cluster. High memory and storage should be avail on data node servers.
* SQL node/server is the MySQL server/client which is used by application/php to connect to the database cluster, worked as API. Actually, SQL node is the gateway engine between application/php and database file.
Server Requirement:
In this guide we need two servers for each type of node, so we need totally 6 servers/vps.
For management server we will take one medium quality/strength vps for management node 1 and another one low quality/strength vps for management node 2, from two different location (suppose, node-1 from USA and node-2 from Germany).
For data server we will take 2 high quality dedicated servers with high memory and storage from two different location.
For SQL server we will take 2 medium quality vps from two different location.
The example IP addresses for this guide is as following: management node 1 = 192.168.1.101 management node 2 = 192.168.1.102 data node 1 = 192.168.1.103 data node 2 = 192.168.1.104 sql node 1 = 192.168.1.105 sql node 2 = 192.168.1.106
Open firewall port number 1186 on management servers, port 2200-2202 on data servers, port 3306 on SQL servers.
Management Node/Server Setup
Let’s start first with management node 1, login to SSH on management node 1, and start inserting commands.
yum -y update
yum -y install nano wget
yum -y install epel-release
yum -y install perl-Class-MethodMaker
yum -y install perl-Data-Dumper
yum -y install perl-DBI
yum -y remove mariadb-libs
Download the cluster package.
cd ~
wget http://cdn.mysql.com//Downloads/MySQL-Cluster-7.6/mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
Install the management server.
rpm -Uvh mysql-cluster-community-management-server-7.6.15-1.el7.x86_64.rpm
Install the dependencies for client.
rpm -Uvh mysql-cluster-community-common-7.6.15-1.el7.x86_64.rpm
rpm -Uvh mysql-cluster-community-libs-7.6.15-1.el7.x86_64.rpm
Now install the client.
rpm -Uvh mysql-cluster-community-client-7.6.15-1.el7.x86_64.rpm
Create a new configuration file for cluster management.
mkdir -p /var/lib/mysql-cluster
nano /var/lib/mysql-cluster/config.ini
Paste the below script in the new file (Replace IP addresses as yours).
[ndb_mgmd default] # Directory for MGM node log files DataDir=/var/lib/mysql-cluster [ndb_mgmd] #Management Node 1 HostName=192.168.1.101 NodeId=1 # Node ID for this management node [ndb_mgmd] #Management Node 2 HostName=192.168.1.102 NodeId=2 # Node ID for this management node [ndbd default] NoOfReplicas=2 # Number of replicas DataMemory=24G # Memory allocate for data storage IndexMemory=8G # Memory allocate for index storage #Directory for Data Node DataDir=/var/lib/mysql-cluster [ndbd] #Data Node 1 HostName=192.168.1.103 NodeId=3 # Node ID for this data node [ndbd] #Data Node 2 HostName=192.168.1.104 NodeId=4 # Node ID for this data node [mysqld] #SQL Node 1 HostName=192.168.1.105 NodeId=5 # Node ID for this sql node [mysqld] #SQL Node 2 HostName=192.168.1.106 NodeId=6 # Node ID for this sql node
Save and exit the file.
Start the management node.
ndb_mgmd --ndb-nodeid=1 --config-file=/var/lib/mysql-cluster/config.ini
The management server 1 setup is complete, now exit from the management server 1, and login to the management server 2 via SSH as root and proceed.
Start inserting commands on management node 2.
yum -y update
yum -y install nano wget
yum -y install epel-release
yum -y install perl-Class-MethodMaker
yum -y install perl-Data-Dumper
yum -y install perl-DBI
yum -y remove mariadb-libs
Download the cluster package.
cd ~
wget http://cdn.mysql.com//Downloads/MySQL-Cluster-7.6/mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
Install the management server.
rpm -Uvh mysql-cluster-community-management-server-7.6.15-1.el7.x86_64.rpm
On management node 2, we do not have to install the client and dependencies.
Create a new configuration file for cluster management.
mkdir -p /var/lib/mysql-cluster
nano /var/lib/mysql-cluster/config.ini
Paste the below script in the new file (Replace IP addresses as yours).
[ndb_mgmd default] # Directory for MGM node log files DataDir=/var/lib/mysql-cluster [ndb_mgmd] #Management Node 1 HostName=192.168.1.101 NodeId=1 # Node ID for this management node [ndb_mgmd] #Management Node 2 HostName=192.168.1.102 NodeId=2 # Node ID for this management node [ndbd default] NoOfReplicas=2 # Number of replicas DataMemory=24G # Memory allocate for data storage IndexMemory=8G # Memory allocate for index storage #Directory for Data Node DataDir=/var/lib/mysql-cluster [ndbd] #Data Node 1 HostName=192.168.1.103 NodeId=3 # Node ID for this data node [ndbd] #Data Node 2 HostName=192.168.1.104 NodeId=4 # Node ID for this data node [mysqld] #SQL Node 1 HostName=192.168.1.105 NodeId=5 # Node ID for this sql node [mysqld] #SQL Node 2 HostName=192.168.1.106 NodeId=6 # Node ID for this sql node
Save and exit the file.
Start the management node 2.
ndb_mgmd --ndb-nodeid=2 --config-file=/var/lib/mysql-cluster/config.ini
Now, exit/logout from management server 2, and login to management server 1 via SSH as root.
Check the management node.
ndb_mgm
show
exit
Both management server setup is now complete, now exit from the server and proceed to data node setup.
Data Node/Server Setup
Secondly, start setup the data node 1, login to SSH on data node 1, and start inserting commands.
yum -y update
yum -y install nano wget
yum -y remove mariadb-libs
Download the cluster package.
cd ~
wget http://cdn.mysql.com//Downloads/MySQL-Cluster-7.6/mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
Install the data server.
rpm -Uvh mysql-cluster-community-data-node-7.6.15-1.el7.x86_64.rpm
Create a new file.
nano /etc/my.cnf
Paste the below script to the file.
[mysqld] ndbcluster ndb-connectstring=192.168.1.101 # IP address of Management Node 1 ndb-connectstring=192.168.1.102 # IP address of Management Node 2 [mysql_cluster] ndb-connectstring=192.168.1.101 # IP address of Management Node 1 ndb-connectstring=192.168.1.102 # IP address of Management Node 2
Save and exit the file.
Create the database directory.
mkdir -p /var/lib/mysql-cluster
Now, start the data node.
ndbd
The data server 1 is now complete, now exit from data server 1, and login to data server 2 via SSH as root and repeat the above process (Data Node/Server Setup) to complete the data node setup on data server 2.
SQL Node/Server Setup
Thirdly, start setup the sql node 1, login to SSH on sql node 1, and start inserting commands.
yum -y update
yum -y install nano wget
yum -y install epel-release
yum -y install perl-Class-MethodMaker
yum -y install perl-Data-Dumper
yum -y install perl-DBI
yum -y remove mariadb-libs
Download the cluster package.
cd ~
wget http://cdn.mysql.com//Downloads/MySQL-Cluster-7.6/mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-cluster-community-7.6.15-1.el7.x86_64.rpm-bundle.tar
Install the dependencies for client.
rpm -Uvh mysql-cluster-community-common-7.6.15-1.el7.x86_64.rpm
rpm -Uvh mysql-cluster-community-libs-7.6.15-1.el7.x86_64.rpm
Install the client.
rpm -Uvh mysql-cluster-community-client-7.6.15-1.el7.x86_64.rpm
Now, install the sql server.
rpm -Uvh mysql-cluster-community-server-7.6.15-1.el7.x86_64.rpm
Create a new file.
nano /etc/my.cnf
Paste the below script to the file.
[mysqld] ndbcluster ndb-connectstring=192.168.1.101 # IP address of Management Node 1 ndb-connectstring=192.168.1.102 # IP address of Management Node 2 default_storage_engine=ndbcluster # Define default Storage Engine used by MySQL [mysql_cluster] ndb-connectstring=192.168.1.101 # IP address of Management Node 1 ndb-connectstring=192.168.1.102 # IP address of Management Node 2
Save and exit the file.
Create the directory.
mkdir -p /var/lib/mysql-cluster
Start the MySQL.
service mysql start
The sql server 1 is now complete, now exit from sql server 1, and login to sql server 2 via SSH as root and repeat the above process (SQL Node/Server Setup) to complete the sql node setup on sql server 2.
We are doing great ! it’s time to check the cluster status.
SSH login to management node 1, and check the cluster.
ndb_mgm
show
exit
We can create a test database now to finish configuring the cluster.
Finishing Task
SSH login to SQL node 1, and prepare MySQL.
cd ~
Get the existing MySQL password.
cat .mysql_secret
Now keep the existing password handy, and change the default password.
mysql_secure_installation
After changed password, login to MySQL shell with your new password.
mysql -u root -p
After logged in, create a root user to access MySQL outside.
CREATE USER 'root'@'%' IDENTIFIED BY 'x7fk3y9dQb';
Replace x7fk3y9dQb with your own secure password.
Now, see the root user and obtain the encrypted password, then keep the encrypted password handy.
select user, host, password from mysql.user;
Set the access privileges to root user with the encrypted password.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxxx...(Encrypted PASSWORD)' WITH GRANT OPTION;
Finally, create a database on sql node 1, and you should see the database also copied automatically to sql node 2.
Repeat the finishing configuration on sql node 2 also.
Enjoy MySQL Cluster !
Leave A Comment