Refresh back your experience on owning a vehicle for yourself from the beginning until now. How is the procedure looks like? May be it will be something like when you start your first job after graduation, you will most probably owning a vehicle which you are able to buy with your basic salary. I believe majorities of the fresh graduates, your first job salary may be as minimum as 1500/month or may be less. If you are fortunate enough, you can have a higher pay. So, with this range of salary, what you can buy? a BMW or Honda? Well, mostly you will start with a Motorcycle. When your salary is higher, you may aim for an economic priced car. As your job performance is good, you may get more higher pay. That time, I think you may think of is a medium or even a luxurious car.
Why I am talking about this? What are the relationship with the topic which I am going to talk about. Well, in some perspective, there is some relationships on the concept or the procedures. You can try to relate it when you go through this posts. If you are not able to relate it, its okie. As long as you are able to setup the MySQL clustering at the end of the post reading, then you still get what you want. :p
A web developer, when they start off their first database application, the number of user may be less. Single database is required. There is no need on having a database server architecture which involved advanced technology such as load balancing, high availability, and etc..
When the number of users grows and the traffic is high, you will think about those solutions instead of keeping the current solutions which will be the bottleneck for your application or service that you provided to your client.
There is a lot of advanced solutions that you can go through such as load balancing or load balancing with high availability. There are many ways to achieve it as well like setting up load balancers which is having multi master database connected behind it or may be clustering.
What I will be talking here will be on database clustering. The demonstration here will be on MySQL clustering which I will share with you guys on the basic steps on setting up the cluster using NDB engine.
To make sure that the clustering that you have setup do meet your needs, you will need to go through some recommended steps that is :
- Identify your application needs over the mysql database. If MySQL do satisfied with the requirement, then you may proceed to setup MySQL cluster for your application. Else, look for other solutions like MySQL database replication.
- Plan and design your MySQL cluster network. How many node(s) you need. Draft out the network diagram. This will ease you on the setup.
- Prepare all the required nodes and the source of the MySQL clustering installer ( you may get it from http://www.mysql.com )
- Setup all the node machine
- Testing the clustering
- Bring them live and enjoy the ride!!
Though you may think that this is such a long procedure, this is somehow will be the results that you get is determined by how many effort you put into the solutions. Some steps might be boring or not usefull, but with every little little thing, when you sum up all together, it does something that may delight you.
Now, lets starts.
Identify Application Needs Over MySQL Database Solutions
My application need a redundant MySQL database connections, load balanced on the MySQL database which improves on the data mining, and high availbility where my database connection will reach 99.999% uptime. I can add additional MySQL database processing power when needed to balanced up the loads.
MySQL Clustering will provides all the requirement that I need.
Plan and Design Your MySQL Cluster Network
This is the first phase of setup and I am having a limited budget on this. So I will start with the most minimum nodes. I can still add in more when it is required. I will have 3 nodes. One will be the nodes that act as Management Server (that manages the cluster) and another two will be act as the Data Node (that will process the database query and data storage) .
The network diagram will be something like this:
MySQL Cluster
Preparing Nodes and Management Server
Have all the 3 servers having Linux server installed. For this example, I will use CentOS 5.2 operating system. Just install the basic structure. Do not install MySQL database server. We will install manually from the latest source that you can get from the
http://www.mysql.com.
A. Setup MySQL Cluster Management Server
Download the latest source of the ndb from
http://dev.mysql.com/downloads/cluster/. Choose the Linux(non-rpm packages). Selecting between x86 or x86_64 is depends on your server machine operating system. If you are using 64 bit OS, then choose the x86_64 instead. Else, choose x86 download source.
The example file that downloaded to your terminal will be as below:
mysql-cluster-gpl-6.3.20-linux-i686-glibc23.tar.gz
Once you have downloaded the source, un compress it:
tar -zxvf mysql-cluster-gpl-6.3.20-linux-i686-glibc23.tar.gz
Next, go into the extracted folder:
cd mysql-cluster-gpl-6.3.20
Copy out all the required files from the “bin” folderm, that is, all files start with ndb_mgm. Copy the file to the “/usr/local/bin” directory:
cp bin/ndb_mgm* /usr/local/bin
Go into the destination folder that you have copied the required file to and chmod as below:
cd /usr/local/bin
chmod +x ndb_mgm*
Create a directory “mysql-cluster” inside /var/lib directory which will be used by the mysql cluster manager on the configuration files and logs.
mkdir /var/lib/mysql-cluster
Go into the mysql-cluster and create the manager configuration file:
cd /var/lib/mysql-cluster
vi config.ini
Add the following line into config.ini:
[NDBD DEFAULT]
NoOfReplicas=2 # Number of data nodes that this cluster have
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Managment Server
[NDB_MGMD]
HostName=10.0.1.30 # the IP of Management Server
# Storage Engines
[NDBD]
HostName=10.0.1.31 # the IP of the FIRST Data Node DataDir= /var/lib/mysql-cluster
[NDBD]
HostName=10.0.1.32 # the IP of the SECOND Data Node DataDir=/var/lib/mysql-cluster
# 2 MySQL Clients
# I personally leave this blank to allow rapid changes of the mysql clients;
# you can enter the hostnames of the above two servers here. I suggest you dont.
[MYSQLD]
[MYSQLD]
Start the Management Server Daemon by:
ndb_mgmd
B. Setup MySQL Data Node
Download the latest source of the ndb from
http://dev.mysql.com/downloads/cluster/. Choose the Linux(non-rpm packages). Selecting between x86 or x86_64 is depends on your server machine operating system. If you are using 64 bit OS, then choose the x86_64 instead. Else, choose x86 download source.
The example file that downloaded to your terminal will be as below:
mysql-cluster-gpl-6.3.20-linux-i686-glibc23.tar.gz
Once you have downloaded the source, un compress it:
tar -zxvf mysql-cluster-gpl-6.3.20-linux-i686-glibc23.tar.gz
Create mysql group and mysq user in the group:
groupadd mysql
useradd -g mysql mysql
Move the uncompressed folder into /usr/local and rename it to mysql folder. This can be done in one command line using “mv”:
mv mysql-cluster-gpl-6.3.20-linux-i686-glibc23 /usr/local/mysql
Change directory to /usr/local/mysql:
cd /usr/local/mysql
Execute the following file in the directory to install the mysql base database:
scripts/mysql_install_db –user=mysql
Change the ownership and group for /usr/local/mysql and /usr/local/mysql by executing the following command line:
chown -R root /usr/local/mysql
chown -R mysql /usr/local/mysql/data
chgrp -R mysql /usr/local/mysql
Copy the mysql.server file located in support-files folder to /etc/rc.d/init.d/ and rename it as mysql. You can choose not to rename it, but I prefer to change it:
cp support-files/mysql.server /etc/rc.d/init.d/mysql
Make sure the file /etc/rc.d/init.d/mysql is executable by :
chmod +x /etc/rc.d/init.d/mysql
Register the /etc/rc.d/init.d/mysql into the services list by :
chkconfig –add mysql
Next, we will go into the configuration part for the Data Node. All you need to do is edit the /etc/my.cnf :
vi /etc/my.cnf
Include the following lines into the my.cnf and save the file:
[mysqld]
ndbcluster
ndb-connectstring=10.0.1.30 # the IP of the MANAGMENT (THIRD) SERVER
[mysql_cluster]
ndb-connectstring=10.0.1.30 # the IP of the MANAGMENT (THIRD) SERVER
Create the /var/lib/mysql-cluster directory for the clustering usage.
mkdir /var/lib/mysql-cluster
Change directory into the mysql-cluster directory and execute the following comandline:
/usr/local/mysql/bin/ndbd –initial
DataNode is initialised! Now, start the mysql database server by :
/etc/rc.d/init.d/mysql start
Repeat this entire section to setup the second Data Node.
Once all this is done, all your machine is well configured and are ready for testing.
Testing MySQL Cluster Network
To make sure that the server manager is fine, ssh into the Management Server. Go into the ndb management console by executing the following command line:
ndb_mgmd
When inside the console, run “show” command to list out the cluster status:
ndb_mgm> show
You will see the following results:
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 2 node(s)
id=2 @10.0.1.31 (mysql-5.1.30 ndb-6.3.20, Nodegroup: 0, Master)
id=3 @10.0.1.32 (mysql-5.1.30 ndb-6.3.20, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.1.30 (mysql-5.1.30 ndb-6.3.20)
[mysqld(API)] 2 node(s)
id=4 @10.0.1.31 (mysql-5.1.30 ndb-6.3.20)
id=5 @10.0.1.32 (mysql-5.1.30 ndb-6.3.20)
Notice that the first line stated “Connected to Management Server at: localhost:1186″ this tells you that the cluster is healthy with the information shown. If you are not getting this line, recheck on your configuration and installation. Make sure everything is installed and configured properly.
Next, test out the Data Node. SSH into any of the data node. Run the following command line to connect to the database server, create a table under the “test” database and insert a data into the table crated:
>mysql
mysql> use test;
mysql> CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
INSERT INTO ctest () VALUES (1);
To make sure the data is inserted correctly, run the SELECT sql command:
mysql> SELECT * FROM ctest;
The results will be :
+——+
| i |
+——+
| 1 |
+——+
Now, last but not least, ssh into another data node. Check whether the “test” database again whether the data is there as well. Do not attempt to create the table, most likely you will get error as the table already created. Just use “SELECT” sql command to check the data is stored properly.
If you are getting the same results at the second data node, your cluster is working fine.
Congratulations!! You have just setup the MySQL NDB Clusters with 2 Data Node.
You will be curious, I have two data node, how my application can balance off the query between these two server as by default, like other web application connection string will be referring to 1 ip instead of 2 ip randomly.
You can consider to put your data node behind a transport layer load balancer device which will load balance the connection to these data node like shown in the network diagram. Therefore, the query connection will be directed to the load balancer and the load balancer will balance it off by redirecting the connection to the nodes.
