Running MySQL Cluster over multiple Windows servers

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 running on Windows – new Webinar

MySQL Cluster Running on Windows

Replay now available from http://www.mysql.com/news-and-events/on-demand-webinars/display-od-445.html

I’ll be presenting a (free) webinar on installing, configuring and running MySQL Cluster on Windows – starting at 10:00 PT on 7th January – that’s 18:00 UK time or 19:00 for most of Europe. To attend, just register ahead of time at mysql.com

In this presentation we will explore the benefits of leveraging MySQL Cluster on Windows. We will also cover step by step, how to get started with installing, configuring, and running MySQL Cluster on Windows. Finally, a review of MySQL Cluster’s architecture and future roadmap for Windows support will be covered. If you are interested in learning how to leverage MySQL Cluster on Windows, this webinar is for you.

Thursday, January 07, 2010: 10:00 Pacific time (America)
Thu, Jan 07: 08:00 Hawaii time
Thu, Jan 07: 11:00 Mountain time (America)
Thu, Jan 07: 12:00 Central time (America)
Thu, Jan 07: 13:00 Eastern time (America)
Thu, Jan 07: 18:00 UTC
Thu, Jan 07: 18:00 Western European time
Thu, Jan 07: 19:00 Central European time
Thu, Jan 07: 20:00 Eastern European time

My first Cluster running on Windows

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

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

Downloading and installing

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

MySQL Cluster Windows Installer

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

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

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

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

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

Configure and run the Cluster

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

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

config.ini

[ndbd default]
noofreplicas=2

[ndbd]
hostname=localhost
id=2

[ndbd]
hostname=localhost
id=3

[ndb_mgmd]
id = 1
hostname=localhost

[mysqld]
id=4
hostname=localhost

[mysqld]
id=5
hostname=localhost

[mysqld]
id=6
hostname=localhost

my.4.cnf

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

my.5.cnf

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

my.6.cnf

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

Those files configure the nodes that make up the Cluster.

From a command prompt window, launch the management node:

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

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

 C:\Users\Andrew>ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @localhost  (mysql-5.1.34 ndb-7.0.6)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[mysqld(API)]   3 node(s)
id=4    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=5    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=6    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)

Using the Cluster

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

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

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

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

C:\Users\Andrew>mysql -h localhost -P 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.34-ndb-7.0.6-cluster-gpl MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| assets         |
+----------------+
1 row in set (0.06 sec)

mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| car  |   950 |
+------+-------+
1 row in set (0.09 sec)

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

Your next steps

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

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