Wednesday, August 4, 2010

MySQL Cluster on Windows - Not so NDB oriented: Set up a dev environment

Getting closer to some serious MySQL Cluster NDB API development on Windows, we still need a decent development environment, and as for myself, I like the ability to have a self-contained environment for this, at least contained within one computer, so that I can develop when on the road. So this means I have to use Windows as a Server anyway, right, if I am developing Windows applications? Well, not necessarily, there is always the option of using a VM, such as VirtualBox or something. But I really do prefer to use the native Windows environment for this, which has a few issues.

As I have said before, I do prefer Linux / Unix as a Server platform. But in this case, Windows will only be a development server, which is different. And if you want to use Windows as a production server, well as I have said before, that is fine too, although not my cup of tea,

But running MySQL Cluster on Windows as of version 7.1.4b of MySQL Cluster is less than exciting. There is no installer, just a ZIP file and the MySQL Cluster servers will not run as Windows Services (except the SQL node of course).

Well, this can be solved, so let me show you what my MySQL Cluster development setup looks like. I have downloaded MySQL Cluster 7.1.4b binary and I unpacked it into C:\MySQL714b. The install directory isn't important, except that of you choose something else, then of course you need to adjust the config files accordingly.

So, the my.ini file for the MySQL Server, which is placed in C:\MySQL714b, looks like this:

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=7104

#Path to installation directory.
basedir="C:/MySQL714b/"

#Path to the database root
datadir="C:/MySQL714b/Data/"
character-set-server=utf8

default-storage-engine=NDB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=700
table_cache=100
tmp_table_size=16M
thread_cache_size=64

# InnoDB settings
innodb_buffer_pool_size=32M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_log_file_size=10M
innodb_thread_concurrency=18

# NDB Options
ndb-connectstring=host=localhost:1186
ndbcluster

[ndb_mgmd]
config-file=C:\MySQL714b\mgm1\config.ini
config-dir=C:\MySQL714b\mgm1

Among the things to note is that you MUST set the ndbcluster variables, as well as the connect string. The MySQL Server that is part of the MySQL Cluster distribution does NOT have ndb enabled by default, don't ask me why, but this is the case (and this is true even for MySQL Cluster on Linux etc by the way).

Also, note that I have set up the config files and directories for the MySQL Cluster management daemon (the ndb_mgmd section), this is not necessary, but it makes using the MySQL Cluster batch-files that manages the Cluster, which I will show later, easier to use.

Now we need a NDB configuration file, a config.ini, but before this we need to create a few directories for the NDB management and data nodes. From a DOS window, run:

mkdir C:\MySQL714b\mgm1
mkdir C:\MySQL714b\node1
mkdir C:\MySQL714b\node1\data
mkdir C:\MySQL714b\node2
mkdir C:\MySQL714b\node2\data

Now we are ready to create a NDB configuration file, called config.ini and placed in C:\MySQL714b\mgm1. It looks like this in my case, and as you can see, this is a minimal configuration:

[ndbd default]
NoOfReplicas=2

[mysqld default]
[ndb_mgmd default]
[tcp default]

[ndb_mgmd]
PortNumber=1186
HostName=127.0.0.1

[ndbd]
HostName=127.0.0.1
DataDir=C:/MySQL714b/node1/data

[ndbd]
HostName=127.0.0.1
DataDir=C:/MySQL714b/node2/data

[mysqld]
[mysqld]
[mysqld]

I will not get into details on the individual configuration parameters here, but I think it is fairly obvious, and that this is just a starting point.

OK, now we have the configuration files we need, there is just one more thing we want to do before we start creating some good old DOS batch-file to start and stop MySQL Cluster, we need to install the MySQL daemon as a service, as it runs best that way. And this is pretty easy. Again, open a DOS window and execute:

C:\MySQL714b\bin\mysqld --install MySQL714b --defaults-file=C:\MySQL714b\my.ini

This will install a service called MySQL714b that will start a MySQL daemon using the config file C:\MySQL714b\my.ini. There is one issue with this service though, and that is that it will be started automatically when Windows starts, which we don't want, as the NDB daemons will not be running at that time. So in the same DOS Window, use the sc command to set the start type of a service. In this case, to change the service to manual, run:

sc config MySQL714b start= demand

Now we are ready to create a DOS batch-file that will start the Cluster. I will use the START DOS command, which starts a Windows process in a DOS Window. Usually, START will run the process in a separate DOS Windows, but there is a /B flag that will run it in a separate process in the same Window as it was started from, this is similar to use the & sign in *ix environment. This script will also start the MySQL Server:

REM Start MySQL Cluster processes
REM
SET MYSQL_HOME=C:\MySQL714b
SET MYSQL_SERVICE=MySQL714b

START /B %MYSQL_HOME%\bin\ndb_mgmd
START /B %MYSQL_HOME%\bin\ndbd --ndb-connectstring=nodeid=2;host=localhost:1186
START /B %MYSQL_HOME%\bin\ndbd --ndb-connectstring=nodeid=3;host=localhost:1186
NET START %MYSQL_SERVICE%

And then a script to stop it:

REM Stop MySQL Cluster processes
REM

SET MYSQL_HOME=C:\MySQL714b
SET MYSQL_SERVICE=MySQL714b

NET STOP %MYSQL_SERVICE%
%MYSQL_HOME%\bin\ndb_mgm --execute=shutdown

Not so bad after all, right? OK, we need a DOS Windows to start and stop the server, but only one. I think this setup makes working with MySQL Cluster on Windows A LOT easier. Once you have done this, just change to the C:\MySQL714b directory in a DOS Window and run ndbstart to start MySQL Cluster on your local machine, and run ndbstop to stop it.

In the next installment on this series on MySQL Cluster NDB Development on Windows, I'll get down to some more details. And I know that this part wasn't NDB API specific at all, but hey, it was necessary!

See you in a while...

/Karlsson

2 comments:

Magnus said...

Hi Anders,

my tests show that ndbcluster is ON by default on both Windows and Linux. Can you please check again?

~/mysql/7.1$> ./sql/Debug/mysqld.exe --verbose --help | grep ndbcluster
ndbcluster ON

Karlsson said...

On Windows, on 7.1.4b, without the parameter ndbcluster set, i.e. I changed to:
# ndbcluster
A show engines returns:
+------------+---------+-------------------------------------------------------
--------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+------------+---------+-------------------------------------------------------
--------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables
| NULL | NULL | NULL |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| ndbinfo | YES | MySQL Cluster system information storage engine
| NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
| InnoDB | YES | Supports transactions, row-level locking, and foreign
eys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it dis
ppears) | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tab
es | NO | NO | NO |
+------------+---------+-------------------------------------------------------
--------+--------------+------+------------+

So no, without NDBCLUSTER set in the my.ini, ndbcluster is not enabled.