Installing a MySQL Cluster Binary Release
on Linux
The process of installing and configuring two nodes MYSQL cluster on oracle
Linux 5.5
Operating System oracle Linux 5.5 (32-bit, installed using
“Basic Server” and without any database)
Cluster
Planning
·
Data
Nodes 02
·
Management Node 01
Setting up
the Cluster
To set up the cluster, we need three servers. Two cluster nodes and one
Management node. I should point out that the Management node is not required
after the cluster install, but I strongly recommend keeping it as it gives you
the automatic failover capability. I will use three servers as examples:
Server1 192.168.100.1
(Cluster Management Server)
Server2 192.168.100.2 (Data Node 1)
Server3 192.168.100.3 (Data Node 2)
Server2 192.168.100.2 (Data Node 1)
Server3 192.168.100.3 (Data Node 2)
First step
is to install MySQL Cluster Management Server on Server1
Let’s download MySQL Cluster from http://dev.mysql.com/downloads/cluster/
#mkdir /usr/src/mysql-mgm
#cd /usr/src/mysql-mgm
#wget pick up any mirror from MySQL's website
#tar xvfz mysql-cluster-gpl-6.2.15-linux-i686-glibc23.tar.gz
#cd mysql-cluster-gpl-6.2.15-linux-i686-glibc23
#mv bin/ndb_mgm /usr/bin
#mv bin/ndb_mgmd /usr/bin
#chmod 755 /usr/bin/ndb_mg*
#cd /usr/src
#rm -rf /usr/src/mysql-mgm
Next step is to create the Cluster configuration file:
#mkdir /var/lib/mysql-cluster
#cd /var/lib/mysql-cluster
#vi config.ini
Here is the sample configuration file:
[NDBD DEFAULT] NoOfReplicas=2 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] # Section for the cluster management node [NDB_MGMD] # IP address of the management node (server1) HostName=192.168.100.1 # Section for the storage nodes [NDBD] # IP address of the first data node (Server2) HostName=192.168.100.2 DataDir= /var/lib/mysql-cluster [NDBD] # IP address of the second storage node (Server3) HostName=192.168.100.3 DataDir=/var/lib/mysql-cluster # one [MYSQLD] per storage node [MYSQLD] [MYSQLD]
Now let's start the Management Server:
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Now, we would want to start the Management Server automatically in case
of a system reboot, so we add an init script to do that:
echo 'ndb_mgmd -f /var/lib/mysql-cluster/config.ini' > /etc/init.d/ndb_mgmd
chmod 755 /etc/init.d/ndb_mgmd
update-rc.d ndb_mgmd defaults
Data Nodes MySQL
Nodes Configuration (Server2 and Server3):
Now let's set up the data nodes. Here are the steps to do that (do on
both data nodes):
#groupadd mysql
#useradd -g mysql mysql
#cd /usr/local/
#wget pick up any mirror from MySQL's website
#tar xvfz mysql-cluster-gpl-6.2.15-linux-i686-glibc23.tar.gz
#ln -s mysql-cluster-gpl-6.2.15-linux-i686-glibc23 mysql
#cd mysql
#scripts/mysql_install_db --user=mysql
#chown -R root:mysql .
#chown -R mysql data
#cp support-files/mysql.server /etc/init.d/
#chmod 755 /etc/init.d/mysql.server
#update-rc.d mysql.server defaults
#cd /usr/local/mysql/bin
#mv * /usr/bin
#cd ../
#rm -fr /usr/local/mysql/bin
#ln -s /usr/bin /usr/local/mysql/bin
Next we need to create the MySQL configuration file /etc/my.cnf on both
nodes:
#vim /etc/my.cnf
Here is the sample file:
[mysqld]
ndbcluster
# IP address of the cluster management server (Server1)
ndb-connectstring=192.168.100.1
[mysql_cluster]
# IP address of the cluster management Server (Server1)
ndb-connectstring=192.168.100.1
Our MySQL installation is almost complete, now let's create the data
directories and start the MySQL Server on both nodes: (Server2 and Server3)
#mkdir /var/lib/mysql-cluster
#cd /var/lib/mysql-cluster
#ndbd --initial
#/etc/init.d/mysql.server start
(Important: we need to run ndbd --initial only when the start MySQL for the first time, and if /var/lib/mysql-cluster/config.ini on Management Server changes.)
Again, it makes sense to start up the cluster nodes automatically in
case of a system restart/failure. Here are the ndbd init script and system
startup links for that:
#echo 'ndbd' > /etc/init.d/ndbd
#chmod 755 /etc/init.d/ndbd
#update-rc.d ndbd defaults
Test On Cluster Management server (server1)
#ndb_mgm
It will take you to the ndb_mgm prompt:
-- NDB Cluster -- Management Client --
ndb_mgm>
Now type show on the prompt You should see an output similar to this
ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.100.2 (Version: version number, Nodegroup: 0, Master)
id=3 @192.168.100.3 (Version: version number, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.100.1 (Version: version number)
[mysqld(API)] 2 node(s)
id=4 @192.168.100.2 (Version: version number)
id=5 @192.168.100.3 (Version: version number)
ndb_mgm>
We should see our data nodes connected in the previous screen. Now type quit to close the Management
client:
ndb_mgm>quit;
Test the
Cluster:
Now, let's create a Test database on Server2 (192.168.100.2) and run
some tests:
On Server2:
On Server2:
#MySQL
MySQL>CREATE DATABASE testdb;
MySQL>USE testdb;
MySQL>CREATE TABLE tblCustomer (ID INT) ENGINE=NDBCLUSTER;
MySQL>INSERT INTO tblCustomer VALUES (1);
MySQL>SELECT * FROM tblCustomer;
MySQL>quit;
pay attention to the create table statement, we must specify
ENGINE=NDBCLUSTER for all tables that we want to clustered. As stated earlier,
MySQL cluster only saupports NDB engine, so if you use any other engine, table
simply wont get clustered.
The result of the SELECT statement would be:
mysql> SELECT * FROM tblCustomer;
+------+
| ID |
+------+
| 1 |
+------+
Since clustering in MySQL is at the "table level" not at the
database level, so we would have to create the database sperately on Server3
(192.168.100.3) as well, but afterwards tblCustomer would be replicated with
all its data (since the engine is NDBCLUSTER):
On Server3:
mysql -u
mysql> CREATE DATABASE testdb;
mysql> USE testdb;
mysql> SELECT * FROM tblCustomer;
Now, if we insert a row of data on Server3, it should be replicated back
to Server2:
mysql>INSERT INTO tblCustomer VALUES (2);
If we run a SELECT query on Server2, here is what we should see:
mysql> SELECT * FROM testtable;
+------+
| ID |
+------+
| 1 |
| 2 |
+------+
