Running MySQL Cluster over multiple Windows serversPosted on January 20th, 2010 98 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.
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
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 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.
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:
[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
[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.
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:
- Category = MySQL Cluster
- OS = Microsoft Windows
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:
[ndbd (NDB)] 2 node (s)
id = firstname.lastname@example.org (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0,
id = email@example.com (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0)
[ndb_mgmd (MGM)] 1 node (s)
id = firstname.lastname@example.org (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?
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
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: 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 ?
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
this my config.ini
yes,and i’ve copied data from mysql\cluster\data\mysql into that folder too
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,
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: 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
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
120521 23:51:46 [Warning] create index stats listener failed: error 4716 line 24
120521 23:51:47 [Warning] create index stats listener failed: error 4716 line 24
120521 23:51:48 [Warning] create index stats listener failed: error 4716 line 24
You need to also copy the ndbinfo folder over to the mysqld_1 folder.
So you should have two folders in mysqld_1
same goes with mysqld_2
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!
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!
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.
I downloaded MySQL Workbench Tools which is really fantastic and helpful to perform most of the administrative task.
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 ?
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
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
Hello, have opened port 3306, so there should not be the problem
This is my config.ini 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
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
a very newbie question. In this specific configuration, are the data exactly the same on a node then other?
Thanks in advance
Hi, have turned off the firewall but it does not work, very strange.
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.
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, 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
mysqld config is:
The SQL Node on DGM1 (my management host 1) is up and can send queries to data nodes. The console shows me [Note] NDB: NodeID 5, all storage node connected.
About second SQL Node on management 2 (DGM2) the log is
[Note] NDB : NodeID 6, no storage nodes connected (time out).
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.
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.
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
I do not use my.cnf but run processes through options by command line
ndb_mgmd –initial –ndb-nodeid=49 –config-dir=c:\mysql-cluster\mycluster-data\49\data\ –config-file=c:\mysql-cluster\mycluster-data\49\data\config.ini
ndbd –ndb-nodeid=3 –ndb-connectstring=ART1:1186,ART2:1186,DGM1:1186,DGM2:1186
mysqld –no-defaults –datadir=C:\mysql-cluster\mycluster-data\61\data\ –tmpdir=C:\mysql-cluster\mycluster-data\61\data\tmp –basedir=C:/mysql-cluster/ –socket=C:\mysql-cluster\mycluster-data\61\sock\mysql.socket –port=3306 –ndbcluster –ndb-nodeid=61 –ndb-connectstring=ART1:1186,ART2:1186,DGM1:1186,DGM2:1186
My network administrator changed my data node subnet mask without inform me about.
Sorry and thanks a lot.
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-cluster\bin\ndb_mgmd.exe –install -f config.ini –configdir=C:\mysql-cluster\bin
C:\mysql-cluster\bin\ndb_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
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”
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
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
Leave a reply