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