MySQL Cluster database & MySQL Replication
RSS icon Email icon Home icon

  • My first Cluster running on Windows

    Posted on June 16th, 2009 admin 33 comments

    I figured that it was time to check out how to install, configure, run and use MySQL Cluster on Windows. To keep things simple, this first Cluster will all run on a single host but includes these nodes:

    • 1 Management node (ndb_mgmd)
    • 2 Data nodes (ndbd)
    • 3 MySQL Server (API) nodes (mysqld)

    Downloading and installing

    Browse to the Windows section of the MySQL Cluster 7.0 download page and download the installer (32 or 64 bit).

    MySQL Cluster Windows Installer

    MySQL Cluster Windows Installer

    Run the .msi file and choose the “Custom” option. Don’t worry about the fact that it’s branded as “MySQL Server 7.0″ and that you’ll go on to see adverts for MySQL Enterprise – that’s just an artefact of how the installer was put together.

    On the next screen, I decided to change the “Install to” directory to “c:\mysql” – not essential but it saves some typing later.

    Go ahead and install the software and then you’ll be asked if you want to configure the server – uncheck that as we’ll want to tailor the configuration so that it works with our Cluster.

    There are a couple of changes you need to make to your Windows configuration before going any further:

    1. Add the new bin folder to your path (in my case “C:\mysql\bin”)
    2. Make hidden files visible (needed in order to set up multiple MySQL Server processes on the same machine)

    Configure and run the Cluster

    Copy the contents of “C:\ProgramData\MySQL\MySQL Server 7.0\data” to “C:\ProgramData\MySQL\MySQL Server 7.0\data4″, “C:\ProgramData\MySQL\MySQL Server 7.0\data5″ and “C:\ProgramData\MySQL\MySQL Server 7.0\data6″. Note that this assumes that you’ve already made hidden files visible. Each of these folders will be used by one of the mysqld processes.

    Create the folder “c:\mysql\cluster” and then create the following files there:

    config.ini

    [ndbd default]
noofreplicas=2
    [ndbd]
hostname=localhost
id=2
    [ndbd]
hostname=localhost
id=3
    [ndb_mgmd]
id = 1
hostname=localhost
    [mysqld]
id=4
hostname=localhost
    [mysqld]
id=5
hostname=localhost
    [mysqld]
id=6
hostname=localhost

    my.4.cnf

    [mysqld]
ndb-nodeid=4
ndbcluster
datadir="C:\ProgramData\MySQL\MySQL Server 7.0\data4"
port=3306
server-id=3306

    my.5.cnf

    [mysqld]
ndb-nodeid=5
ndbcluster
datadir="C:\ProgramData\MySQL\MySQL Server 7.0\data5"
port=3307
server-id=3307

    my.6.cnf

    [mysqld]
ndb-nodeid=6
ndbcluster
datadir="C:\ProgramData\MySQL\MySQL Server 7.0\data6"
port=3308
server-id=3308

    Those files configure the nodes that make up the Cluster.

    From a command prompt window, launch the management node:

    C:\Users\Andrew>cd \mysql\cluster
C:\mysql\cluster>ndb_mgmd -f config.ini
2009-06-16 20:01:20 [MgmSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-06-16 20:01:20 [MgmSrvr] INFO     -- The default config directory 'c:\mysql\mysql-cluster' does not exist. Trying to create it...
2009-06-16 20:01:20 [MgmSrvr] INFO     -- Sucessfully created config directory
2009-06-16 20:01:20 [MgmSrvr] INFO     -- Reading cluster configuration from 'config.ini'

    and then from another window, check that the cluster has been defined:

     C:\Users\Andrew>ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)
    [ndb_mgmd(MGM)] 1 node(s)
id=1    @localhost  (mysql-5.1.34 ndb-7.0.6)
    [mysqld(API)]   3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)

    Fire up 2 more command prompt windows and launch the 2 data nodes:

    C:\Users\Andrew>ndbd
