Running MySQL Cluster over multiple Windows servers

Windows Logo
Following on from the earlier article on running MySQL Cluster on a single Windows host, this one looks at installing and running over multiple Windows machines.

Target Deployment

Target Deployment

In this post, the MySQL Cluster database will be split over 3 Windows machines:

  • 2 hosts with each running 1 data node
  • 3rd host running management node and 2 MySQL Servers

 

 

 

 

 

 

 

MySQL Cluster Downloads

MySQL Cluster Downloads

The first step is to download and install the software on each of the Windows hosts. There are 2 main ways that you can install MySQL Cluster on Windows – the first is an MSI installer and the second is extracting the files from a Zip archive – this article focuses on using the Zip file as the single host article used the MSI installer. You can get the Zip archive from the Cluster downloads page on mysql.com

Make sure that you choose the correct file MSI vs. Zip, 32 bit vs. 64 bit. Also make sure that you don’t download the source code rather than the binaries (you can tell the source archive as it’s a much smaller file).

Repeat this on each of the hosts that will run a Cluster node (or download it once and then copy it to each of the hosts).

Extract Zip archive

Extract Zip archive

Extract the contents of the Zip archive to “c:mysqlcluster”.

I prefer to keep the configuration and data files separate from the software and so I create a new folder “MySQL_ClusterMy_Cluster” in my home directory and in there create “conf” and “data” folders. Within the “data” folder on 192.168.0.19, create a sub-folder for each of the 2 MySQL Server (mysqld) processes and then copy “c:mysqlclusterdatamysql” to each – this creates the mysql database for each of the MySQL nodes containing data such as privileges and password.

Add MySQL executables to PATH

Add MySQL executables to PATH

To get access to the executables from the command line, add “c:mysqlclusterbin” to the PATH environment variable.

 

 

 

 

 

 

Create a “config.ini” file within the “conf” folder that was created on the host to run the management node (ndb_mgmd) – which in this example is 192.168.0.19:

[ndbd default]
noofreplicas=2
datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdata

[ndbd]
hostname=192.168.0.201
id=2

[ndbd]
hostname=192.168.0.4
id=3

[ndb_mgmd]
id=1
hostname=192.168.0.19

mysqld]
id=101
hostname=192.168.0.19

[mysqld]
id=102
hostname=192.168.0.19

As both of the MySQL Server nodes will also run on 192.168.0.19, we create a cnf file for each of them in that same “conf” folder:

my.101.cnf

[mysqld]
ndb-nodeid=101
ndbcluster
datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdatamysqld_1
port=3306
ndb-connectstring=192.168.0.19:1186

my.102.cnf

[mysqld]
ndb-nodeid=102
ndbcluster
datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdatamysqld_2
port=3307
ndb-connectstring=192.168.0.19:1186

Note that the “ndb-connectstring” is not strictly needed as the MySQL Servers happen to be on the same host as the management node but they’ve been included to make it clearer what to change if you moved those nodes to another machine.

Ports to open for MySQL Cluster nodes

Ports to open for MySQL Cluster nodes

The information from this table can help you set up the firewall rules to allow the nodes to communitcate with each other but if possible, turn off the firewall for connections between these 3 nodes.

The next step is to start the nodes (processes) but before then, a hint.

If you run each command from a different command prompt then your screen will quickly fill with Windows. Instead, rather than just typing “c: <command>” use “c: start /B <command>” (for example “c: start /B ndbd –initial”) so that multiple processes can be run from the same Window. To keep things simple, the “start /B” has been skipped for the rest of this article.

The first process to start is the management node; run the following from the “<home>DocumentsMySQL_ClusterMy_Cluster” folder on 192.168.0.19:

192.168.0.19: ndb_mgmd --initial -f conf/config.ini --configdir=./conf

Next, start the data nodes on 192.168.0.201 and 192.168.0.4:

192.168.0.201: ndbd -c 192.168.0.19:1186 --initial

192.168.0.4: ndbd -c 192.168.0.19:1186 --initial

and then finally start the MySQL Server nodes on 192.168.0.19:

192.168.0.19: mysqld --defaults-file=confmy.101.cnf
192.168.0.19: mysqld --defaults-file=confmy.102.cnf

Finally, check that all of the nodes are up and running:

192.168.0.19: 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    @192.168.0.201  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=3    @192.168.0.4  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.19  (mysql-5.1.39 ndb-7.0.9)

[mysqld(API)]   2 node(s)
id=101  @192.168.0.19  (mysql-5.1.39 ndb-7.0.9)
id=102  @192.168.0.19  (mysql-5.1.39 ndb-7.0.9)

Known limitation of running MySQL Cluster on Windows:

  • No angel process for data nodes -> processes will not be automatically restarted even if you set StopOnError to FALSE
  • Running nodes as Windows services is not really practical (as well as software limitations, would still need to activate processes through ndb_mgm)
  • Safe to run management node or MySQL Servers (or NDB API Apps) on Windows in production: will soon be supported
  • See open MySQL Cluster bugs reported for Windows:
  • You tell us! Help us get to GA quality on Windows:




