CentOS 6: Install MySQL Cluster – The Simple Way
MySQL cluster is consists of 3 types of node:
1.Data node (mysql-data1 & mysql-data2)
2.Sql daemon node (mysql-mysqld1 & mysql-mysqld2)
3.Management node (mysql-management)
Data nodes
Data node will hold the database and it will replicate automatically to all data nodes.
Sql Nodes
Sql daemon node is the interface between database and client.
It will serve the query they got from data nodes, similar like a “gateway”.
Management node
Management node is required in order to monitor and manage the whole cluster.
Recommended minimum setup for high availability and scalability will be 5 servers as what I have highlights in the picture below.
I will be using CentOS 6.3 64bit for all servers.
All Servers
1.SELINUX must be disabled on all servers. Change the SELINUX configuration file at /etc/sysconfig/selinux:
2.Firewall is disabled on all servers:
$ service iptables stop
$ chkconfig iptables off
$ setenforce 0
3. Entries under /etc/hosts for all servers should be as below:
web-server 192.168.1.21
mysql-mysqld1 192.168.1.51
mysql-mysqld2 192.168.1.52
mysql-management 192.168.1.53
mysql-data1 192.168.1.54
mysql-data2 192.168.1.55
Management Node
1. Download and install MySQL Cluster (management & tools) package from here:
$ cd /usr/local/src
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL- Cluster-gpl-management-7.0.35-1.rhel5.x86_64.rpm
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL- Cluster-gpl-tools-7.0.34-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-management-7.0.35-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-tools-7.0.34-1.rhel5.x86_64.rpm
2. Create the mysql-cluster directory and configuration file config.ini:
$ mkdir -p /var/lib/mysql-cluster
$ vim /var/lib/mysql-cluster/config.ini
And add following line:
[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster
[ndb_mgmd]
HostName=mysql-management
[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/var/lib/mysql-cluster
[ndbd]
HostName=mysql-data1
[ndbd]
HostName=mysql-data2
[mysqld]
HostName=mysql-mysqld1
[mysqld]
HostName=mysql-mysqld2
Data Nodes
1. Following steps should be executed on both data nodes (mysql-data1 and mysql-data2). Download and install the MySQL storage package from here:
$ cd /usr/local/src
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-storage-7.0.35-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-storage-7.0.35-1.rhel5.x86_64.rpm
2. Add following line under /etc/my.cnf:
[mysqld]
ndbcluster
ndb-connectstring=mysql-management
[mysql_cluster]
ndb-connectstring=mysql-management
SQL Nodes
1.Following steps should be executed on both SQL nodes (mysql-mysqld1 and mysql-mysqld2). Remove mysql-libs using yum:
$ yum remove mysql-libs –y
2. Install required package using yum:
$ yum install libaio –y
3. Download the MySQL client, shared and server package from MySQL download site here:
$ cd /usr/local/src
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-client-gpl-7.2.8-1.el6.x86_64.rpm/
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-shared-gpl-7.2.8-1.el6.x86_64.rpm/
$ wget http://www.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-server-gpl-7.2.8-1.el6.x86_64.rpm/
4. Install all packages:
rpm -Uhv MySQL-Cluster-*
5. Add following line into /etc/my.cnf:
[mysqld]
ndbcluster
ndb-connectstring=mysql-management
default_storage_engine=ndbcluster
[mysql_cluster]
ndb-connectstring=mysql-management
Start the Cluster
1.To start the cluster, we must follow this order:
Management Node > Data Node > SQL Node
2.So, login to management node (mysql-management) and execute following command:
$ ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.63 ndb-7.0.35
2012-11-22 07:36:55 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2012-11-22 07:36:55 [MgmtSrvr] INFO -- Sucessfully created config directory
3.Next is start the ndbd service in Data Node (mysql-data1):
$ ndbd
2012-11-22 07:37:24 [ndbd] INFO -- Angel connected to 'mysql-management:1186'
2012-11-22 07:37:24 [ndbd] INFO -- Angel allocated nodeid: 2
4.Next is start the ndbd service in Data Node (mysql-data2):
$ ndbd
2012-11-22 07:37:24 [ndbd] INFO -- Angel connected to 'mysql-management:1186'
2012-11-22 07:37:24 [ndbd] INFO -- Angel allocated nodeid: 3
5.Next is start the mysql service in SQL node (mysql-mysqld1):
service mysql start
6.Next is start the mysql service in SQL node (mysql-mysqld2):
service mysql start
Monitor the Cluster
Monitoring the cluster will require you to login into management server. To check overall status of cluster:
$ ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.54 (mysql-5.1.63 ndb-7.0.35, Nodegroup: 0, Master)
id=3 @192.168.1.55 (mysql-5.1.63 ndb-7.0.35, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.53 (mysql-5.1.63 ndb-7.0.35)
[mysqld(API)] 2 node(s)
id=4 @192.168.1.51 (mysql-5.5.27 ndb-7.2.8)
id=5 @192.168.1.52 (mysql-5.5.27 ndb-7.2.8)
To check the Data nodes status:
$ ndb_mgm -e "all status"
Connected to Management Server at: localhost:1186
Node 2: started (mysql-5.1.63 ndb-7.0.35)
Node 3: started (mysql-5.1.63 ndb-7.0.35)
To check the memory usage of data nodes:
$ ndb_mgm -e "all report memory"
Connected to Management Server at: localhost:1186
Node 2: Data usage is 0%(23 32K pages of total 8192)
Node 2: Index usage is 0%(20 8K pages of total 16416)
Node 3: Data usage is 0%(23 32K pages of total 8192)
Node 3: Index usage is 0%(20 8K pages of total 16416)
Stopping the Cluster
1.To stop the cluster, we must follow this order:
SQL Node > Management Node / Data Node
2.Login to SQL node (mysql-mysqld1 & mysql-mysqld2) and run following command:
$ service mysql stop
3.Login to management node (mysql-management) and run following command:
$ ndb_mgm -e shutdown
Done! You should now able to create or import database on one of the SQL node. You can put a load balancer in front of the SQL node to take advantage on the performance and high availability.
Notes
You may notice that the distribution version I installed is “rhel5?. You can get the “el6” distribution package at this page: http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.2/ and search for any “el6? package name.
Reference
http://blog.secaserver.com/2012/11/centos-6-install-mysql-cluster-the-simple-way/
