Running MySQL Cluster over multiple Windows servers @ Andrew Morgan’s MySQL Cluster Database Blog

Running MySQL Cluster over multiple Windows servers

Posted on January 20th, 2010 admin 22 comments

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.

Note that MySQL Cluster is still for development use only on Windows – use LINUX or Solaris for live deployments. For the latest status of which platforms are supported for development and deployment, refer to the Supported Platforms matrix.

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

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:\mysql\cluster”.

I prefer to keep the configuration and data files separate from the software and so I create a new folder “MySQL_Cluster\My_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:\mysql\cluster\data\mysql” 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:\mysql\cluster\bin” 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:\am233268\Documents\MySQL_Cluster\My_Cluster\data\

[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:\am233268\Documents\MySQL_Cluster\My_Cluster\data\mysqld_1
port=3306
ndb-connectstring=192.168.0.19:1186
```
my.102.cnf
```
[mysqld]
ndb-nodeid=102
ndbcluster
datadir=E:\am233268\Documents\MySQL_Cluster\My_Cluster\data\mysqld_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

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>\Documents\MySQL_Cluster\My_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=conf\my.101.cnf
192.168.0.19: mysqld --defaults-file=conf\my.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:
  - bugs.mysql.com
    - Category = MySQL Cluster
    - OS = Microsoft Windows
MySQL Cluster MySQL, MySQL Cluster, MySQL Cluster 7.0, Windows
22 responses to “Running MySQL Cluster over multiple Windows servers”
- Kriss Pakrer January 19th, 2010 at 16:51
  
  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
- admin January 19th, 2010 at 23:29
  
  Hi Kriss,
  
  I covered this in a recent webinar – you can view the replay at http://www.mysql.com/news-and-events/on-demand-webinars/display-od-445.html
  
  Personally, I prefer to extract the software from the zip file as it’s more transparent.
  
  Please let me know if you have any questions.
  
  Regards, Andrew.
- Kriss Pakrer January 25th, 2010 at 11:29
  
  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 January 25th, 2010 at 14:03
  
  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:\Windows\System32\drivers\etc\hosts
  
  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.
- Kriss Pakrer January 25th, 2010 at 15:40
  
  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!
- Kriss Pakrer January 26th, 2010 at 11:58
  
  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?
- admin January 26th, 2010 at 12:37
  
  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.
- Kriss Pakrer January 26th, 2010 at 14:34
  
  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
- admin January 26th, 2010 at 14:58
  
  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.
- Kriss Pakrer January 27th, 2010 at 11:46
  
  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
- admin January 27th, 2010 at 15:57
  
  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.
- den February 8th, 2010 at 16:50
  
  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
- admin February 8th, 2010 at 17:03
  
  Hi Den, if your management nodes are acting as arbitrators (which one of them is by default) then it’s important that they are running on an independent host from the data nodes. More details on http://www.clusterdb.com/mysql-cluster/mysql-cluster-fault-tolerance-impact-of-deployment-decisions/
  
  Regards, Andrew.
- den February 8th, 2010 at 17:27
  
  Hi Andrew,
  yes, the management node it is independent, but I try to figure out why mysqld(s) described in your article
  
  datadir=E:\am233268\Documents\MySQL_Cluster\My_Cluster\data\mysqld_1
  and
  datadir=E:\am233268\Documents\MySQL_Cluster\My_Cluster\data\mysqld_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
- Martyn Hughes April 19th, 2010 at 19:43
  
  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 April 29th, 2010 at 08:18
  
  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
- DY May 29th, 2010 at 07:04
  
  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:\Users\Administrator\Documents\MySQL_Cluster\My_Cluster>mysqld –defaults-file
  =conf\my.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:\mysql\cluster\data as the data directory, instead of following the instructions. But then it says no storage nodes connected:
  
  *********
  C:\Users\Administrator\Documents\MySQL_Cluster\My_Cluster>mysqld –defaults-file
  =conf\my.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
- DY June 2nd, 2010 at 07:13
  
  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
- admin June 7th, 2010 at 10:35
  
  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.
- Confused June 7th, 2010 at 15:04
  
  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 June 7th, 2010 at 15:14
  
  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.
- Carno July 17th, 2010 at 04:48
  
  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.
Leave a reply

Click here to cancel reply.

Name (required)

Mail (will not be published) (required)

Website