114 comments

  1. Kriss Pakrer says:

    Is it possible you could post a guide on how to install MySQL Cluster on Multiple Windows Machines, but using the MSI installer.

    I have tried but unable to get the MySQL Cluster to see the other machine running wither the API Node or NDB Node.

    Many thanks,

    Kriss

  2. Kriss Pakrer says:

    Hi Andrew thank you for your response.

    Have you have any trouble remote access mysql using the binaries?

    I have posted a full report regarding this on

    http://bugs.mysql.com/bug.php?id=50417&thanks=2&notify=67

    I am unable to access mysql database from any remote machine either using a query browser or a command prompt.

    If I follow your example using the MSI on a single system, I can remote access mysql, but using the binaries I can’t. I have updated the user table to allow remote access, but still no luck.

    Many thanks for your reply and if you need any more information just let me know.

    Kriss

    • admin says:

      I tried this with a fresh Windows VM and got the same error (even with the mysql database
      created)…

      ERROR 1042 (HY000): Can’t get hostname for your address

      I resolved it by adding the hostnames/IP addresses to
      C:WindowsSystem32driversetchosts

      Of course, if DNS were set up for these hosts then that wouldn’t have been needed.

      This could well be a problem with the ‘mysql’ folder copied over from the zip archive – might have been better to install Perl and then execute the mysql_install_db.pl script.

      Andrew.

  3. Kriss Pakrer says:

    Andrew, thank you very much for your help.

    This has resolved the issue. I understand that this could of work straight away if install with perl, and I will look into this and let you know.

    Hopefully MySQL may release a fix where you shouldn’t have to edit the ‘Hosts’ file to allow remote access to the database, surely editing the user talbe should be enough.

    Again many thanks Andrew, a great help! 🙂

  4. Kriss Pakrer says:

    Hi Andrew,

    Just a quick question.

    When i shutdown the management node my datanode also shuts down. I run One computer with both a Management Node and a Data Node, and a Second computer with just a Data Node.

    I f i shut down the Managment computer completely, my secondary Data Node also shuts down.

    When i do run the second data node i use the query:
    ndb -c 10.0.10.4:1186

    Shut down the management node and they both come offline, and then i carnt access the mysql nodes if both Data Nodes come offline.

    Any reason for this?

  5. admin says:

    Hi Kriss,

    yes this is expected behaviour. When a subset of the data nodes lose contact with the rest, they try to figure out if it’s safe for them to stay up or not.

    In your case, the data node on the surviving host realises that it can’t communicate with the second data node and it can’t know whether that data node has died or not (could just be an issue with the network connection between the 2 hosts). The worst case scenario would be that both data nodes decided that they were the only survivors and continue to independently process transactions – this is referred to as split brain.

    To prevent the split brain scenario, the data node consults the arbitrator to see if it can stay up. By default, the management node acts as the arbitrator – as that too is uncontactable, the data node decides that the only safe option is to shut down.

    The way to make sure that your Cluster is highly available is to move your management node to a 3rd host.

    This is covered in much more detail at http://www.clusterdb.com/mysql-cluster/mysql-cluster-fault-tolerance-impact-of-deployment-decisions/

    Regards, Andrew.

  6. Kriss Pakrer says:

    Hi Andrew,

    Are you aware of any problems running a Data Node and a MySQL Node on the same host?

    Basically I am wanting to only use 5-Hosts, 3 of these hosts for MySQL + Data Nodes and the remaining 2-Hosts for 2-Management Nodes.

    I was just woundering if you were aware of any problem with NDB and MySql on the same Host?

    Many thanks again in advance,

    Kriss

  7. admin says:

    Hi Kriss,

    it’s possible to run data nodes and mysqld’s on the same hosts *but* there is potential for resource conflicts (memory, CPU, disk IO). If you’ve 5 hosts then consider:

    Host 1: Data node
    Host 2: Data node
    Host 3: mysqld(s) + management node
    Host 4: mysqld(s) + management node
    Host 5: mysqld(s)

    Regards, Andrew.

  8. Kriss Pakrer says:

    Hi Andrew,

    Are yuo aware of any way to keep the Cluster Server running using only one Data node and one MySQL Node.

    As far as i can you you always need two Data Nodes running otherwise the cluster will shutdown as one Data node is unable to keep the cluster alive.

    At present I am using 5 host using replication (not cluster) and if any machine fails there is no action needed, I can just bring it back online when I see it has failed. My replication server can go down to One Server, meaning Four server can go down and the service will still be alive, and working.

    Where as with cluster it looks like you always need two or three computer running in a cluster to keep it running for example one MySQL Node and two Data Nodes each one a single host, or MySQL Node and a Data node on the same Host and another data node.

    But again any host failed then the cluster fails, which dosn’t offer me a better system to what I am using now.

    Is there any way to allow cluster to run on a single MySQL Node and Data Node.

    Many thanks,

    Kriss

  9. admin says:

    Hi Kriss,

    you can run Cluster with a single data node. By setting NoOfReplicas to 1 and only have 1 [ndbd] section in configi.ini – however, this isn’t fault tolerant as if that 1 process or machine dies then you’ve lost your database. It is also possible to run with just 1 data node with NoOfReplicas=2 when the other data node has failed *but* only if that surviving data node can still contact the management server.

    If you’ve got access to 5 machines then just run the management node for 1 server on any of the 5 hosts that isn’t running data nodes for the same Cluster.

    One advantage of MySQL Cluster over regular replication is that the replication between the data nodes is synchronous and so if your 5 machines are in the same location then you should be able to build a far more fault tolerant solution than you have today. When using MySQL Cluster, you only tend to use the regular MySQL (asynchronous) replication when you want to replicate the data to a different storage engine or you want to have geographic redundancy.

    In the end, MySQL Cluster is a clustered database and it’s in that way (having a cluster of nodes) that it can scale AND provide high availability.

    Regards, Andrew.

  10. den says:

    Hi,
    really nice article and worked pretty well (tried in a test env)… btw it is obscure to me why the mysqld(s) resides on the same server of the management node instead on the nodes server; I’m asking this because I’ve read a nice article too for mysql (5.x) cluster on Ubuntu http://bieg.wordpress.com/2008/08/03/mysql-clustering-ubuntu/ where the roles are opposite.

    Thx for your patience.
    Den

  11. den says:

    Hi Andrew,
    yes, the management node it is independent, but I try to figure out why mysqld(s) described in your article

    datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdatamysqld_1
    and
    datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdatamysqld_2

    is different from the link that I’ve post before; in substance why:
    [mysqld(API)] 2 node(s)
    id=101 @192.168.0.19 (mysql-5.1.39 ndb-7.0.9)
    id=102 @192.168.0.19 (mysql-5.1.39 ndb-7.0.9)
    and why not
    [mysqld(API)] 2 node(s)
    id=101 @192.168.0.201 (mysql-5.1.39 ndb-7.0.9)
    id=102 @192.168.0.4 (mysql-5.1.39 ndb-7.0.9)

    Maybe I’ve missing something important in the link http://www.clusterdb.com/mysql-cluster/mysql-cluster-fault-tolerance-impact-of-deployment-decisions/ but I’m not so expert..

    Thx

  12. Hello, great article, thanks for this…

    I have tested out the previous cluster example you kindly posted and that worked fine. I added a copy of one of our development databases into the cluster and tested it with our application (a game) and all worked fine..

    Now I have a few other questions…

    Can I add additional MySQL nodes, Data nodes and Management nodes without taking down the cluster?

    In the original setup how can I close the cluster down and restart it, or do I have to manually restart each node?

    Are there any GUI tools for cluster yet? Like MySQL query browser and MySQL Administrator

    • admin says:

      It’s fairly straight forward to add new MySQL Server and data nodes (never tried adding management nodes).

      For MySQL Server nodes, just add the relevant [mysqld] sections to your config.ini file(s) and then restart the management nodes followed by all of the data nodes in sequence. You can then start your new MySQL Server process.

      It’s a little more complex for the data nodes as you’ll probably want to repartition your existing tables once you’ve created the new node-group. Full instructions can be found in this white paper: http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster7_architecture.php

  13. DY says:

    Hi… I followed all the instructions and got to the point where everything is started except the mysql servers:

    ***********
    ndb_mgm> show
    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 @10.192.58.16 (mysql-5.1.44 ndb-7.1.3, starting, Nodegroup: 0)
    id=3 @10.192.59.128 (mysql-5.1.44 ndb-7.1.3, starting, Nodegroup: 0)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @10.192.57.123 (mysql-5.1.44 ndb-7.1.3)

    [mysqld(API)] 2 node(s)
    id=101 (not connected, accepting connect from any host)
    id=102 (not connected, accepting connect from any host)
    ***********

    When I try to start mysql I got the errors:

    ***********
    C:UsersAdministratorDocumentsMySQL_ClusterMy_Cluster>mysqld –defaults-file
    =confmy.101.cnf –console –verbose
    100529 2:14:54 [Note] Plugin ‘FEDERATED’ is disabled.
    mysqld: Table ‘mysql.plugin’ doesn’t exist
    100529 2:14:54 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgr
    ade to create it.
    InnoDB: The first specified data file .ibdata1 did not exist:
    InnoDB: a new database to be created!
    100529 2:14:54 InnoDB: Setting file .ibdata1 size to 10 MB
    InnoDB: Database physically writes the file full: wait…
    100529 2:14:54 InnoDB: Log file .ib_logfile0 did not exist: new to be created

    InnoDB: Setting log file .ib_logfile0 size to 5 MB
    InnoDB: Database physically writes the file full: wait…
    100529 2:14:54 InnoDB: Log file .ib_logfile1 did not exist: new to be created

    InnoDB: Setting log file .ib_logfile1 size to 5 MB
    InnoDB: Database physically writes the file full: wait…
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    100529 2:14:55 InnoDB: Started; log sequence number 0 0
    100529 2:14:55 [Note] NDB: NodeID is 101, management server ‘10.192.57.123:1186

    100529 2:14:56 [Note] NDB[0]: NodeID: 101, no storage nodes connected (timed ou
    t)
    100529 2:14:56 [Note] Starting Cluster Binlog Thread
    100529 2:14:56 [ERROR] Fatal error: Can’t open and lock privilege tables: Table
    ‘mysql.host’ doesn’t exist
    ***********

    The only way mysql will even startup is if I use the c:mysqlclusterdata as the data directory, instead of following the instructions. But then it says no storage nodes connected:

    *********
    C:UsersAdministratorDocumentsMySQL_ClusterMy_Cluster>mysqld –defaults-file
    =confmy.101.cnf –console –verbose
    100529 2:34:58 [Note] Plugin ‘FEDERATED’ is disabled.
    100529 2:34:58 InnoDB: Started; log sequence number 0 44233
    100529 2:34:58 [Note] NDB: NodeID is 101, management server ‘10.192.57.123:1186

    100529 2:34:59 [Note] NDB[0]: NodeID: 101, no storage nodes connected (timed ou
    t)
    100529 2:34:59 [Note] Starting Cluster Binlog Thread
    100529 2:34:59 [Note] Event Scheduler: Loaded 0 events
    100529 2:35:14 [Warning] NDB : Tables not available after 15 seconds. Consider
    increasing –ndb-wait-setup value
    100529 2:35:14 [Note] mysqld: ready for connections.
    Version: ‘5.1.44-ndb-7.1.3-cluster-gpl’ socket: ” port: 3306 MySQL Cluster S
    erver (GPL)
    **********

    And then when I connect to the db and try to create db (gives first error below, but mysql prompt seems happy) and then a table (gives second error below) I get:

    **********
    100529 2:55:04 [Warning] NDB: Could not acquire global schema lock (4009)Cluste
    r Failure
    100529 2:55:50 [Warning] NDB: Could not acquire global schema lock (4009)Cluste
    r Failure
    **********

    Any one know what’s going on???

    Thanks,
    DY

  14. DY says:

    Hi Andrew,

    Please ignore my previous post, I figured out. Now on the data nodes I keep getting warning: timerhandlinglab with diffs of mostly 62 to 63. I have checked the times of my win 2008 servers and they seem to be in sync.

    I have searched online but couldn’t find any other useful info about this warning and how to resolve it.

    Thanks,
    DY

  15. admin says:

    Hi DY,

    not sure if it will fix it for you but have you taken a look at http://forums.mysql.com/read.php?25,285742,285922#msg-285922 ?

    Regards, Andrew.

  16. Confused says:

    Hi Andrew,

    The data on SQL data nodes is not in sync.

    Do I have to setup replication separately between the data nodes as well to keep them in sync?

    I have chosen mysql cluster setup over SQL server 2008 setup.

    Your help would be really appreciated.

    • admin says:

      If a table is stored in MySQL Cluster (by specifying “engine=ndb” when creating the table) then the contents of that data will be visible through all MySQL Servers that are part of the Cluster. The data is automatically replicated between the data nodes in order to achieve High Availability. If you specify a different storage engine (or don’t specify one at all) when creating the table then the data is held locally (and independently) within each MySQL Server. If you created the table with a different storage engine then you can migrate it to Cluster with “mysql> ALTER TABLE engine=ndb;”.

      Regards, Andrew.

  17. Carno says:

    DY;

    I have the same error with you, mysql server would not run.

    Logs:
    100717 12:31:30 InnoDB: Started; log sequence number 0 44233
    100717 12:31:30 [Note] NDB: NodeID is 50, management server ‘localhost:1186’
    100717 12:31:30 [Note] NDB[0]: NodeID: 50, all storage nodes connected
    100717 12:31:30 [Warning] NDB: server id set to zero will cause any other mysqld with bin log to log with wrong server id
    100717 12:31:30 [Note] Starting Cluster Binlog Thread
    100717 12:31:30 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

    How did you manage to overcome it.

  18. Rabin says:

    Do I need to install MySQL Community Server? or just download the zip file then extract followed by the tutorial?

  19. Nguyen Huu Tinh says:

    when I use mysql proxy to use load balancer. but when 1 mysqld down, it not redirect to another mysqld.
    Can you help me solve this problem.
    Thanks

    • admin says:

      I’m no expert on MySQL proxy but a bit of Googling came up with this incantation…

      mysql-proxy –proxy-backend-addresses=serverA:3306 –proxy-backend-addresses=serverB:3306 –proxy-backend-addresses=serverC:3306 –proxy-read-only-backend-addresses=serverD:3306

      … Note that when people talk about load balancing with MySQL, it will usually be focused on read-only queries but with Cluster you can also load balance writes as all of the changes are actually made in the data nodes and so are visible immediately to all MySQL Servers in the Cluster. Because of this, you probably don’t need to specify any servers with the proxy-read-only-backend-addresses option.

      If this doesn’t work then you can try the MySQL Proxy forum at http://forums.mysql.com/list.php?146

      Andrew.

  20. Duy MInh says:

    When I run ndbd, i have error:
    2011-01-17 10:34:41 [ndbd] INFO — Angel connected to ‘10.88.40.29:1186’
    2011-01-17 10:34:41 [ndbd] ERROR — Failed to allocate nodeid, error: ‘Error
    : Could not alloc node id at 10.88.40.29 port 1186: Connection done from wrong h
    ost ip 10.88.40.29.’

    How can I fix it
    Regards

    • admin says:

      Hi Duy,

      this suggests that you’re trying to start the ndbd process on the ‘wrong’ host.

      In your config.ini file you specify the IP address of the host where each of the processes should run. Check that you’re running the ndbd.exe on the server with the IP address that you specify in the [ndbd] sections of the config.ini file. You can also check where the Cluster expects the data node processes to be run by executing…

      ndb_mgm -e show

  21. Reuel says:

    Hey, is fully functional for windows 2008 server now? I means, can I use it in a production environment?

    Thanks in advances,
    Reuel

  22. Andrew says:

    Hi,

    I am having problems, when i start up my data nodes, I get the following issue: “Couldnt Start daemon error failed to open logfile …. for write errno2”

    I think its a windows permissions issue, for the data node contacting the mangement node – any ideas on how to fix it?

  23. Andrew says:

    Hi,

    I have looked into the permissions. But cant figure out how to allow another computer access to the file system- its a windows problem i know, but its holding up the whole thing.

    Im thinking of setting up a domain, with both computers in it and then setting permission for the second computer to access the file system. Sound Right? Or is this a way too complicated solution to a permissions issue?

    Thanks for the help, im running around in circles!

    • admin says:

      Hi Andrew,

      it should only be local processes writing to the local file system (I’ve never had to set up cross-system file access) – a key feature of MySQL Cluster is that it’s shared-nothing with no need for shared storage. Firewalls on the other hand may need opening up (ideally turned off between the Cluster hosts).

      Have you tried getting everything running on a single server first?

      Andrew.

  24. Andrew says:

    Ok,

    I did set it up on the single server first and it worked fine.

    Once i moved it to 2 servers the problems started, particularly when i started the data nodes. This is what I received:

    ## Start Data Node B on 50.57.80.204 – using multithreading option ndbmtd.exe

    C:MySQL DATA NODE 2mysql-cluster-gpl-noinstall-7.1.15-winx64mysql-cluster-gpl-7.1.15-winx64binndbmtd.exe -c 184.106.79.42:1186

    I then get this:

    INFO- Angel connected to 184.106.79.42:1186
    INFO- Angel Allocated nodeid: 3
    WARNING- cannot change directory to ‘c:my_clusterndb_data’, error 2
    ERROR- Couldnt start as daemon, error Failed to open logfile ‘c:my_clusterndb_datandb_3_out.log’ for write, errno: 2

    Not sure what it means, the log file is there, so i guess it was a permissions issue – any ideas?

    Thanks again!

  25. Septian says:

    what about error when i try this :
    C:UsersSav_MySQL_clustermy_cluster>start /B ndb_mgmd –initial -f conf/confi
    g.ini –configdir=./conf

    C:UsersSav_MySQL_clustermy_cluster>MySQL Cluster Management Server mysql-5.1
    .56 ndb-7.1.18
    2011-12-26 09:18:29 [MgmtSrvr] ERROR — Could not determine which nodeid to u
    se for this node. Specify it with –ndb-nodeid= on command line

    i’m newbie…

  26. Krishnalal says:

    Hi
    any one know how to correct this error when i run ndb_mgmd by giving host ip in the config.ini file

    2012-01-13 12:56:34 [MgmtSrvr] ERROR — at line 16: Mixing of localhost (default for [NDBD]HostName) with other hostname(172.16.24.70) is illegal
    Thanks in advance
    Krishnalal

    • admin says:

      Krishnalal – you are using a mix of “localhost” and real host names or IP addresses in your config.ini file – this isn’t allowed. Replace localhost with the machine’s IP address or 127.0.0.1

      Andrew.

  27. Krishnalal says:

    Hi Andrew
    Thanks for the help. it worked fine.
    Now i am facing another problem, the mysqld is started and then exited when i executed it.
    I am having Two Pcs, one pc running management, data and mysqld’s. another pc with a data node.
    The following i have done…
    C:MySQLcluster>mysqld –defaults-file=confmy1.cnf

    C:MySQLcluster>mysqld –defaults-file=confmy2.cnf

    C:MySQLcluster>ndb_mgm -e show
    Connected to Management Server at: localhost:1186
    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 @172.16.24.70 (mysql-5.1.56 ndb-7.1.18, starting, Nodegroup: 0, Master)

    id=3 @172.16.24.77 (mysql-5.1.56 ndb-7.1.18, starting, Nodegroup: 0)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @172.16.24.70 (mysql-5.1.56 ndb-7.1.18)

    [mysqld(API)] 3 node(s)
    id=4 (not connected, accepting connect from 172.16.24.70)
    id=5 (not connected, accepting connect from 172.16.24.70)
    id=6 (not connected, accepting connect from 172.16.24.70)
    Pls help..
    Thanks in Advance
    Krishnalal

  28. Krishnalal says:

    Hi
    can any one know why this error occurs when mysqld is executed….
    120120 10:06:15 [Note] Plugin ‘FEDERATED’ is disabled.
    C:MySQLMySQLServer5.0binmysqld: Table ‘mysql.plugin’ doesn’t exist
    120120 10:06:15 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    120120 10:06:15 InnoDB: Initializing buffer pool, size = 8.0M
    120120 10:06:15 InnoDB: Completed initialization of buffer pool
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    120120 10:06:15 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    120120 10:06:16 InnoDB: Started; log sequence number 0 44233
    120120 10:06:51 [Warning] NDB: server id set to zero – changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
    120120 10:06:51 [Note] Starting Cluster Binlog Thread
    120120 10:06:51 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

    Pl help
    Regards
    Krishnalal

    • admin says:

      Hi Krishnalal,

      are you sure that you copied the mysql database files into the data directory for the MySQL Server(s) and that they have permissions set correctly for whatever user is being used to run the mysqld process?

      Regards, Andrew.

  29. Krishnalal says:

    Hi Andrew

    yes, i have copied the mysql database files in to the data directory as specified in the blog.
    How to set permissions for running mysqld process.?
    Pls help as early as possible.becoz i am stuck up with the cluster implementation.
    Thanks in advance
    Krishnalal

  30. admin says:

    Hi Krishnalal,

    in your .cnf file(s) you should have specified a “datadir” folder. Check that folder to make sure that it exists (on the host where you’re running the mysqld process). In there you should see a bunch of folders (that you’ve previously copied there) including one called “mysql” – that sub-folder should contain the files that the error message is referring to.

    Double check that you’re including the correct .cnf file with the –defaults-file when you start the mysqld process.

    You should be able to check which users have read/write access to a file by right clicking it, selecting properties and then Security – details depend on what version of Windows you’re running.

    Andrew.

  31. Krishnalal says:

    Hello
    my mysqld is started and stoped after 1 minute.
    The error i got is

    120123 17:57:08 [Note] Plugin ‘FEDERATED’ is disabled.
    mysqld: Table ‘mysql.plugin’ doesn’t exist
    120123 17:57:08 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    120123 17:57:08 InnoDB: Initializing buffer pool, size = 8.0M
    120123 17:57:08 InnoDB: Completed initialization of buffer pool
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    120123 17:57:08 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    120123 17:57:09 InnoDB: Started; log sequence number 0 44233
    120123 17:57:44 [Warning] NDB: server id set to zero – changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
    120123 17:57:44 [Note] Starting Cluster Binlog Thread
    120123 17:57:44 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

    I followed all the steps, that Andrew instructed.
    Can any help..
    Krishnalal

  32. Rajini says:

    Hi I have configured MYSQL Cluster 5.0 on windows xp(single system).

    But i m unable to launch Management node, getting the following error, Can any show me the right way to do this.

    C:MysqlCluster>mysqld
    ‘mysqld’ is not recognized as an internal or external command,
    operable program or batch file.

    Rajini.

    • admin says:

      Hi Rajini,

      a couple of comments

      1) You should switch to the latest version of MySQL Cluster – download Cluster 7.2 from http://www.mysql.com/downloads/cluster/
      2) mysqld is the MySQL Server process; ndb_mgmd is the management node
      3) Make sure that you’ve added the MySQL bin folder to your Windows path if you don’t want to give the full path name every time you run a command

      Regards, Andrew.

  33. Rajini says:

    Hi Andrew,
    Thanks for the reply.
    Iam using mysql 5.5 version and cluster also 5.5.
    Is it possible combination of server 5.5 n cluster 7.2?
    – Where i need to add this bin folder path to my windows ?
    -how i need to check weather my ports are running or not? if so how i need to start
    -i have node 3306,3307,3308 and i havee added this ports in firewall tab i windows XP.

    Rajini.

    • admin says:

      Fore the firewall you need to open up the ports needed for clients to connect to each MySQL Server (3306 by default). You should also open up 1186 for any machines accessing the ndb_mgmds (including all hosts in the Cluster). By default, the port used for each data node to connect to all other nodes is allocated dynamically but you can fix it by setting ServerPort in each [ndbd] section (make sure that no 2 data nodes on the same host use the same value) and then add that to the firewall exceptions.

      Ideally just turn off the firewall or at least between the hosts making up the Cluster.

      It varies slightly between versions of Windows but to set the Path variable,try navigating through Control Panel/System/Advanced System Settings/Environment Variables/System Variables

      Regards, Andrew.

  34. Des says:

    Hi Andrew,

    Followed the steps as above but encountered the following error when I tried to start mysqld:

    C:Program FilesMySQLclusterMySQL_ClusterMy_Cluster>mysqld –defaults-file=c
    onfmy.102.cnf.txt -show
    120310 2:49:27 [Warning] Can’t create test file C:Program FilesMySQLcluster
    owdes.lower-test
    120310 2:49:27 [Warning] Can’t create test file C:Program FilesMySQLcluster
    owdes.lower-test
    mysqld: Can’t change dir to ‘C:Program FilesMySQLclusterow’ (Errcode: 2)
    120310 2:49:28 [ERROR] Aborting

    120310 2:49:28 [Note] mysqld: Shutdown complete

    It seems like a permission issue-I did grant full permission to everyone. I did followed the process(mgmt node–data nodes–sql node).Do you have any ideas what causing the error?

  35. Luca says:

    Hi,
    I tried to configure the cluster mysql on windows following the step by step guide and running the command to check the status of the cluster I get this result:
    ndb_mgm> show
    cluster Configuration
    ———————
    [ndbd (NDB)] 2 node (s)
    id = 2@192.168.1.2 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0,
    id = 3@192.168.1.3 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0)

    [ndb_mgmd (MGM)] 1 node (s)
    id = 1@192.168.1.4 (mysql-5.5.19 ndb-7.2.4)

    [mysqld (API)] 2 node (s)
    id = 101 (not connected, accepting connect from 192.168.1.4)
    id = 102 (not connected, accepting connect from 192.168.1.4)

    My doubt and on the [mysqld (API)] 2 node (s).

    It is correct what I see or the cluster is not working properly?

    • admin says:

      Hi Luca,

      this is showing that the mysqld processes have not connected to the Cluster. Check if there are any errors when you start the mysqlds. Use the verbose option to get more information if required.

      One possible causes… make sure you use the mysqld binaries that come as part of the MySQL Cluster install – other versions will not work

      Check if the mysqld process is actually running and if you can connect to it, run SHOW ENGINES to check that ndbcluster is enabled.

      Regards, Andrew.

  36. Saraswathi says:

    Hi,

    I have followed your step by step guide for mysql cluster configuration on Windows. The data nodes and managed servers have started properly. When i start mysql nodes i get the below error.

    ———————-

    120326 21:17:51 [Note] Plugin ‘FEDERATED’ is disabled.
    mysqld: Table ‘mysql.plugin’ doesn’t exist
    120326 21:17:51 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    120326 21:17:51 InnoDB: The InnoDB memory heap is disabled
    120326 21:17:51 InnoDB: Mutexes and rw_locks use Windows interlocked functions
    120326 21:17:51 InnoDB: Compressed tables use zlib 1.2.3
    120326 21:17:51 InnoDB: Initializing buffer pool, size = 128.0M
    120326 21:17:51 InnoDB: Completed initialization of buffer pool
    120326 21:17:51 InnoDB: highest supported file format is Barracuda.
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    120326 21:17:51 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    120326 21:17:51 InnoDB: Waiting for the background threads to start
    120326 21:17:52 InnoDB: 1.1.8 started; log sequence number 1595675
    120326 21:17:52 [Note] NDB: NodeID is 101, management server ‘192.168.92.120:1186’
    120326 21:17:52 [Note] NDB[0]: NodeID: 101, all storage nodes connected
    120326 21:17:52 [Warning] NDB: server id set to zero – changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
    120326 21:17:52 [Note] Starting Cluster Binlog Thread
    120326 21:17:52 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

    ——————

    I have used Zip archive for cluster installation and mysql coming with cluster.

    Can someone help ?

    Thanks !

  37. ramdan says:

    hi andrew,
    i’ve been trying to run command :

    192.168.0.4: ndbd -c 192.168.0.19:1186 –initial

    from the mgmt node computer,but there was an error :

    “The filename,directory name,or volume label syntax is incorrect”

    how to solve this problem?thanks you

    • admin says:

      Hi Ramdan,

      I haven’t seen that error but on Windows, the format of the path name in the config.ini and my.cnf files is very specific (e.g. forward vs. back slashed) so make sure that you have things entered exactly as they should be.

      When you’re starting up the data nodes it will be using the contents of the config.ini file so make sure that things like the datadir are correctly defined.

      Regards, Andrew.

  38. ramdan says:

    this my config.ini

    [ndbd default]
    noofreplicas=1
    datadir=C:UsershamkaMYSQL_ClusterMy_Clusterdata

    [ndbd]
    hostname=192.168.0.4
    id=3

    [ndb_mgmd]
    id=1
    hostname=192.168.0.19

    [mysqld]
    id=101
    hostname=192.168.0.19

    [mysqld]
    id=102
    hostname=192.168.0.19

  39. ramdan says:

    yes,and i’ve copied data from mysqlclusterdatamysql into that folder too

  40. Kiril says:

    hi Andrew,

    I did a rather simpler installation of the cluster on windows server 2008 (1 server with mangament and sql nodes and 2 servers, each w. one data node) but when I start the sql node (all other processes started up fine) I get this ‘listener’ warning. Do you have an idea what is going wrong?

    Thank you so much,
    – Kiril

    c:mysqlbin>mysqld –console
    120521 23:51:02 [Note] Plugin ‘FEDERATED’ is disabled.
    120521 23:51:02 InnoDB: The InnoDB memory heap is disabled
    120521 23:51:02 InnoDB: Mutexes and rw_locks use Windows interlocked functions
    120521 23:51:02 InnoDB: Compressed tables use zlib 1.2.3
    120521 23:51:02 InnoDB: Initializing buffer pool, size = 128.0M
    120521 23:51:02 InnoDB: Completed initialization of buffer pool
    120521 23:51:02 InnoDB: highest supported file format is Barracuda.
    120521 23:51:03 InnoDB: Waiting for the background threads to start
    120521 23:51:04 InnoDB: 1.1.8 started; log sequence number 1595675
    120521 23:51:04 [Note] NDB: NodeID is 4, management server ‘192.168.1.2:1186’
    120521 23:51:34 [Note] NDB[0]: NodeID: 4, some storage nodes connected
    120521 23:51:34 [Warning] NDB: server id set to zero – changes logged to bin log
    with server id zero will be logged with another server id by slave mysqlds
    120521 23:51:34 [Note] Starting Cluster Binlog Thread
    120521 23:51:34 [Note] Event Scheduler: Loaded 0 events
    120521 23:51:34 [Note] mysqld: ready for connections.
    Version: ‘5.5.20-ndb-7.2.5-gpl’ socket: ” port: 3306 MySQL Cluster Community
    Server (GPL)
    120521 23:51:35 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
    120521 23:51:45 [Warning] execute index stats listener failed: error 1427 line 2
    537
    120521 23:51:46 [Warning] create index stats listener failed: error 4716 line 24
    83
    120521 23:51:47 [Warning] create index stats listener failed: error 4716 line 24
    83
    120521 23:51:48 [Warning] create index stats listener failed: error 4716 line 24
    83

  41. Devin Ellis says:

    Saraswathi

    You need to also copy the ndbinfo folder over to the mysqld_1 folder.

    So you should have two folders in mysqld_1
    mysql
    ndbinfo

    same goes with mysqld_2

  42. neo says:

    hi Andrew,
    I have the same probleme like Kiril. just like this:
    Version: ‘5.5.27-ndb-7.2.8-cluster-gpl’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Cluster Community Server (GPL)
    121108 14:25:07 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
    121108 14:25:26 [Warning] execute index stats listener failed: error 1427 line 2537
    121108 14:25:28 [Warning] create index stats listener failed: error 4716 line 2483
    121108 14:25:29 [Warning] create index stats listener failed: error 4716 line 2483
    121108 14:25:30 [Warning] create index stats listener failed: error 4716 line 2483
    121108 14:25:31 [Warning] create index stats listener failed: error 4716 line 2483
    121108 14:25:32 [Warning] create index stats listener failed: error 4716 line 2483

    Could you give me some advice? thank you!

  43. neo says:

    Andrew,

    Thank you for your respond! I have solved this problem in another way. I re-installed Mysql Server,rebuiding a new environment. And I disabled any firewalls on each computer. Then the simple cluster can be started.

    I think it is night in your country now. And Good night!

    Neo.

  44. aasim1967 says:

    After making mysql cluster, how do I use database. I mean how to connect to database and what is the default credential to use. Secondly I need to know how to coonect MySql database throug SQLYOG.

    Regards
    Aasim shaikh

    • andrew says:

      Aasim,

      you should be able to connect to the mysqld’s in the Cluster just as if they were ‘regular’ ones. I’ve no experience with SQLYOG and so can’t help you with that.

      Regards, Andrew.

  45. aasim1967 says:

    Thanks Andrew,

    I downloaded MySQL Workbench Tools which is really fantastic and helpful to perform most of the administrative task.

    Regards
    Aasim Shaikh

  46. Muthu says:

    Hi Andrew,

    I tried the steps slightly modifying for my reqts.

    MGM – 192.168.0.201
    NBD – 192.168.0.122
    NBD – 192.168.0.202
    API – 192.168.0.122

    Except for the API others worked fine.

    For the API, I created a .conf file with ndb_connectstring. When I try mysqld without datadir value in .conf file, it attempts to connect which I ensured by checking the log file in the MGM server. But when the datadir is present in .conf file, there is attempt to connect to MGM though the mysql is connected and the ndbcluster process is also running.

    Any suggestions ?

  47. Alex says:

    Hello Andrew, has the same problem as Luca, no errors when I start mysqld, the cluster log shows that I get Alert Node 101 disconnected.
    Have followed your guide and done exactly as it says

    • andrew says:

      Hi Alex,

      Have you tried starting the mysqld with the verbose option to get more information?

      Is the mysqld running and if so, can you can connect to it, run SHOW ENGINES to check that ndbcluster is enabled.

      Are you using the mysqld binaries that come as part of the MySQL Cluster install – other versions will not work

      What version of MySQL Cluster are you using?

      Andrew.

  48. Alex says:

    Hello Andrew, I have tried to start mysqld in the verbose option but nothing happens.
    Ndbcluster is disabled when I start mysqld-console, how do I get it enabled?
    yes I use mysqld binaries that came with the installation, have done exactly as your instructions.
    I’m using version 5.5, mysql, and think there is 7.2 MySQL Cluster

    / Alex

    • andrew says:

      Hi Alex,

      could you please confirm that there is no firewall blocking ports between the hosts making up the Cluster?

      Could you also please share your config.ini and my.cnf files?

      Andrew.

  49. Alex says:

    Hello, have opened port 3306, so there should not be the problem

    This is my config.ini file

    [ndbd default]
    noofreplicas=2
    datadir=c:MySQL_ClusterMy_Clusterdata

    [ndbd]
    hostname=192.168.1.109
    Nodeid=2

    [ndbd]
    hostname=192.168.1.112
    Nodeid=3

    [ndb_mgmd]
    Nodeid=1
    hostname=192.168.1.114

    mysqld]
    Nodeid=101
    hostname=192.168.1.114

    [mysqld]
    Nodeid=102
    hostname=192.168.1.114
    ////////////////////////////////////////
    ndb_1_cluster.log file

    2013-04-04 19:57:48 [MgmtSrvr] INFO — Nodeid 101 allocated for API at 192.168.1.114
    2013-04-04 19:57:48 [MgmtSrvr] INFO — Node 101: mysqld –server-id=0
    2013-04-04 19:57:48 [MgmtSrvr] INFO — Node 2: Node 101 Connected
    2013-04-04 19:57:48 [MgmtSrvr] INFO — Node 2: Node 101: API mysql-5.5.27 ndb-7.2.8
    2013-04-04 19:57:48 [MgmtSrvr] INFO — Node 3: Node 101 Connected
    2013-04-04 19:57:48 [MgmtSrvr] INFO — Node 3: Node 101: API mysql-5.5.27 ndb-7.2.8
    2013-04-04 19:57:49 [MgmtSrvr] ALERT — Node 3: Node 101 Disconnected
    2013-04-04 19:57:49 [MgmtSrvr] INFO — Node 3: Communication to Node 101 closed
    2013-04-04 19:57:49 [MgmtSrvr] INFO — Node 2: Communication to Node 101 closed
    2013-04-04 19:57:49 [MgmtSrvr] ALERT — Node 2: Node 101 Disconnected
    2013-04-04 19:57:52 [MgmtSrvr] INFO — Node 3: Communication to Node 101 opened
    2013-04-04 19:57:52 [MgmtSrvr] INFO — Node 2: Communication to Node 101 opened
    2013-04-04 19:58:51 [MgmtSrvr] INFO — Nodeid 101 allocated for API at 192.168.1.114
    2013-04-04 19:58:51 [MgmtSrvr] INFO — Node 101: mysqld –server-id=0
    2013-04-04 19:58:51 [MgmtSrvr] INFO — Node 2: Node 101 Connected
    2013-04-04 19:58:51 [MgmtSrvr] INFO — Node 2: Node 101: API mysql-5.5.27 ndb-7.2.8
    2013-04-04 19:58:51 [MgmtSrvr] INFO — Node 3: Node 101 Connected
    2013-04-04 19:58:51 [MgmtSrvr] INFO — Node 3: Node 101: API mysql-5.5.27 ndb-7.2.8
    2013-04-04 19:58:53 [MgmtSrvr] ALERT — Node 3: Node 101 Disconnected
    2013-04-04 19:58:53 [MgmtSrvr] INFO — Node 3: Communication to Node 101 closed
    2013-04-04 19:58:53 [MgmtSrvr] INFO — Node 2: Communication to Node 101 closed
    2013-04-04 19:58:53 [MgmtSrvr] ALERT — Node 2: Node 101 Disconnected
    2013-04-04 19:58:57 [MgmtSrvr] INFO — Node 2: Communication to Node 101 opened
    2013-04-04 19:58:57 [MgmtSrvr] INFO — Node 3: Communication to Node 101 opened

    And this is my.101.cnf

    [mysqld]
    ndb-nodeid=101
    ndbcluster
    datadir=c:MySQL_ClusterMy_Clusterdatamysqld_1
    port=3306
    ndb-connectstring=192.168.1.114:1186

  50. Alex says:

    Hi Andrew, ok I will try this.
    But why is ndbcluster disabled?, Have I missed something? or could it be that a firewall is blocking

    / Alex

    • andrew says:

      Alex – yes, it could be that the firewall is stopping the mysqld from connecting to the data nodes. In that case there’s no way for the mysqld to store data using the ndbcluster storage engine and so it would be disabled.

  51. NGnasso says:

    Hi Andrew,
    a very newbie question. In this specific configuration, are the data exactly the same on a node then other?

    Thanks in advance

    • andrew says:

      NGnasso,

      the data is held in the data nodes rather than in the MySQL Servers.Each MySQL Server connects to every data node and so they all access the same data. If you have 2 data nodes then they form a node group and they each store all of the data but only access half of it; if you have 4 data nodes then they form 2 node groups and each node group stores half the data,….

      Andrew.

  52. Alex says:

    Hi, have turned off the firewall but it does not work, very strange.

  53. NGnasso says:

    Hi Andrew, i tried 2 different configurations for my tests. I have 4 hosts and my topology is: 2 management nodes, 2 data nodes and 2 sql nodes.
    My first try was:
    management1 : 172.25.49.1
    management2 : 172.25.49.2
    data node 1 : 172.25.49.3
    data node 2 : 172.25.49.4
    SQL node 1 : 172.25.49.3
    SQL node 2 : 172.25.49.4
    In this configuration, my cluster goes up, i connect through HeidiSQL on 172.25.49.3/4 on local host, create a table engine=ndbcluster in my test database and data are replicated between each data node.
    Now i’m trying to configure a cluster with a topology like yours. The SQL nodes are on the management host instead of data node.
    I launch the cluster and it goes up, but if i try to launch HeidiSQL connecting to localhost where mysqld runs, to do the same operations, it creates the test db on localhost (management node) instead of data nodes. I’m new about mysql and i think i’m missing something.
    Thanks in advance.

    • andrew says:

      Hi Nicola,

      first of all, what makes you believe that the data is not being stored in the data nodes?

      If there is a firewall blocking ports between the SQL node hosts and the data node hosts then the mysqlds would not be able to communicate with the data nodes and so would revert to storing the data using the InnoDB storage engine (rather than ndbcluster). This should be easy to check…

      mysql> SHOW ENGINES; # Check that NDBCLUSTER is enabled

      c: ndb_mgm -e show :: Confirm that both the MySQL Servers, Data Nodes and both Management Nodes are shown as part of the Cluster.

      If you can’t turn off the firewalls then you can force the mysqld/ndbd connection to use as specific port by specifying the ServerPort parameter in the config.ini files.

      Regards, Andrew.

  54. NGnasso says:

    There is something not clear for me surely. I have to create a database through SQL node…but should i find this database on storage node or on sql node? When i tried to do it, i found this database on SQL node, instead of storage node. So my question is, is the db structure (tables,data type, etc) on sql node and the data physically on storage, or should i find db structure and date on storage?

    Thanks

    • andrew says:

      Nicola,

      for Cluster tables, you’ll see files on the MySQL Server hosts *but* these are for the data schema and they don’t hold the data. On the data nodes, the data is stored but it isn’t 1 file per table and you won’t find any files named after the tables. Those data node files contain the checkpoints and redo logs for in-memory tables as well as the table spaces and undo logs for disk-based tables/columns.

      Andrew.

  55. NGnasso says:

    Thanks Andrew, now everything is much clearer. But now i have another problem. One of the SQL nodes won’t goes up. It’s on the management2; All the cluster is up except it. I copied the data dir with all db created from sql node 1 to the second. The error message shown by the –console –verbose is NDB : Tables not available after 30 secs. I checked for communication between storage node hosts and management2/sql node host and they pings each other.

    Thanks a lot

  56. NGnasso says:

    mysqld config is:

    [mysqld]
    ndbcluster
    datadir=C:/mysql-cluster/data
    ndb-connectstring=DGM1,DGM2

    [mysql_cluster]
    ndb-connectstring=DGM1,DGM2

    The SQL Node on DGM1 (my management host 1) is up and can send queries to data nodes. The console shows me [Note] NDB[0]: NodeID 5, all storage node connected.
    About second SQL Node on management 2 (DGM2) the log is
    [Note] NDB[0] : NodeID 6, no storage nodes connected (time out).

    • andrew says:

      Hi Nicola,

      my suspicion would be that your firewall is preventing the MySQL Server connecting to one or more of the nodes. The host running the mysqld needs port 1186 open to connect to the management node and (by default) any possible dynamically allocated port to each of the data nodes. You can reduce how many ports you need to open up by fixing the port used for mysqlds to connect to data nodes by setting the ServerPort config parameter.

      As a first step, if you’re able to disable the firewall on all of these machines then try that out just to confirm or rule it out as the cause.

      Andrew.

  57. NGnasso says:

    Hi Andrew, firewalls were down, pings reached every host, i dunno. Btw, the configuration has been changed again.
    Now i have 4 management, 2 data node and 2 SQL node splitted on 4 hosts. Every host has a management, 2 of them have 1 data node and 1 sql node too.
    I start the cluster and all 4 management nodes go up, but the 2 data node remain in the starting state, immediatly after the phase 0 (completed), without producing any error message.

  58. NGnasso says:

    I know, but i don’t decide the topology, i only execute. So i’m trying to run this configuration, i’m still having the same problem, data nodes won’t pass the starting phase 0.

    • andrew says:

      The only things I can suggest are:

      – Make sure that the management nodes all have identical config.ini files and are started with “ndb_mgmd –initial”
      – Make sure that the firewalls really are turned off (if local mysqlds can connect but remote ones can’t then this is the usual suspect)
      – Start the data nodes with the –initial option (assuming you don’t have any data in the database yet!)

      Other than that, if it still fails then check the MySQL Server logs and the Cluster logs on the management nodes. If there’s still no smoking gun then try posting your config.ini and my.cnf files here, together with the output from “ndb_mgm -e show” and the commands you’re using to start each of your nodes.

      Andrew.

  59. NGnasso says:

    I only have windows firewall and it’s deactivated. When i start any node i receive this message: Failed to report event to event log, error: 1502

    My config.ini is:
    #
    # Configuration file for MyCluster
    #

    [NDB_MGMD DEFAULT]
    Portnumber=1186

    [NDB_MGMD]
    NodeId=49
    HostName=ART1
    DataDir=C:mysql-clustermycluster-data49data
    Portnumber=1186

    [NDB_MGMD]
    NodeId=52
    HostName=ART2
    DataDir=C:mysql-clustermycluster-data52data
    Portnumber=1186

    [NDB_MGMD]
    NodeId=63
    HostName=DGM1
    DataDir=C:mysql-clustermycluster-data63data
    Portnumber=1186

    [NDB_MGMD]
    NodeId=64
    HostName=DGM2
    DataDir=C:mysql-clustermycluster-data64data
    Portnumber=1186

    [TCP DEFAULT]

    [NDBD DEFAULT]
    NoOfReplicas=2

    [NDBD]
    NodeId=1
    HostName=DGM2
    DataDir=C:mysql-clustermycluster-data1data

    [NDBD]
    NodeId=3
    HostName=DGM1
    DataDir=C:mysql-clustermycluster-data3data

    [MYSQLD DEFAULT]

    [MYSQLD]
    NodeId=61
    HostName=DGM1

    [MYSQLD]
    NodeId=50
    HostName=DGM2

    I do not use my.cnf but run processes through options by command line

    Management Nodes:
    ndb_mgmd –initial –ndb-nodeid=49 –config-dir=c:mysql-clustermycluster-data49data –config-file=c:mysql-clustermycluster-data49dataconfig.ini

    Data Nodes:
    ndbd –ndb-nodeid=3 –ndb-connectstring=ART1:1186,ART2:1186,DGM1:1186,DGM2:1186

    SQL Node:
    mysqld –no-defaults –datadir=C:mysql-clustermycluster-data61data –tmpdir=C:mysql-clustermycluster-data61datatmp –basedir=C:/mysql-cluster/ –socket=C:mysql-clustermycluster-data61sockmysql.socket –port=3306 –ndbcluster –ndb-nodeid=61 –ndb-connectstring=ART1:1186,ART2:1186,DGM1:1186,DGM2:1186

  60. NGnasso says:

    Solved.
    My network administrator changed my data node subnet mask without inform me about.

    Sorry and thanks a lot.

  61. NGnasso says:

    Hi Andrew, my cluster works fine now, but i found a strange problem while installing processes as Windows services, particullary management process. The process starts then fails due it can’t find the config.ini file. I specified the path in the installation command as below

    C:mysql-clusterbinndb_mgmd.exe –install -f config.ini –configdir=C:mysql-clusterbin

    and also

    C:mysql-clusterbinndb_mgmd.exe –install -f config.ini –configdir=.

    In the Windows event log i found this message:
    Error opening ‘config.ini’, error 2, No such file or directory.
    Clearly is a path error, but i can’t understand where the error in my command line is.

    Thanks in advance

  62. NGnasso says:

    Found solution searching in mysql cluster official forum. I post the solution, someone could find same problem.

    The command line is

    “C:\mysql-cluster\bin\ndb_mgmd.exe” –install –config-file=”C:\mysql-cluster\bin\config.ini” –configdir=”C:\mysql-cluster\bin”

    Thanks

  63. Rafael Santiago says:

    buenas tardes Sr Andrew tengo 4 (A>B>C>D>A) tiendas con MySQL Server 5.6 con MySQL cluster 5.6 instalados(Los 4 servidores lo tengo Replicados(A>B>C>D>A)). tengo windows 7 instalado, mi idea es que las 4 tiendas esten sincronizados, si quiero actualizar el almacen de la tienda 1(A), el resto se refleje dicha actualizacion(B>C>D), Quiero saber si MySQL cluster me sirve, tambien necesito que si el Nodo C falla, en la replicacion se rompe, me gustaria saber si existe un metodo para que si C falla la replicacion seria ((A>B>D>A)), aqui encontre algo pero no se como aplicarlo http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html?page=5 o me conviene mas MySQL Cluster…Ayudame por favor Andrew…Gracias

  64. Rafael Santiago says:

    In https://translate.google.com.mx/ good afternoon Mr Andrew I have 4 (A> B> C> D> A) MySQL Server 5.6 stores the MySQL Cluster 5.6 installed (I have the 4 servers so Replicated (A > B> C> D> A)). I have windows 7 installed, my idea is that the 4 shops are synchronized, if I want to upgrade the stock the store 1 (A), the remainder reflects this update (B> C> D), I want to know if I use MySQL cluster, I also need it if Node C fails, the replication is broken, I wonder if there is a method so that if C fails serious replication ((A> B> D> A)), here found something but not how to apply http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html?page=5 or more MySQL Cluster for me … Help me please … Thanks Andrew

  65. Jessica Lane says:

    Wow, it took me forever to find a clear explanation of this. Thanks for the help.

  66. Gaurav says:

    Hi Andrew
    In “Target Deployment” picture it is mentioned API node(102) is communicating with API node(101), is it valid?

    In general I would like to ask a question that does a API node communicate with other API node in mysql cluster?

    Thanks
    Gaurav

    • andrew says:

      Hi Gaurav,

      not the best of diagrams – the line is actually from the management node to each of the MySQL Servers. The API nodes do not communicate with each other.

      Andrew.

      • Gaurav says:

        Thank you Andrew for clarification,

        I have other query that suppose I have 2 sql nodes are running in a cluster, a request went to first sql node and while performing the request ,the sql node went down then in this case will other sql node take responsibility to process the request to complete?

        Thanks
        Gaurav

  67. Abbas says:

    How do I deploy MySQL Cluster on Windows Azure? Is there a step by step article on this?

    I want to use a front end opencart eCommerce app with a high availability SQL Cluster. I know that Windows azure provides availability for load balancing but I am unable to figure out a way for mySQL?

  68. Anna says:

    Hi guys,
    I really need your help, I already tried all the possibilities! I reinstall the program thousand times! my problem as a lot of others is the sqld. when I try to launch the sqld in the nodes I received a message of error, and in my main node when I type ndb_mgm show its appear that the myswld API is not connected. I was thinking that maybe I shoul use the sql serve only in the main node and left the other ones with the data. So I am fscing this same problem for more the one week and I really need to solve this as soon as possible.

  69. rudolph says:

    good day i am having a problem with running multiple sql servers

    the error looks like this.

    [mysqld (API)] 2 node (s)
    id = 101 (not connected, accepting connect from 192.168.1.26)
    id = 102 (not connected, accepting connect from 192.168.1.26)

    can you please helm me with this????

    thanks

  70. ashok p says:

    My Mysql cluster has 4 nodes with following details

    1. Management node

    2. two data nodes

    3. 1 sql node->here is the problem for me.

    SQl/API node is not working connected to my cluster some times and i observed following error continuosly at sql node.
    2015-05-21 16:06:40 2068 [Warning] execute index stats listener failed: error 1427 line 2545
    2015-05-21 16:06:59 2068 [Warning] execute index stats listener failed: error 1427 line 2545
    2015-05-21 16:07:19 2068 [Warning] execute index stats listener failed: error 1427 line 2545
    2015-05-21 16:07:38 2068 [Warning] execute index stats listener failed: error 1427 line 2545
    2015-05-21 16:07:57 2068 [Warning] execute index stats listener failed: error 1427 line 2545
    Can you please help what was the problem?

    • andrew says:

      A couple of suggestions…

      1. Make sure that all of the nodes (processes) are using the same software version
      2. Try turning off any firewalls
      3. If you’ve made any changes to the config.ini file then stop and restart the management node with the --initial and then restart every data node and then every MySQL Server node

      Let us know how you get on.

      Andrew.

  71. Deep says:

    Hello,

    Thank you for an amazing tutorial.

    I created config.ini and my.101.cnf and my.102.cnf files on every node. i followed your instruction. when use ndb_mgmd –initial -f conf/config.ini –configdir=./conf ,it’s working perfectly on management node.

    after that i tried to start sql node using ndbd -c 192.168.0.19:1186 –initial and i am having “[ndbd] ERROR — couldnot connect to management server, error “””

    Please help me ASAP.

    Thank you.

Leave a Reply to Andrew Cancel reply