Monday, February 18, 2013

MySql cluster and Java

Pre-requisites

  • ClusterJ API : ClusterJ is a high level database API that is similar in style and concept to object-relational mapping persistence frameworks such as Hibernate and JPA
  • Requires below jar files
    openjpa-1.2.0.jar
    driver-5.1.10.jar (This is the MySQL JDBC driver)
    geronimo-jta_1.1_spec-1.1.jar
    geronimo-jpa_3.0_spec-1.0.jar
    serp-1.13.1.jar
    commons-lang-2.1.jar
    commons-collections-3.2.jar
    clusterj-api.jar
    clusterj.jar
    clusterjpa.jar

Code

import com.mysql.clusterj.annotation.Column;
import com.mysql.clusterj.annotation.Index;
import com.mysql.clusterj.annotation.PersistenceCapable;
import com.mysql.clusterj.annotation.PrimaryKey;

//sample code

Properties props = new Properties();
//replace localhost with managment node hostname
props.put("com.mysql.clusterj.connectstring", "localhost:1186");

//replace mydb with your database name.
props.put("com.mysql.clusterj.database", "mydb");


References:

MySQL Cluster on Windows my way

Welcome to crash mysql cluster master page :-)

Lets plan to implement it this way.

NodeIP Address
Management (MGMD) node192.168.0.10
MySQL server (SQL) node192.168.0.20
Data (NDBD) node "A"192.168.0.30
Data (NDBD) node "B"192.168.0.40

Precautions: Always use forward slashesh or 2 backward slasesh for specifying the paths in config or ini files.

Step 1: Download and install (on sqlnode host or 192.168.0.20)
  • Download Binaries here http://dev.mysql.com/downloads/cluster/
  •  Extract the zip to c:\mysql directory. If you put into c drive you will need admin rights to start management console. If you put in c:\users\\mysqlc you dont need admin rights to start anything so choice is yours.
    Note: Basically you need full rights on the directory.
  • Add path c:\mysql\bin to Environment Variable.
  • leave this and come back on step 3
Step 2: Setup management node
  • Create c:\mysql\bin directory
  • copy ndb_mgmd.exe and ndb_mgm.exe in c:\mysql\bin from sqlnode host

    > Note: we just need these executables on management node.
  • Create local configuration file my.ini - this file needs only to supply the location of configuration file

    [mysql_cluster]
    # options for manangement node process
    #config-file=c:/mysql/bin/config.ini
  • Create configuration file config.ini with below content and save to c:\mysql\bin
[ndbd default]
NoOfReplicas=2
DataDir=C:/mysql/bin/cluster-data

[ndb_mgmd]
HostName=192.168.0.10               # Hostname or IP address of management node
DataDir=C:/mysql/bin/cluster-logs   # Directory for management node log files


[ndbd]
# Options for data node "A":  # (one [ndbd] section per data node)
HostName=192.168.0.30           # Hostname or IP address

[ndbd]
# Options for data node "B":
HostName=192.168.0.40           # Hostname or IP address

[mysqld]
# SQL node options:
HostName=192.168.0.20           # Hostname or IP address



Step 3: Setup sql node
  •  create c:\mysql\my.ini with below content
[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine
ndb-connectstring=192.168.0.10  # location of management server


#use different port if you already have mysql server running or uncomment below
port=5000

#provide basedir and datadire if they are not in c:\ drive
basedir=c:/mysql
datadir=c:/mysql/data

Step 4: Setup Data nodes

Follow the below steps on data node hosts.
  • Create c:\mysql and c:\mysql\bin and c:\mysql\bin\cluster-data directory
  • Copy ndbd.exe from (192.168.0.20) into c:\bin
    Note: we just need this executable and nothing else for data node.
  • Create my.ini into c:\mysql\my.ini and add below content

    [mysql_cluster]
    # Options for data node process:
    ndb-connectstring=192.168.0.10  # location of management server

Step 4: Start the cluster.
  • 1st start managment nodec:\mysql\bin> ndb_mgmd -f config.ini [--initial]
    Important: management node cashes the configuration data that it reads from config.ini. If you make any changes in configuration you must provide --reload option or --initial option to realod the configuration otherwise the -f config.ini option will be ignored from 2nd run onward
    Note: remove "[" and "]" if you are using the --initial option
    Note: -f or --config-file is same.
    Important: By default cluster create configuration cache in c:\mysql\bin\mysql-cluster directory but you can override with --configdir option.
  • 2nd start data nodesc:\mysql\bin> ndbdNote: as long as you are running the process from bin directory and your my.ini file is in the bin directory you dont need provide any path.
  • Start Node Manager to see what's going on..c:\mysql\bin> ndb_mgmndb_mgm> show ndb_mgm>ALL STATUS
  • finally, start sql nodec:\mysql\bin>mysqld --console

    Note: console option provide more details and can be used for debugging purpose. Again as long as you keep the my.ini in the bin directory you dont need provide any path.



Known Errors & Resolutions.
  • If data nodes are stopping then Start node with --initial parameter, ie. >ndbd --initial
  • If data directory are stored in different location than default binary location
Debugging
  • The below option "--console" provides debugging information when used with mysqld

    c:\mysql\> mysqld --console

References:

Sunday, February 17, 2013

How to Display time in Different Timezones

Specific Example: How to display current time for Rio De Janeiro?

  1. Find out the timezone abbreviation or name from here http://www.timeanddate.com/worldclock/city.html?n=213
    Timezone abbreviation: BRT – Brasilia time
  2. Find out the mapping of correct standard name for the above timezone. http://msdn.microsoft.com/en-us/library/ms912391(v=winembedded.11).aspx
    065E. South America Standard Time(GMT-03:00) Brasilia
  3. Now we can use the code to set timezone to E. South America Standard Time

here is the code sample.

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;

namespace Timezone
{
    class Program
    {
        static void Main(string[] args)
        {
            System.Console.WriteLine("Local time:" + DateTime.Now.ToString());

            var brazilTime = TimeZoneInfo.ConvertTime(DateTime.Now, TimeZoneInfo.FindSystemTimeZoneById("E. South America Standard Time"));
            
            System.Console.WriteLine("Brazil Time:\t" + brazilTime.ToString());
            
            System.Console.ReadLine();
        }
    }
}