2009-06-16 20:08:57 [ndbd] INFO     -- Configuration fetched from 'localhost:118
6', generation: 1
2009-06-16 20:08:57 [ndbd] INFO     -- Ndb started
NDBMT: non-mt
2009-06-16 20:08:57 [ndbd] INFO     -- NDB Cluster -- DB node 2
2009-06-16 20:08:57 [ndbd] INFO     -- mysql-5.1.34 ndb-7.0.6 --
2009-06-16 20:08:57 [ndbd] INFO     -- Ndbd_mem_manager::init(1) min: 84Mb initi
al: 104Mb
Adding 104Mb to ZONE_LO (1,3327)
2009-06-16 20:08:57 [ndbd] INFO     -- Start initiated (mysql-5.1.34 ndb-7.0.6)
WOPool::init(61, 9)
RWPool::init(22, 13)
RWPool::init(42, 18)
RWPool::init(62, 13)
Using 1 fragments per node
RWPool::init(c2, 18)
RWPool::init(e2, 14)
WOPool::init(41, 8 )
RWPool::init(82, 12)
RWPool::init(a2, 52)
WOPool::init(21, 5)

    (repeat from another new window for the second data node).

    After both data nodes (ndbd) have been launched, you should be able to see them through the management client:

    ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=3    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)
    [ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
    [mysqld(API)]   3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)

    Finally, the 3 MySQL Server/API nodes should be lauched from 3 new windows:

    C:\Users\Andrew>cd \mysql\cluster
C:\mysql\cluster>mysqld --defaults-file=my.4.cnf

C:\Users\Andrew>cd \mysql\cluster
C:\mysql\cluster>mysqld --defaults-file=my.5.cnf

