Blogs
Archive
  • November 2017 (1)
  • October 2017 (3)
  • September 2017 (4)
  • August 2017 (3)
  • July 2017 (3)
  • June 2017 (3)
  • May 2017 (1)
  • February 2015 (2)
  • January 2015 (1)
  • December 2014 (3)
  • February 2014 (2)
Tags View All Blogs
Scrum.NetMicrosoftVisual StudioVC++C#JavaTestingQuality AssuranceUX DesignSPAAngularKnockoutBackboneUnderscoreDatabaseMySQLBlockchainHyperledgerChaincodeEnterprise MobilityUIIonicCordovaHybrid AppCode ReviewTypescriptAndroidIOTReactICOVenture CapitalArtificial IntelligenceKotlinIoTFuchsia OSVirtual RealityVOIPCrowdfunding

How to Setup a MySQL Database Cluster

It was my first day in the new organization and client assigned a task of setting up MySQL cluster. Truly speaking, I was speechless for a moment on hearing the term Cluster. I had never heard this term before. I was little upset on getting the task on the very first day. After doing research for some time, I found that there is an auto installer for MySQL but due to limited access on the systems and with antivirus installed, auto cluster did not work. Antivirus was actually blocking cluster startup. I researched more and found how to set up MySQL cluster manually. The cluster, that I designed, requires at least 4 machines in order to be highly available and fault tolerance cluster. At the end of the task, client was very happy and appreciated my work.

Database Clustering is a technique by which one or more servers or instances connect to a single database. A database is a set of physical files that actually store data and an instance is set of memory and processes that interact with these physical files. The term database clustering is bit ambiguous. Some people consider database cluster as two or more servers that share same storage while others consider database cluster to be a set of replicated servers. So, depending upon how data is stored and resources are allocated, clustering takes different forms.

One form is Shared-Nothing Architecture also known as Database Sharding. In Shared-Nothing Architecture, a cluster is made up of different nodes. Each node is fully independent of others. So, there is no single point of failure. An example of this type of cluster is an organization that has multiple data centers for a single website. In case, one data center is down, others can take over the charge and when the first data center is up, it updates itself with data from the other server.

Second form is Shared-Architecture in which data is stored centrally and then accessed by instances on different nodes or servers.

Following matrix shows the use cases of how cluster will behave when nodes are down. These use cases are based on a four-machine cluster.

Use CaseMgmt 1Mgmt 2data + sql node 1data + sql node 2Notes
1upupup + upup + upcluster is active
2downupup + upup + upcluster is active
3up downup + upup + upcluster is active
4downdownup + upup + upcluster is active
5upupup+ updown + upcluster is active
6upupdown + upup+ upcluster is active
7upupup + downdown + upcluster is active
8upupdown + upup + downcluster is active
9upupup + downup + upcluster is active
10down downdown + upup + upcluster is down.

Here is a brief of how I set up the MySQL database cluster.

Tools Used

  • Operating System: Windows 7 64 bit.
  • MySQL Cluster s/w download: Supported version of MySql cluster can be downloaded from http://www.mysql.com/downloads/cluster/. In this example, I used Windows (x86, 64-bit), ZIP Archive version 7.3.5. Please note that only MySql executables that come with this cluster archive must be used.

Pre-Requisites

  • Visual Studio C++ 2008 Redistributable
  • Allow exception for all ports used in the cluster
  • All hosts should be on the same LAN for fast data transfer among the nodes and to avoid latency caused by internet problems

Installation

Locate the Zip file that you have downloaded and extract the contents to the desired location. In this case, C:\User1\2MC\mysqlc is used.

Configuration

In this setup, I use 2 management nodes (ndb_mgmd), 2 data nodes (ndbd) & 2 MySQL Server(mysqld). Let's assume the following IPs for the nodes:

  • Mgmd1 = 10.0.0.1
  • Mgmd2 = 10.0.0.2
  • DataNode1 = 10.0.0.3
  • DataNode2 = 10.0.0.4
  • SqlNode1 = 10.0.0.3
  • SqlNode2 = 10.0.0.4

