Thursday, September 16, 2010

How to Create a Mysql NDB cluster on a PC, windows 7 tutorial

Just like How to create a Cassandra cluster on a single PC / windows Tutorial, I will list the prerequisites and point of config , then will go through a simple cluster configuration step by step.  I will create a two storage Node + 1 Management Node + one MySQl (API Node)

before you try to run/create a cluster, please check the following requirements.

  • Two TCP Ports,make sure no apps are using these two ports. also check the firewall policy. I will put P1 and P2 as reference later on. 
    • one is for Mysql API, I will put 5000 here. ( Just like the port 3306 for the standard MySQL.  the port will used for the connection library.)
    • one is the Management Port, by default is 1186.
    • No other ports are needed to be specified explicitly. ( every data node just handshake with management node first to establish the cluster membership. like the gossip-based clustering protocol.)
  • TWO Data Folders, DataFolder_1 and DataFolder_2
    • one is for Data Node. (ALL Data Nodes  in the same host will share the same folder by default. )
    • another one is for API Node ( just like the data folder in the standalone mysql, user management , information schema, those system tables.)
  • TWO Config files. Config_1 and Congfig_2
    • one is for Management Node.
    • another one is for API Node
    • NO config file for data node directly. why? when data node join the cluster, it will first talk to management node. the manage node will tell data node what it’s configuration will be.)
  • One Config folder, Conf_1
    • a central place to store Confg_1 and Config_2 , also the system generated config.

then, Let’s start the installation process. basically just copy and past files. :)

1. Download the mysql cluster bits from mysql.com. http://www.mysql.com/downloads/cluster/. My base folder is C:\mysql\mysqlcluster

unzip it , and rename the uncompressed folder . I will put it to C:\mysql\mysqlcluster\mysqlc. so the folder structure will looks like these.

image

Add the Bin folder to the system path. then you dont have to key in the full path to execute those commands.

2. Create DataFolder_1 and Data_folder2. and One Config folder.

Data Node will be C:\mysql\mysqlcluster\my_cluster\ndb_data
   Mysql data node will be C:\mysql\mysqlcluster\my_cluster\mysqld_data
  onc folder, C:\mysql\mysqlcluster\my_cluster\conf

copy the initial Mysql and Ndbinfo folder from the downloaded data folder, here will be C:\mysql\mysqlcluster\mysqlc\data to Mysql data folder. #2 folder above.

3. Create two config file. 
   Config_1 for management node. I will put it into C:\mysql\mysqlcluster\my_cluster\conf\management.ini
 

[ndb_mgmd]
hostname=localhost
datadir=C:\mysql\mysqlcluster\my_cluster\ndb_data
id=1
[ndbd default]
noofreplicas=2
datadir=C:\mysql\mysqlcluster\my_cluster\ndb_data
[ndbd]
hostname=localhost
id=3
[ndbd]
hostname=localhost
id=4
[mysqld]
Id=50

then the config_2 for mysql API Node.

I will put it into C:\mysql\mysqlcluster\my_cluster\conf\mysqld.conf

[mysqld]
ndbcluster
datadir=C:\mysql\mysqlcluster\my_cluster\mysqld_data
basedir=C:\mysql\mysqlcluster\mysqlc
port=5000

4. time to kick off the Cluster.

A. Start the management Node first. ( run the initial loading. , always to be the 1st Node. waiting for all nodes to connect and join the cluster.)

ndb_mgmd -f c:\mysql\mysqlcluster\my_cluster\conf\management.ini --initial --config-dir=c:\mysql\mysqlcluster\my_cluster\conf

  
after that, when you check the conf foder. one system config file will be generated.
image

Here by default, ndb_mgmt process will listen on the 1186 port and waiting for other node to join.
image

When you run the “ndb_mgm -e show” command, it will you the cluster status. at this moment, Only management Node is Ready.  other nodes are disconnected.


image

B. Start Storage Node 3. ( all you need is to specify the Management Node endpoint, If you get fail to spawn process error, put the foreground option here. )
       run “ndbd -c localhost:1186 --foreground=true”

   image

run status again, “ndb_mgm –e show”, Node 3 is up now.
image

Let’s start Node 4. run the same command. “ndbd -c localhost:1186 --foreground=true”. here is the trick. we dont specify the node id. we will talk to the mgmt node. it will assign one for you.
run status again, “ndb_mgm –e show”, Bote Nodes are ready

image

C. Start the API Node. ( which is equivalent to the mysqld in standalone mysql.)

mysqld --defaults-file="c:\mysql\mysqlcluster\my_cluster\conf\mysqld.conf"

after that, we are done. all nodes are up

image

AS a Say, all talks to Center management port , you can tell from the tcpviewer.

image

Now cluster is ready, let’s connect to the API node. and create some data.

mysql -h 127.0.0.1 –P5000 -u root
mysql> create database clusterdb;use clusterdb;
mysql> create table simples (id int not null primary key) engine=ndb;
mysql> insert into simples values (1),(2),(3),(4);
mysql> select * from simples;

here by default , the datamemory and indexmemory will determined the db capacity. because all data are stored in memory by default.

you can try push more data to the db. and query the system table.

image

since 5.1.16, we can offload the non-indexed column from memory to disk. here is the basic syntax.

CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.log'
    INITIAL_SIZE 16M
    UNDO_BUFFER_SIZE 2M
    ENGINE NDBCLUSTER;

    CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    INITIAL_SIZE 32M
    ENGINE NDBCLUSTER;

    create table nonmemory2(junk char(255) default null) tablespace ts_1 storage disk engine=ndb;

also, please note, even data is stored in disk, it still need a small memory footprint for each record. around 8 bytes as a point to disk.

More tutorials.
How to create a Cassandra cluster on a single PC
how to setup multi ip address on one network card
Lucene, Indexing and searching

8 comments:

Ts@ng@ris said...

Hi,
thanks for your post for installing mysql cluster on a sinlge windows 7 pc. What i need is to install mysql cluster on two windows 7 pcs so as to show the data replication as part of my final year project. Is this possible?

Ryan DH said...

YES

Ts@ng@ris said...

Ok! Thanks for your reply, do you know any good tutorial on how to implement that?

Jayesh Gupta said...

Big Thanks..!!! It had solved my all queries regarding MySQL cluster set up.

Thayalan Sivapaleswararajah said...

I', getting below warning. Any idea


MySQL Cluster Management Server mysql-5.6.14 ndb-7.3.3
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 4: [MGM] id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 10: [DB] id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 13: [DB] id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 15: [API] Id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 4: [MGM] id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 10: [DB] id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 13: [DB] id is deprecated, use NodeId instead
2013-11-26 16:00:53 [MgmtSrvr] WARNING -- at line 15: [API] Id is deprecated, use NodeId instead

Selvakumar KuppuSamy said...
This comment has been removed by the author.
Selvakumar KuppuSamy said...
This comment has been removed by the author.
Selvakumar KuppuSamy said...

Hi,

If i delete current mysql_data directory and access the ndb_data is possible , is it required both mysql_data and ndb_data to get required results?

 
Locations of visitors to this page