Running MySQL Cluster over multiple Windows servers

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

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 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

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:

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

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:

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.

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.

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.

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.

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

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.

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.

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;”.

… 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.

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…

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?

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).

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

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

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?

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

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.

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

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.

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

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.

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 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.

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.

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 – 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.

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,….

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.

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.

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?

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.

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.

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.

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.

– 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.

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

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

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?

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.

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 “””