On Mgmd1 (10.0.0.1), create following directory structure:

  • C:\User1\2MC\my_cluster
  • C:\User1\2MC\my_cluster\conf\
  • C:\User1\2MC\my_cluster\msof1
  • C:\User1\2MC\my_cluster\conf\ config.ini

On DataNode1 & Sql Node1 (10.0.0.3), create following directory structure:

  • C:\User1\2MC\my_cluster\conf
  • C:\User1\2MC\my_cluster\mysqld1_data
  • C:\User1\2MC\my_cluster\ndb1_data
  • C:\User1\2MC\my_cluster\conf\my1.cnf

On DataNode2 & Sql Node2(10.0.0.3), create following directory structure:

  • C:\User1\2MC\my_cluster\conf
  • C:\User1\2MC\my_cluster\mysqld1_data
  • C:\User1\2MC\my_cluster\ndb1_data
  • C:\User1\2MC\my_cluster\conf\my2.cnf

my1.cnf: Open my1.cnf and write the following connection string:

	[mysql_cluster]
	   ndb-connectstring=10.0.0.1,10.0.0.2
[mysqld]
ndbcluster
ndb-connectstring=10.0.0.1:1186,10.0.0.2:1186
explicit_defaults_for_timestamp=true
datadir=C:\User1\2MC\my_cluster\mysqld1_data\nbasedir=C:\User1\2MC\mysqlc #Base Directory From Where MySqld is run. Default port for MySqld = 3306

my2.cnf: Open my2.cnf and write the following connection string:

	[mysql_cluster]
	   ndb-connectstring=10.0.0.1,10.0.0.2
[mysqld]
ndbcluster 
ndb-connectstring=10.0.0.1:1186,10.0.0.2:1186
explicit_defaults_for_timestamp=true
datadir=C:\User1\2MC\my_cluster\mysqld1_data 
basedir=C:\User1\2MC\mysqlc #Base Directory From Where MySqld is run. Default port for MySqld = 3306

config.ini: Open config.ini on 10.0.0.1 and write the following connection string. When done, copy config.ini on m/c 10.0.0.2

	[ndbd default]
noofreplicas=2
#datamemory=80M
#indexmemory=18M

[ndb_mgmd]
hostname=10.0.0.1
nodeId = 1
datadir=C:\User1\2MC\my_cluster\msof1

[ndb_mgmd]
hostname=10.0.0.2
nodeId = 2
datadir=C:\User1\2MC\my_cluster\msof1

[ndbd]
hostname=10.0.0.3
nodeId=3
datadir=C:\User1\2MC\my_cluster
db1_data

[ndbd]
hostname=10.0.0.4
nodeId=4
datadir=C:\User1\2MC\my_cluster
db1_data

[mysqld]
hostname=10.0.0.3
nodeId=5

[mysqld]
hostname=10.0.0.4
nodeId=6

Like other MySql server, mysqld process requires a 'mysql' database to be created and populated with the important system data (like ndbinfo). These can be copied from the MySQL cluster installation directory as follows. So, on m/c 10.0.0.3 & 10.0.0.4 do the following:

1.Copy files from C:\User1\2MC\mysqlc\data\mysql  to C:\User1\2MC\my_cluster\mysqld_data\mysql
2.Copy files from C:\User1\2MC\mysqlc\data\ndbinfo to C:\User1\2MC\my_cluster\mysqld_data\ndbinfo3.Copy files from C:\User1\2MC\mysqlc\data\performance_schema to C:\User1\2MC\my_cluster\mysqld_data\performance_schema

Starting Cluster

User must be logged in to the system as ADMIN. MySql cluster should be started in the following sequence:

Management Node>>>Data Nodes>>> Sql Nodes