C:\Users\Andrew>cd \mysql\cluster
C:\mysql\cluster>mysqld --defaults-file=my.6.cnf

    Now, just check that all of the Cluster nodes are now up and running from the management client…

    ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=3    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)
    [ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
    [mysqld(API)]   3 node(s)
id=4    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=5    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=6    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)

    Using the Cluster

    There are now 3 API nodes/MySQL Servers/mgmds running; all accessing the same data. Each of those nodes can be accessed by the mysql client using the ports that were configured in the my.X.cnf files. For example, we can access the first of those nodes (node 4) in the following way from (yet another) window:

    C:\Users\Andrew>mysql -h localhost -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.34-ndb-7.0.6-cluster-gpl MySQL Cluster Server (GPLType 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> create table assets (name varchar(30) not null primary key, value int) engine=ndb;
Query OK, 0 rows affected (1.44 sec
mysql> insert into assets values ('car', 950);
Query OK, 1 row affected (0.00 sec
mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| car  |   950 |
+------+-------+
1 row in set (0.00 sec
mysql> insert into assets2 values ('car', 950);
Query OK, 1 row affected (0.00 sec)

    To check that everything is working correctly, we can access the same database through another of the API nodes:

    C:\Users\Andrew>mysql -h localhost -P 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.34-ndb-7.0.6-cluster-gpl MySQL Cluster Server (GPL)
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| assets         |
+----------------+
1 row in set (0.06 sec)
    mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| car  |   950 |
+------+-------+
1 row in set (0.09 sec)

    It’s important to note that the table (and its contents) of any table created using the ndb storage engine can be accessed through any of the API nodes but those created using other storage engines are local to each of the API nodes (MySQL Servers).

    Your next steps

    This is a very simple, contrived set up – in any sensible deployment, the nodes would be spread accross multiple physical hosts in the interests of performance and redundancy. You’d also set several more variables in the configuration files in order to size and tune your Cluster. Finally, you’d likely want to have some of these processes running as daemons or services rather than firing up so many windows.

    It’s important to note that Windows is not a fully supported platform for MySQL Cluster. If you have an interest in deploying a production system on Windows then please contact me at andrew@clusterdb.com

    MySQL Cluster , , ,
     

    30 responses to “My first Cluster running on Windows” RSS icon

    • Siddharth Prasad August 5th, 2009 at 08:12

      Hi
      i tried this out.seems that the cluster is working, but when i am accesing it using mysql -h localhost -p 3307 . it asks for a password and when i give the previous password it shows
      “access denied for user odbc@localhost.
      what i did is that i unsinstalled the previous version of mysql 5.1. and then installed the mysql cluster. but it did not asked me for giving any password .
      Please suggest how to go with it?

    • Have you made sure that permission has been granted for ‘odbc’@'localhost’? Try logging in with ‘mysql -u root’ to check and fix if needed.

    • Siddharth Prasad August 6th, 2009 at 05:35

      hi it’s working..now . but how can i deploy it on other systems , so that i can use it from other systems…….how to get the more broader version of this….
      please Reply…

    • Siddharth Prasad August 10th, 2009 at 03:01

      hi Andrew.
      i have some problem while making the data node on other system.when i configure all the files on my management node as described by the “DEPLOYING MYSQL CLUSTER OVER MULTIPLE HOST ON LINUX” .I am just extending it to windows.if use your example every thing is working fine…but when i make the data node on other system without making it as management node.when i issue the ndbd command…. it’s not able to identify the management node…
      I am not getting it…Can you please help me..
      it’s necessary….

      Siddharth Prasad

    • Hi Siddharth,

      are you specifying the correct IP address and port number (e.g. ndbd –initial -c 192.168.0.3:1186).

      What error message did you get?

      Regards, Andrew

    • siddharth prasad August 11th, 2009 at 03:15

      Hi Andrew,
      Thanks for the Reply..Now it’s working..
      i just have to give the managment node ip with the NDBD command and in the sql node Configuration file i just have to mention one more parametre called ndb-connectstring 192.168.1.8 the ip of the managment node…..
      but every time i start my cluster i have to open like many command prompt windows…how to avoid this?

      And Can you name some good resources about managing and using the cluster configuration?

      Thanks
      Siddharth prasad

    • Hi Siddarth,

      glad that you’ve got it working now.

      I’m not an expert in this area but I know that you can run the nodes as Windows services rather than from the command-line (just like any process – I don’t believe that there’s anything unique for MySQL Cluster).

      Regards, Andrew.

    • Siddharth Prasad August 13th, 2009 at 03:54

      Hi Andrew
      there is a problem …when i start more than 4 data nodes . and use the
      shell: ndb_mgm show command to see the status of the server.it says cannot get configuration,illegal reply from server..and the managment server crashes..

      i searched the net and found that windows
      ” On Windows, the internal basestring_vsprintf() function did not return a POSIX-compliant value as expected, causing the management server to crash when trying to start a MySQL Cluster
      with more than 4 data nodes.”

      it will be nice of you,if you can help me out of this.

      Thank you
      siddharth Prasad

    • Hi Siddharth
      You can pick up the fix by following the instructions in the bug report…. http://bugs.mysql.com/bug.php?id=45733

      Alternatively, you could wait for MySQL Cluster 7.0.7

      Regards, Andrew.

    • Siddharth Prasad August 13th, 2009 at 12:58

      Hi Andrew
      sorry to disturb you again.
      This time the problem is of speed.with 7 lacs records in a table A group by and order by sql query takes 32 secs to give the reply.

      when i took the back up and executed it on other machine without clustering and with engine=innodb . that also takes approx 35 secs.

      is it OK . or my clustering is not working .
      i have a four data node , with 2 replica, 2 node group and 6 sql node clustering.

      if it’s not ok what should i do? Waiting for the Reply.

      Thank You
      Siddharth Prasad

    • Hi, thanks for the great post, it helped me alot setting up my first cluster on Windows. I’ve got the cluster up and running but are now faced with a stability problem, it seems that “a little now and then” my datanodes simply shut down for no apparent reason. The shutdown is not synched though and it could well be that only one datanode shut down while the other (on a different server) keep running. This would be expected if I had any connection issues and one datanode lost contact with the management node and the other node, but as far as I can see there are no connection issues here. Do you know how I can proceed to find out what’s going on?

    • Hi Erik,

      the first thing to check would be the log files – you should be able to recognise from the log files which data node each belongs too.

      Once you’ve had a look and there’s no obvious cause from your side then search bugs.mysql.com to see if your problem has been raised before and then raise it if not.

      Note that Windows is still considered a development-only platform for MySQL Cluster but bug reports are still gratefully received as that will help to bring it up to production level.

      Regards, Andrew.

      If it looks like there’s a problem

    • hi AndRew,

      in this step: Copy the contents of “C:\ProgramData\MySQL\MySQL Server 7.0\data” to “C:\ProgramData\MySQL\MySQL Server 7.0\data4″

      I try to find data directory in “C:\ProgramData\MySQL\MySQL Server 7.0″ and “C:\Program Files\MySQL\MySQL Server 7.0″ but don’t have anything.

      Please help me.
      Thanks.

    • LanhVC,

      did you first make sure that you’ve set up Windows to make hidden files visible?

      Regards, Andrew.

    • I just tried to follow the instructions to create the cluster. After I typed ndbd, I got the errors:
      Error setting NTFS compression attribute: 6
      Error setting NTFS compression attribute: 6
      Error setting NTFS compression attribute: 6
      Error setting NTFS compression attribute: 6
      Error setting NTFS compression attribute: 5
      Error setting NTFS compression attribute: 5

      Do you have any ideas how to solve this?

      Thanks a lot

    • hi ANDREW,
      I maked hidden files visible.

      I finded but have in “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 7.0″

      is this directory needed to find?
      this directory content all folder of database exist MySQL.

      Thanks,
      LanhVC.

    • Hi Kelvin,

      There’s a patch available (http://bugs.mysql.com/bug.php?id=44418) or you can wait for MySQL Cluster 7.0.7 binaries to be published or download the source from ftp://ftp.mysql.com/pub/mysql/download/cluster_telco/mysql-5.1.35-ndb-7.0.7/ and compile it for yourself.

      Regards, Andrew.

    • Hi, Thanks for your reply. I downloaded from the link provided but need to search for the tutorial on how to compile it in windows as I’ve never done this before.

    • hello andrew,

      i am already searching for this tutorial for ages, and thx god and to you who wrote this tutorial.
      but still, i have problems to connect to mysql API/nodes, when i launched mysqld –defaults-files=my.#.cnf, i saw in ndb_mgm like this :

      ndb_mgm> show
      Cluster Configuration
      ———————
      [ndbd(NDB)] 2 node(s)
      id=2 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
      id=3 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)

      [ndb_mgmd(MGM)] 1 node(s)
      id=1 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

      [mysqld(API)] 3 node(s)
      id=4 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)
      id=5 (not connected, accepting connect from localhost)
      id=6 (not connected, accepting connect from localhost)

      ndb_mgm> show
      Cluster Configuration
      ———————
      [ndbd(NDB)] 2 node(s)
      id=2 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
      id=3 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)

      [ndb_mgmd(MGM)] 1 node(s)
      id=1 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

      [mysqld(API)] 3 node(s)
      id=4 (not connected, accepting connect from localhost)
      id=5 (not connected, accepting connect from localhost)
      id=6 (not connected, accepting connect from localhost)

      ndb_mgm> show
      Cluster Configuration
      ———————
      [ndbd(NDB)] 2 node(s)
      id=2 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0, Master)
      id=3 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7, Nodegroup: 0)

      [ndb_mgmd(MGM)] 1 node(s)
      id=1 @127.0.0.1 (mysql-5.1.35 ndb-7.0.7)

      [mysqld(API)] 3 node(s)
      id=4 (not connected, accepting connect from localhost)
      id=5 (not connected, accepting connect from localhost)
      id=6 (not connected, accepting connect from localhost)

      ndb_mgm>

      it wont connect, well it connect for a while and than disconnect, could u help me with this…?

      Best Regards

      Alex

    • Hi Alex,

      could you please include the output from mysqld?

      Thanks, Andrew.

    • hi andrew

      the are no messages on the mysqld, as shown below :

      Ion@ION C:\mysql\mysql-cluster
      # mysqld –default-files=my.7.cnf

      Ion@ION C:\mysql\mysql-cluster
      #

    • Hi Andrew,

      I’ve had the same problem and I fixed it. It was because I didn’t install the MySql Administrator wizard. And then restart your machine.

    • Sorry, my message was for Alex not Andrew.

    • hi andrew

      i am tracing the mysql error and found this ;

      091006 11:00:28 [Note] Plugin ‘FEDERATED’ is disabled.
      091006 11:00:29 [Note] PrimeBase XT (PBXT) Engine 1.0.08 RC loaded…
      091006 11:00:29 [Note] Paul McCullagh, PrimeBase Technologies GmbH, http://www.primebase.org
      091006 11:00:30 InnoDB: Started; log sequence number 0 46409
      091006 11:00:32 [Note] Event Scheduler: Loaded 0 events
      091006 11:00:32 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections.
      Version: ‘5.1.37′ socket: ” port: 3306 Source distribution
      091006 11:31:11 [Note] C:\xampp\mysql\bin\mysqld.exe: Normal shutdown

      091006 11:31:12 [Note] Event Scheduler: Purging the queue. 0 events
      091006 11:31:12 InnoDB: Starting shutdown…
      091006 11:31:13 InnoDB: Shutdown completed; log sequence number 0 46409
      091006 11:31:13 [Note] PrimeBase XT Engine shutdown…
      091006 11:31:13 [Warning] Forcing shutdown of 1 plugins
      091006 11:31:13 [Note] C:\xampp\mysql\bin\mysqld.exe: Shutdown complete

      091006 11:34:10 InnoDB: Started; log sequence number 0 46409
      091006 11:34:11 [Note] Event Scheduler: Loaded 0 events
      091006 11:34:11 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for connections.
      Version: ‘5.1.35-ndb-7.0.7-cluster-gpl’ socket: ” port: 3306 MySQL Cluster Server (GPL)

      could u help me with this, it make my other node database didn’t work

      regards

      alex

    • hi andrew

      there are another log error, as shown bellow :

      091010 14:42:03 InnoDB: Started; log sequence number 0 46409
      091010 14:42:03 [Note] NDB: NodeID is 7, management server ‘localhost:1186′
      091010 14:42:03 [Note] NDB[0]: NodeID: 7, all storage nodes connected
      091010 14:42:03 [Note] Starting Cluster Binlog Thread
      Can’t start server: Bind on TCP/IP port: No such file or directory
      091010 14:42:03 [ERROR] Do you already have another mysqld server running on port: 3307 ?
      091010 14:42:03 [ERROR] Aborting

      this is confussing cos, i am using deffrent port on it 3306,3307,3308 as usuggested in your tutorial but it said Do you already have another mysqld server running on port : 3307

      please help me

      regards

      alex

    • Hi Alex,

      I’ll be experimenting more on Windows soon – I’ll let you know if I seem anything similar.

      Have you tried the suggestion from Xavi?

      If that doesn’t help then I’d suggest searching bugs.mysql.com to see if anyone has raised a similar issue and create a bug report if they haven’t.

      Andrew.

    • hi xavi

      what do you mean about mysql administrator wizard..? do you mean mysql administrator GUI or else..?

      regards

      Alex

    • Hi there, i cant seem to connect my nodes. Same problem with alex…

    • Something has recently changed in Cluster that meant that the mysqld processes wouldn’t connect to the Cluster. I’ve now replaced “ndbcluster=true” with just “ndbcluster” in the my.X.cnf files and it should now work again.

      Andrew.

    • Hi Andrew,

      Currently i am using different hosts for my SQL nodes. However, they do seem to work when i fire them to start. I have tried alot of method but it just didnt work.

      this is my my.cnf file on the different machines:

      [mysqld]
      ndbcluster
      ndb-connectstring=xxx.xxx.42.158
      datadir=”C:\…\data”
      port=3306
      server-id=3306
      —————————————
      this is my config.ini:
      [ndbd default]
      NoOfReplicas=4

      [ndb_mgmd]
      id=1
      hostname=xxx.xxx.42.158

      [ndbd]
      id=19
      hostname=xxx.xxx.178.197

      [ndbd]
      id=20
      hostname=xxx.xxx.12.195

      [ndbd]
      id=21
      hostname=xxx.xxx.178.197

      [ndbd]
      id=22
      hostname=xxx.xxx.17.234

      [mysqld]
      [mysqld]
      [mysqld]


    3 Trackbacks / Pingbacks

    Leave a reply

clusterdb Tweets

andrewmorgan Tweets

MySQL Cluster DB Pages

RSS LinuxJedi

Search

MySQL and MySQL Cluster are trademarks of Sun Microsystems
This site is independent of Sun/MySQL