Thursday, 29 December 2011

HA Clustering with Windows 2008 and MySQL

Clustering is available in a High Availability mode commonly known as HA and High Performance mode commonly known as NLB. In HA a service or resource is made available across the cluster nodes and its availability is made a resilient as possible. Windows 2008 Enterprise offers fail over clustering. Here I am showing you a HA cluster with a single DB service that will be available across the cluster nodes.

Node Spec:
Windows 2008 Enterprise
Storage:
  • Local Disk - for OS , OS specific
  • Witness Disk / Quorum Disk - iscsi DISK, min 512MB
  • Application and Data Disk - iscsi DISK, App specific
Network Cards:
  • Card 1 for iscis network - static address
  • Card 2 for cluster network - static address
  • Card 3 for cluster service ip which can be accessed by the clients
Application Service: MySQL
Download the MySQL zip.
The iSCSI storage disks should be configured and only add the Quorum disk initially.
The nodes will be AD integrated, I have used the following details.
Using a cheap absolutely free SAN iSCSI, a Windows SAN hot-to is presented here - http://sudhakarbellamkonda.blogspot.com/2012/01/microsoft-windows-iscsi-san-clusters-1.html
Node Specification: 
Node1:
Name: dbclu1
Network Cards: iscsi-network static 192.168.200.1
               cluster-network static 192.168.100.61
               cluster-service dhcp 192.168.1.103 reserved
Storage: iSCSI disk 1 512M, Auto log at boot
         iSCSI disk 2 10G, Auto log at boot           

Node2:
Name: dbclu2
Network Cards: iscsi-network static 192.168.200.2
               cluster-network static 192.168.100.62
               cluster-service dhcp 192.168.1.104 reserved
Storage: iSCSI disk 1 512M, Auto log at boot
         iSCSI disk 2 10G, Auto log at boot           
Node3:

Name: dbclu3
Network Cards: iscsi-network static 192.168.200.3
               cluster-network static 192.168.100.63
               cluster-service dhcp 192.168.1.102 reserved
Storage: iSCSI disk 1 512M, Auto log at boot
         iSCSI disk 2 10G, Auto log at boot           

Cluster Name: db-cl1

  • Add storage disk1 to all the nodes. A how-to is presented here that should be repeated on all nodes - http://sudhakarbellamkonda.blogspot.com/2012/01/microsoft-windows-iscsi-san-clusters-2.html
  • At node1 start the Fail over Cluster Management and click on validate a configuration, add the nodes and wait for the configuration test to get over. All the tests should be passed if not rectify your test issue and then revalidate.
  • From the main page click 'create a cluster' and add the nodes. The MMC will ask for the cluster name, enter db-cl1 or what ever name you would want to give.
  • From the cluster network disable the iscsi-network card so that this network is only used for SAN Storage.
  • Add the second iSCSI disk at all nodes. A how-to is presented here that should be repeated on all nodes - http://sudhakarbellamkonda.blogspot.com/2012/01/microsoft-windows-iscsi-san-clusters-2.html
  • Unzip the MySQL archive to the second disk at node one
  • Add it as a service at all the nodes - Use the below statement to add the service.
    Assuming H: to be the second disk and h:\mysql the program folder,
    H:\mysql\bin>"H:\mysql\bin\mysqld" --install MySQL --defaults-file=h:\mysql\my.cnf 
  • Make sure that the service is stopped at all the nodes and set to automatic start
  • Add the second iSCSI disk to the cluster
  • Right click services and applications and select configure a service/application
  • Select Generic application
  • Add MySQL from the list
  • the Cluster manager will add the service and start it.
  • From the main cluster page, if you have not entered a static ip, obtain the dynamic ip and test the clustered service.
 To test the cluster you can shutdown the node where the DB service is online and see if the service is moved to another node.

Screen shots of this cluster available below.






If you want a cluster which will work if only one node is available with the disks then you can configure the cluster to use the iSCSI Disk1 of 512M as the Quorum disk. The min requirnment of a Quorum disk is 500M.