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.

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.

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

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.

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”

  

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

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

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

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

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.

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