The binary version for MySQL Cluster 7.2.6 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version).
A description of all of the changes (fixes) that have gone into MySQL Cluster 7.2.6 (compared to 7.2.5) are available from the 7.2.6 Change log.
There are a couple of conferences coming up where you can expect to learn about the latest developments in MySQL Cluster and MySQL Replication (as well as what else is happening in MySQL 5.6).
The first is the Oracle MySQL Innovation Day which is being held in Oracle HQ at Redwood Shores. This is an all-day event on 5th June – unfortunately I won’t be able to attend this one but there will be lots of great Cluster and replication sessions. If you can’t make it out to California then there will be a live Webcast. You can register here to attend in person or join the webcast.
The second is MySQL Connect – this runs the weekend before Oracle OpenWorld in San Francisco; it’s not until 29th September but it’s worth registering now to get the early bird pricing and save $500 (end 13th July). There are lots of great sessions lined up both from the MySQL experts within Oracle and users and community members.
I’ve previously provided an example of using MySQL Cluster Manager to add nodes to a running MySQL Cluster deployment but I’ve since received a number of questions around how to do this in more complex circumstances (for example when ending up with more than 1 MySQL Server on a single host where each mysqld process should use a different port). The purpose of this post is to work through one of these more complex scenarios.
The starting point is an existing cluster made up of 3 hosts with the nodes (processes) as described in this MCM report:
mcm> SHOW STATUS -r mycluster; +--------+----------+-------------------------------+---------+-----------+---------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-------------------------------+---------+-----------+---------+ | 1 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 | | 2 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 0 | 7_2_5 | | 49 | ndb_mgmd | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 50 | mysqld | paas-23-54.osc.uk.oracle.com | running | | 7_2_5 | | 51 | mysqld | paas-23-55.osc.uk.oracle.com | running | | 7_2_5 | | 52 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | +--------+----------+-------------------------------+---------+-----------+---------+ 7 rows in set (0.01 sec)
This same configuration is shown graphically in this diagram:
Original MySQL Cluster deployment
Note that the ‘ndbapi’ node isn’t actually a process but is instead a ‘slot’ that can be used by any NDB API client to access the data in the data nodes directly – this could be any of:
This Cluster is now going to be extended by adding an extra host as well as extra nodes (both processes and ndbapi slots).
The following diagram illustrates what the final Cluster will look like:
MySQL Cluster after on-line scaling
The first step is to add the new host to the configuration and make it aware of the MySQL Cluster package being used (in this example, 7.2.5). Note that you should already have started the mcmd process on this new host (if not then do that now):
mcm> ADD HOSTS --hosts=paas-23-57.osc.uk.oracle.com mysite; +--------------------------+ | Command result | +--------------------------+ | Hosts added successfully | +--------------------------+ 1 row in set (8.04 sec) mcm> ADD PACKAGE -h paas-23-57.osc.uk.oracle.com --basedir=/home/oracle/cluster_7_2_5 7_2_5; +----------------------------+ | Command result | +----------------------------+ | Package added successfully | +----------------------------+ 1 row in set (0.68 sec)
At this point the MCM agent on the new host is connected with the existing 3 but it has not become part of the Cluster – this is done by declaring which nodes should be on that host; at the same time I add some extra nodes to the existing hosts. As there will be more than one MySQL server (mysqld) running on some of the hosts, I’ll explicitly tell MCM what port number to use for some of the mysqlds (rather than just using the default of 3306).
mcm> ADD PROCESS -R ndbmtd@paas-23-54.osc.uk.oracle.com, ndbmtd@paas-23-55.osc.uk.oracle.com,mysqld@paas-23-56.osc.uk.oracle.com, ndbapi@paas-23-56.osc.uk.oracle.com,mysqld@paas-23-57.osc.uk.oracle.com, mysqld@paas-23-57.osc.uk.oracle.com,ndbapi@paas-23-57.osc.uk.oracle.com -s port:mysqld:54=3307,port:mysqld:57=3307 mycluster;
+----------------------------+ | Command result | +----------------------------+ | Process added successfully | +----------------------------+ 1 row in set (2 min 34.22 sec)
In case you’re wondering how I was able to predict the node-ids that would be allocated to the new nodes, the scheme is very simple:
If you look carefully at the results you’ll notice that the ADD PROCESS command took a while to run (2.5 minutes) – the reason for this is that behind the scenes, MCM performed a rolling restart – ensuring that all of the existing nodes pick up the new configuration without losing database service. Before starting the new processes, it makes sense to double check that the correct ports are allocated to each of the mysqlds:
mcm> GET -d port:mysqld mycluster; +------+-------+----------+---------+----------+---------+---------+---------+ | Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment | +------+-------+----------+---------+----------+---------+---------+---------+ | port | 3306 | mysqld | 50 | | | Default | | | port | 3306 | mysqld | 51 | | | Default | | | port | 3306 | mysqld | 52 | | | Default | | | port | 3307 | mysqld | 54 | | | | | | port | 3306 | mysqld | 56 | | | Default | | | port | 3307 | mysqld | 57 | | | | | +------+-------+----------+---------+----------+---------+---------+---------+ 6 rows in set (0.07 sec)
At this point the new processes can be started and then the status of all of the processes confirmed:
mcm> START PROCESS --added mycluster; +------------------------------+ | Command result | +------------------------------+ | Process started successfully | +------------------------------+ 1 row in set (26.30 sec) mcm> SHOW STATUS -r mycluster; +--------+----------+-------------------------------+---------+-----------+---------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-------------------------------+---------+-----------+---------+ | 1 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 | | 2 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 0 | 7_2_5 | | 49 | ndb_mgmd | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 50 | mysqld | paas-23-54.osc.uk.oracle.com | running | | 7_2_5 | | 51 | mysqld | paas-23-55.osc.uk.oracle.com | running | | 7_2_5 | | 52 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | | 3 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 1 | 7_2_5 | | 4 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 1 | 7_2_5 | | 54 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 55 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | | 56 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 | | 57 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 | | 58 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | | +--------+----------+-------------------------------+---------+-----------+---------+ 14 rows in set (0.08 sec)
The enlarged Cluster is now up and running but any existing MySQL Cluster tables will only be stored across the original data nodes. To remedy that, each of those existing tables should be repartitioned:
mysql> ALTER ONLINE TABLE simples REORGANIZE PARTITION; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> OPTIMIZE TABLE simples; +-------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+----------+----------+----------+ | clusterdb.simples | optimize | status | OK | +-------------------+----------+----------+----------+ 1 row in set (0.00 sec)
You can safely perform the repartitioning while the Cluster is up and running (with your application sending in reads and writes) but there is a performance impact (has been measured at 50%) and so you probably want to do this at a reasonably quiet time of day.
As always, please post feedback and questions in the comments section of this post.
I’ve previously provided an example of using MySQL Cluster Manager to add nodes to a running MySQL Cluster deployment but I’ve since received a number of questions around how to do this in more complex circumstances (for example when ending up with more than 1 MySQL Server on a single host where each mysqld process should use a different port). The purpose of this post is to work through one of these more complex scenarios.
The starting point is an existing cluster made up of 3 hosts with the nodes (processes) as described in this MCM report:
mcm> SHOW STATUS -r mycluster; +--------+----------+-------------------------------+---------+-----------+---------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-------------------------------+---------+-----------+---------+ | 1 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 | | 2 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 0 | 7_2_5 | | 49 | ndb_mgmd | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 50 | mysqld | paas-23-54.osc.uk.oracle.com | running | | 7_2_5 | | 51 | mysqld | paas-23-55.osc.uk.oracle.com | running | | 7_2_5 | | 52 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | +--------+----------+-------------------------------+---------+-----------+---------+ 7 rows in set (0.01 sec)
This same configuration is shown graphically in this diagram:
Original MySQL Cluster deployment
Note that the ‘ndbapi’ node isn’t actually a process but is instead a ‘slot’ that can be used by any NDB API client to access the data in the data nodes directly – this could be any of:
This Cluster is now going to be extended by adding an extra host as well as extra nodes (both processes and ndbapi slots).
The following diagram illustrates what the final Cluster will look like:
MySQL Cluster after on-line scaling
The first step is to add the new host to the configuration and make it aware of the MySQL Cluster package being used (in this example, 7.2.5). Note that you should already have started the mcmd process on this new host (if not then do that now):
mcm> ADD HOSTS --hosts=paas-23-57.osc.uk.oracle.com mysite; +--------------------------+ | Command result | +--------------------------+ | Hosts added successfully | +--------------------------+ 1 row in set (8.04 sec) mcm> ADD PACKAGE -h paas-23-57.osc.uk.oracle.com --basedir=/home/oracle/cluster_7_2_5 7_2_5; +----------------------------+ | Command result | +----------------------------+ | Package added successfully | +----------------------------+ 1 row in set (0.68 sec)
At this point the MCM agent on the new host is connected with the existing 3 but it has not become part of the Cluster – this is done by declaring which nodes should be on that host; at the same time I add some extra nodes to the existing hosts. As there will be more than one MySQL server (mysqld) running on some of the hosts, I’ll explicitly tell MCM what port number to use for some of the mysqlds (rather than just using the default of 3306).
mcm> ADD PROCESS -R ndbmtd@paas-23-54.osc.uk.oracle.com, ndbmtd@paas-23-55.osc.uk.oracle.com,mysqld@paas-23-56.osc.uk.oracle.com, ndbapi@paas-23-56.osc.uk.oracle.com,mysqld@paas-23-57.osc.uk.oracle.com, mysqld@paas-23-57.osc.uk.oracle.com,ndbapi@paas-23-57.osc.uk.oracle.com -s port:mysqld:54=3307,port:mysqld:57=3307 mycluster;
+----------------------------+ | Command result | +----------------------------+ | Process added successfully | +----------------------------+ 1 row in set (2 min 34.22 sec)
In case you’re wondering how I was able to predict the node-ids that would be allocated to the new nodes, the scheme is very simple:
If you look carefully at the results you’ll notice that the ADD PROCESS command took a while to run (2.5 minutes) – the reason for this is that behind the scenes, MCM performed a rolling restart – ensuring that all of the existing nodes pick up the new configuration without losing database service. Before starting the new processes, it makes sense to double check that the correct ports are allocated to each of the mysqlds:
mcm> GET -d port:mysqld mycluster; +------+-------+----------+---------+----------+---------+---------+---------+ | Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment | +------+-------+----------+---------+----------+---------+---------+---------+ | port | 3306 | mysqld | 50 | | | Default | | | port | 3306 | mysqld | 51 | | | Default | | | port | 3306 | mysqld | 52 | | | Default | | | port | 3307 | mysqld | 54 | | | | | | port | 3306 | mysqld | 56 | | | Default | | | port | 3307 | mysqld | 57 | | | | | +------+-------+----------+---------+----------+---------+---------+---------+ 6 rows in set (0.07 sec)
At this point the new processes can be started and then the status of all of the processes confirmed:
mcm> START PROCESS --added mycluster; +------------------------------+ | Command result | +------------------------------+ | Process started successfully | +------------------------------+ 1 row in set (26.30 sec) mcm> SHOW STATUS -r mycluster; +--------+----------+-------------------------------+---------+-----------+---------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-------------------------------+---------+-----------+---------+ | 1 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 | | 2 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 0 | 7_2_5 | | 49 | ndb_mgmd | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 50 | mysqld | paas-23-54.osc.uk.oracle.com | running | | 7_2_5 | | 51 | mysqld | paas-23-55.osc.uk.oracle.com | running | | 7_2_5 | | 52 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | | 3 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 1 | 7_2_5 | | 4 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 1 | 7_2_5 | | 54 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 55 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | | 56 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 | | 57 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 | | 58 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | | +--------+----------+-------------------------------+---------+-----------+---------+ 14 rows in set (0.08 sec)
The enlarged Cluster is now up and running but any existing MySQL Cluster tables will only be stored across the original data nodes. To remedy that, each of those existing tables should be repartitioned:
mysql> ALTER ONLINE TABLE simples REORGANIZE PARTITION; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> OPTIMIZE TABLE simples; +-------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+----------+----------+----------+ | clusterdb.simples | optimize | status | OK | +-------------------+----------+----------+----------+ 1 row in set (0.00 sec)
You can safely perform the repartitioning while the Cluster is up and running (with your application sending in reads and writes) but there is a performance impact (has been measured at 50%) and so you probably want to do this at a reasonably quiet time of day.
As always, please post feedback and questions in the comments section of this post.
Schema-free NoSQL Data
Update – the webinar replay is now available from here.
On Thursday, I’ll be presenting a webinar on NoSQL (of course with a MySQL twist!) – as always it’s free to attend but you need to register here in advance. Even if you can’t attend, it’s worth registering as you’ll be sent a link to the replay and the charts. The session will introduce the concepts and motivations behind the NoSQL movement and then go on to explain how you can get most of the same benefits with MySQL (including MySQL Cluster) while still getting the RDBMS benefits such as ACID transactions.
The official description:
The ever increasing performance demands of web-based services has generated significant interest in providing NoSQL access methods to MySQL – enabling users to maintain all of the advantages of their existing relational database infrastructure, while providing blazing fast performance for simple queries, using an API to complement regular SQL access to their data. This session looks at the existing NoSQL access methods for MySQL as well as the latest developments for both the InnoDB and MySQL Cluster storage engines. See how you can get the best of both worlds – persistence, consistency, rich queries, high availability, scalability and simple, flexible APIs and schemas for agile development.
When:
Yesterday we ran a webinar discussing the
demands of next generation web services and how blending the best of relational
and NoSQL technologies enables developers and architects to deliver the
agility, performance and availability needed to be successful.
Attendees posted a number of great
questions to the MySQL developers, serving to provide additional insights into areas like auto-sharding and cross-shard JOINs,
replication, performance, client libraries, etc. So I thought it would be useful to post those
below, for the benefit of those unable to attend the webinar.
Before getting to the Q&A, there are a
couple of other resources that maybe useful to those looking at NoSQL
capabilities within MySQL:
- On-Demand webinar (coming
soon!)
- Slides used during the webinar
- Guide to MySQL and NoSQL
whitepaper
- MySQL Cluster demo, including
NoSQL interfaces, auto-sharing, high availability, etc.
So here is the Q&A from the event
Q.
Where does MySQL Cluster fit in to the CAP theorem?
A. MySQL Cluster is flexible. A single
Cluster will prefer consistency over availability in the presence of network
partitions. A pair of Clusters can be configured to prefer availability over
consistency. A full explanation can be found on the MySQL Cluster & CAP Theorem blog post.
Q.
Can you configure the number of replicas? (the slide used a replication factor
of 1)
Yes. A cluster is configured by an .ini file. The option NoOfReplicas sets the number of originals and replicas: 1 = no data redundancy, 2 = one copy etc. Usually there's no benefit in setting it >2.
Q. Interestingly most (if not all) of the NoSQL databases recommend having 3 copies of data (the replication factor).
Yes, with configurable quorum based Reads and writes. MySQL Cluster does not need a quorum of replicas online to provide service. Systems that require a quorum need > 2 replicas to be able to tolerate a single failure. Additionally, many NoSQL systems take liberal inspiration from the original GFS paper which described a 3 replica configuration. MySQL Cluster avoids the need for a quorum by using a lightweight arbitrator. You can configure more than 2 replicas, but this is a tradeoff between incrementally improved availability, and linearly increased cost.
Q.
Can you have cross node group JOINS? Wouldn't that run into the risk of
flooding the network?
MySQL Cluster 7.2 supports cross nodegroup
joins. A full cross-join can require a large amount of data transfer, which may
bottleneck on network bandwidth. However, for more selective joins, typically
seen with OLTP and light analytic applications, cross node-group joins give a
great performance boost and network bandwidth saving over having the MySQL
Server perform the join.
Q.
Are the details of the benchmark available anywhere? According to my
calculations it results in approx. 350k ops/sec per processor which is the
largest number I've seen lately
The details are linked from Mikael
Ronstrom's blog
The benchmark uses a benchmarking tool we
call flexAsynch which runs parallel asynchronous transactions. It involved 100
byte reads, of 25 columns each. Regarding the per-processor ops/s, MySQL
Cluster is particularly efficient in terms of throughput/node. It uses
lock-free minimal copy message passing internally, and maximizes ID cache
reuse. Note also that these are in-memory tables, there is no need to read
anything from disk.
Q. Is
access control (like table) planned to be supported for NoSQL access mode?
Currently we have not seen much need for full SQL-like access control (which has always been overkill for web apps and telco apps). So we have no plans, though especially with memcached it is certainly possible to turn-on connection-level access control. But specifically table level controls are not planned.
Q. How
is the performance of memcached APi with MySQL against memcached+MySQL or any
other Object Cache like Ecache with MySQL DB?
With the memcache API we generally see a
memcached response in less than 1 ms. and a small cluster with one memcached
server can handle tens of thousands of operations per second.
Q.
Can .NET can access MemcachedAPI?
Yes, just use a .Net memcache client such
as the enyim or BeIT memcache libraries.
Q. Is
the row level locking applicable when you update a column through memcached API?
An update that comes through memcached uses
a row lock and then releases it immediately. Memcached operations like
"INCREMENT" are actually pushed down to the data nodes. In most cases
the locks are not even held long enough for a network round trip.
Q. Has
anyone published an example using something like PHP? I am assuming that you just use the PHP
memcached extension to hook into the memcached API. Is that correct?
Not that I'm aware of but absolutely you
can use it with php or any of the other drivers
Q. For
beginner we need more examples.
Take a look here for a fully worked example
Q. Can I access MySQL using Cobol (Open Cobol) or C and if so where can I find the coding libraries etc?
A. There is a cobol implementation that works well with MySQL, but I do not think it is Open Cobol. Also there is a MySQL C client library that is a standard part of every mysql distribution
Q. Is
there a place to go to find help when testing and/implementing the NoSQL
access?
If using Cluster then you can use the
cluster@lists.mysql.com alias or post on the MySQL Cluster forum
Q. Are there any white papers on this?
Yes - there is more detail in the MySQL Guide to NoSQL whitepaper
If you have further questions, please don’t
hesitate to use the comments below!
The binary version for MySQL Cluster 7.2.5 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version).
A description of all of the changes (fixes) that have gone into MySQL Cluster 7.2.5 (compared to 7.2.4) will appear in the 7.2.5 Change log.
Check out this MySQL Cluster 7.2 Demo
From the Cluster 7.2 Demo
And upcoming MySQL Webcasts on Cluster, MySQL on Windows, MySQL Enterprise Edition, NoSQL access to MySQL, and Building High Performance and High Traffic PHP applications are available for registration!
Many Windows users first looking to experiment with MySQL Cluster may find the process of getting everything up and running daunting. Fortunately, there’s MySQL Cluster Manager (MCM) which can help jump-start that process – and that’s all many people will need.
In my work, I frequently have to install specific version of MySQL Server or Cluster to validate behavior of a handful of commands, and MCM is a bit overkill. I have a couple of tricks that I use to quickly set up MySQL Cluster on Windows environments:
The Cluster configuration file is very sparse:
[ndbd default] NoOfReplicas=2 DataDir=D:\cluster-data [NDB_MGMD] HostName=127.0.0.1 NodeId=1 DataDir= D:\cluster-data # Data Nodes # One entry for each node [NDBD] HostName=127.0.0.1 NodeId=2 [NDBD] HostName=127.0.0.1 NodeId=3 # SQL Nodes # One entry for each node [MYSQLD] HostName=127.0.0.1 [MYSQLD]
If you use this configuration file, make sure that you also create the DataDir you specify – the Cluster management node will not start if it does not exist.
The batch file I use is similarly simple, but it saves me plenty of time when I have to quickly deploy a specific version for a quick 5-minute test:
REM stop MySQL SQL nodes running on ports 3307 or 3308, if any exist REM (ignore errors if mysqld isn't available on the port) bin\mysqladmin -uroot -P3307 shutdown bin\mysqladmin -uroot -P3308 shutdown REM stop MySQL data and management nodes, if running (connection failure errors can be ignored) bin\ndb_mgm -e "shutdown" REM start the MySQL Cluster management node start bin\ndb_mgmd --configdir=%CD% -f config.ini REM start the MySQL Cluster data nodes, passing --initial if the batch file received that argument start bin\ndbd %1 start bin\ndbd %1 REM created a copy of the MySQL SQL node data directory, if it doesn't already exist IF NOT EXIST %CD%\data2 XCOPY %CD%\data %CD%\data2 /i /S REM start the MySQL SQL nodes on ports 3307 and 3308 start bin\mysqld --no-defaults --basedir=%CD% --datadir=%CD%\data --port=3307 --ndbcluster --ndb-connectstring=localhost:1186 --console start bin\mysqld --no-defaults --basedir=%CD% --datadir=%CD%\data2 --port=3308 --ndbcluster --ndb-connectstring=localhost:1186 --console
The script expects to be run from within the directory created when unpacking the .ZIP file. It leverages the default (blank) password for the root account in Windows .ZIP distributions to shutdown the SQL nodes, and uses port 3307 and 3308 to avoid causing problems with any existing MySQL server that may be running on port 3306 (which is my personal, persistent installation).
Hopefully this will help people looking to do a quick deployment of MySQL Cluster on Windows.
Many Windows users first looking to experiment with MySQL Cluster may find the process of getting everything up and running daunting. Fortunately, there’s MySQL Cluster Manager (MCM) which can help jump-start that process – and that’s all many people will need.
In my work, I frequently have to install specific version of MySQL Server or Cluster to validate behavior of a handful of commands, and MCM is a bit overkill. I have a couple of tricks that I use to quickly set up MySQL Cluster on Windows environments:
The Cluster configuration file is very sparse:
[ndbd default] NoOfReplicas=2 DataDir=D:\cluster-data [NDB_MGMD] HostName=127.0.0.1 NodeId=1 DataDir= D:\cluster-data # Data Nodes # One entry for each node [NDBD] HostName=127.0.0.1 NodeId=2 [NDBD] HostName=127.0.0.1 NodeId=3 # SQL Nodes # One entry for each node [MYSQLD] HostName=127.0.0.1 [MYSQLD]
If you use this configuration file, make sure that you also create the DataDir you specify – the Cluster management node will not start if it does not exist.
The batch file I use is similarly simple, but it saves me plenty of time when I have to quickly deploy a specific version for a quick 5-minute test:
REM stop MySQL SQL nodes running on ports 3307 or 3308, if any exist REM (ignore errors if mysqld isn't available on the port) bin\mysqladmin -uroot -P3307 shutdown bin\mysqladmin -uroot -P3308 shutdown REM stop MySQL data and management nodes, if running (connection failure errors can be ignored) bin\ndb_mgm -e "shutdown" REM start the MySQL Cluster management node start bin\ndb_mgmd --configdir=%CD% -f config.ini REM start the MySQL Cluster data nodes, passing --initial if the batch file received that argument start bin\ndbd %1 start bin\ndbd %1 REM created a copy of the MySQL SQL node data directory, if it doesn't already exist IF NOT EXIST %CD%\data2 XCOPY %CD%\data %CD%\data2 /i /S REM start the MySQL SQL nodes on ports 3307 and 3308 start bin\mysqld --no-defaults --basedir=%CD% --datadir=%CD%\data --port=3307 --ndbcluster --ndb-connectstring=localhost:1186 --console start bin\mysqld --no-defaults --basedir=%CD% --datadir=%CD%\data2 --port=3308 --ndbcluster --ndb-connectstring=localhost:1186 --console
The script expects to be run from within the directory created when unpacking the .ZIP file. It leverages the default (blank) password for the root account in Windows .ZIP distributions to shutdown the SQL nodes, and uses port 3307 and 3308 to avoid causing problems with any existing MySQL server that may be running on port 3306 (which is my personal, persistent installation).
Hopefully this will help people looking to do a quick deployment of MySQL Cluster on Windows.