How do install and configure MySQL Cluster

Enterprise Solutions

Enterprise Solutions
Enterprise Solutions

Enterprise Solutions - Wiki

How do install and configure MySQL Cluster

Enterprise Solutions - Wiki

How do install and configure MySQL Cluster for Windows?

Applies to:

Window64 bit Operating System

Author:

Carthik Annayan

Problem:

How do i install and configure MySQL Cluster for Windows?

Solution:

How to implement a MySQL Cluster? We will discuss the steps to implement MySQL Cluster now. In a Typical HA environment for a MySQL Cluster, we need at-least 6 Servers to build HA at each Layer of the MySQL Cluster. The best part of deploying a MySQL cluster is it’s easy, and does not require many pre-requisites. All you need is a couple of servers, the operating system and a couple of switches.

The Nodes are Named as follows:-

Server Nodes:- Servernode1.example.com 192.168.0.10, Servernode2.example.com 192.168.0.20 

Data Nodes:- Datanode1.example.com 192.168.0.30, Datanode2.example.com 192.168.0.40

 Management Server Nodes:- Mgmntnode1.example.com 192.168.0.50, Mgmntnode2.example.com 192.168.0.60

Networking Pre-requisites:-

• Dedicated >= 1GB/s networking

• Prevent network failures (NIC x 2, Bonding)

• Use dedicated network for cluster communication

• No security layer to management node (remote shutdown allowed ....)

• Enable port 1186 access only from cluster nodes and administrators

 

Storage Layer (data nodes):-

 • One data node can use 8 cores

CPU: 2 x 4 cores

RAM: As much as you need a 10GB data set will require 20GB of RAM (because of redundancy)

• Each node will then need 2 x 10 / #of data nodes. (2 data nodes →10GB of RAM → 16GB RAM is good)

Disk space: 10xDataMemory + space for BACKUP + TableSpace (if disk data tables)

 

SQL Layer (MySQL Servers):-

 CPU: 2 – 16 cores

RAM: Not so important – 4GB enough (depends on connections and buffers)

The Mgmntnode2 Node is optional. It is important to provide HA at the Management node as well, since the management node is the central console from which all the Data nodes and server nodes are managed. However for this implementation we will consider only one Management node and implementation of MySQL Cluster 7.2.

Once the Servers are ready, the first step is to download the MySQL Cluster Bundle from the following Location (http://www.mysql.com/downloads/cluster ). Download the bundle that best suits your needs. Either the msi or the zip format. For this implementation I have used the zip format. The support status of the MySQL Cluster (OS, Architecture) is available from the MySQL Support Matrix. The next step is to Unzip the downloaded content.

Extract the content of the Zip archive to “c:\mysql\cluster” on the Server Nodes. Perform this step on each of the nodes or download the zip file to one node, and copy the contents to the other nodes.

To get access to the executables from the command line, add “c:\mysql\cluster\bin” to the PATH environment variable.

Now create the config.ini file in the Management Node (192.168.0.50), under the following Folder “c:\mysql\cluster”.

 Example config.ini file

config.ini

[ndbd default]

noofreplicas=2

DataDir=c:/mysql/bin/cluster-data       # Directory for each data node’s files

DataMemory=80M

IndexMemory=18M

 [ndb_mgmd]

hostname=192.168.0.50

NodeId = 1

DataDir=c:/mysql/bin/cluster-logs      #Directory of the management node Log files

 [ndbd]                                                   # Data Node1

hostname=192.168.0.30

NodeId=2

 [ndbd]                                                   # Data Node2

hostname=192.168.0.40

NodeId=3

 [mysqld]                                               # Server Node1     

hostname= 192.168.0.10

NodeId=101

 [mysqld]                                               # Server Node2

hostname= 192.168.0.20

 NodeId=102

 The My.ïni file of each data node has information about only the connect string which is the IP Address of the management node. The my.cnf file is located in “c:\mysql\my.ini”

  my.ini (Data Node1)

[mysql_cluster]

Ndb-connectstring=192.168.16.50

 Place the same contents in the DataNode2 as well.

 Finally, the my.ini contents of the MySQL Server Node located in “c:\mysql\my.ini” of the ServerNode.

 [mysqld]

Ndb-nodeid=101

Ndbcluster

Ndb-connectstring=192.168.16.50:1186                    # Location of the management Server

Port=3306

Default-storage-engine=ndbcluster

Once the configuration files are in place, the Nodes must be started in the following order, The Management Node, the Data Nodes and the SQL Server Node. The Commands to perform each of the following are displayed below.

Managment Node (192.168.0.50):-

ndb_mgmd -f /var/lib/mysql-cluster/config.ini  --configdir=/var/lib/mysql-cluster/ --initial

Data Nodes(192.168.0.30, 192.168.0.40):-

ndbd -c <Managementnode Ip address>:1186 –initial

This command should be issued on each of the Data Nodes.

SQL Server Node(192.168.0.10 , 192.168.0.20):-

C:\mysql\bin> mysqld –console

This command should be issued on each of the MySQL Server nodes.

Finally, to check if the cluster is up and running, issue the below command on the command line of the management sever. The ndb_mgmd executable is present in the directory to  ”c:\mysql\bin”

ndb_mgmd > show

0