Follow below steps:

  • Open command prompt as admin on 10.0.0.1 and change directory to
    C:\User1\2MC\my_cluster
  • Start Management Node as
    C:\User1\2MC\my_cluster>start /B 
    C:\User1\2MC\mysqlc\bin\ndb_mgmd -f conf\config.ini --initial -configdir=C:\User1\2MC\my_cluster\conf
  • Open command prompt as admin on 10.0.0.2
  • Start Management Node as
    C:\User1\2MC\my_cluster>start /B 
    C:\User1\2MC\mysqlc\bin\ndb_mgmd -f conf\config.ini --initial -configdir=C:\User1\2MC\my_cluster\conf
  • Open Command Propmt as admin on 10.0.0.3 and change directory to
    C:\User1\2MC\my_cluster
  • Start data node as
    start /B 
    C:\User1\2MC\mysqlc\bin\ndbd -c 10.0.0.1:1186,10.0.0.2:1186
  • Start SqlNode as
    start /B 
    C:\User1\2MC\mysqlc\bin\mysqld --defaults-file=conf\my1.cnf
  • Open Command Propmt as admin on 10.0.0.4 and change directory to
    C:\User1\2MC\my_cluster
  • Start data node as
    start /B 
    C:\User1\2MC\mysqlc\bin\ndbd -c 10.0.0.1:1186,10.0.0.2:1186
  • Start SqlNode as
    start /B 
    C:\User1\2MC\mysqlc\bin\mysqld --defaults-file=conf\my2.cnf

Checking the Status Of Cluster

From either of the machines 10.0.0.1 or 10.0.0.2, run

C:\User1\2MC\mysqlc\bin\ndb_mgm.exe 

Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3  @10.0.0.3  (mysql-5.6.17 ndb-7.3.5, Nodegroup: 0, *)
id=4    @10.0.0.4  (mysql-5.6.17 ndb-7.3.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @10.0.0.1  (mysql-5.6.17 ndb-7.3.5)
id=2    @10.0.0.2  (mysql-5.6.17 ndb-7.3.5)

[mysqld(API)]   2 node(s)
id=5    @10.0.0.3  (mysql-5.6.17 ndb-7.3.5)
id=6    @10.0.0.4  (mysql-5.6.17 ndb-7.3.5)

How Create Cluster Nodes As Windows Services?

Consider a scenario when your machine is down and you are not available. Someone else boots up the machine. If that person is not aware of running these commands, your node will still remain down. To overcome this scenario, MySQL supports creating nodes as windows services. Once created, if your machine goes down, anyone can bring that machine UP which automatically brings the node UP on that particular machine. Use below commands to configure nodes as windows service:

ManagementNode1 As Windows Service

"C:\\User1\\2MC\\mysqlc\\bin\\ndb_mgmd.exe" --install=MGMD1 --config-file="C:\\User1\\2MC\\my_cluster\\conf\\config.ini" --configdir="C:\\User1\\2MC\\my_cluster\\conf"

ManagementNode2 As Windows Service

"C:\\User1\\2MC\\mysqlc\\bin\\ndb_mgmd.exe" --install=MGMD2 --config-file="C:\\User1\\2MC\\my_cluster\\conf\\config.ini" --configdir="C:\\User1\\2MC\\my_cluster\\conf"

DataNode1 As Windows Service

"C:\\User1\\2MC\\mysqlc\\bin\\ndbd.exe" --install=DataNode1 --ndb-connectstring="host=10.0.0.1:1186,10.0.0.2:1186" --connect-retries="-1"

DataNode2 As Windows Service

"C:\\User1\\2MC\\mysqlc\\bin\\ndbd.exe" --install=DataNode2 --ndb-connectstring="host=10.0.0.1:1186,10.0.0.2:1186" –connect-retries="-1"

SQL Node1 As Windows Service

"C:\User1\2MC\mysqlc\bin\mysqld.exe" --install MySqld1 –defaults-file=C:\User1\2MC\my_cluster\conf\my1.cnf

SQL Node2 As Windows Service

"C:\User1\2MC\mysqlc\bin\mysqld.exe" --install MySqld2 –defaults-file=C:\User1\2MC\my_cluster\conf\my2.cnf