The latest (GA) version of MySQL Cluster Manager is available through Oracle’s E-Delivery site. You can download the software and try it out for yourselves (just select “MySQL Database” as the product pack, select your platform, click “Go” and then scroll down to get the software).
So what’s new in this version
If you’ve looked at MCM in the past then the first thing that you’ll notice is that it’s now much simpler to get it up and running – in particular the configuration and running of the agent has now been reduced to just running a single executable (called "mcmd").
The second change is that you can now stop the MCM agents from within the MCM CLI – for example "stop agents mysite" will safely stop all of the agents running on the hosts defined by "mysite".
Those 2 changes make it much simpler for the novice user to get up and running quickly; for the more expert user, the most signifficant change is that MCM can now manage multiple clusters.
Obviously, there are a bunch of more minor changes as well as bug fixes.
Refresher – So What is MySQL Cluster Manager?
MySQL Cluster Manager provides the ability to control the entire cluster as a single entity, while also supporting very granular control down to individual processes within the cluster itself. Administrators are able to create and delete entire clusters, and to start, stop and restart the cluster with a single command. As a result, administrators no longer need to manually restart each data node in turn, in the correct sequence, or to create custom scripts to automate the process.
MySQL Cluster Manager automates on-line management operations, including the upgrade, downgrade and reconfiguration of running clusters as well as adding nodes on-line for dynamic, on-demand scalability, without interrupting applications or clients accessing the database. Administrators no longer need to manually edit configuration files and distribute them to other cluster nodes, or to determine if rolling restarts are required. MySQL Cluster Manager handles all of these tasks, thereby enforcing best practices and making on-line operations significantly simpler, faster and less error-prone.
MySQL Cluster Manager is able to monitor cluster health at both an Operating System and per-process level by automatically polling each node in the cluster. It can detect if a process or server host is alive, dead or has hung, allowing for faster problem detection, resolution and recovery.
To deliver 99.999% availability, MySQL Cluster has the capability to self-heal from failures by automatically restarting failed Data Nodes, without manual intervention. MySQL Cluster Manager extends this functionality by also monitoring and automatically recovering SQL and Management Nodes.
How is it Implemented?
MySQL Cluster Manager Architecture
MySQL Cluster Manager is implemented as a series of agent processes that co-operate with each other to manage the MySQL Cluster deployment; one agent running on each host machine that will be running a MySQL Cluster node (process). The administrator uses the regular mysql command to connect to any one of the agents using the port number of the agent (defaults to 1862 compared to the MySQL Server default of 3306).
How is it Used?
When using MySQL Cluster Manager to manage your MySQL Cluster deployment, the administrator no longer edits the configuration files (for example config.ini and my.cnf); instead, these files are created and maintained by the agents. In fact, if those files are manually edited, the changes will be overwritten by the configuration information which is held within the agents. Each agent stores all of the cluster configuration data, but it only creates the configuration files that are required for the nodes that are configured to run on that host.
Similarly when using MySQL Cluster Manager, management actions must not be performed by the administrator using the ndb_mgm command (which directly connects to the management node meaning that the agents themselves would not have visibility of any operations performed with it).
When using MySQL Cluster Manager, the ‘angel’ processes are no longer needed (or created) for the data nodes, as it becomes the responsibility of the agents to detect the failure of the data nodes and recreate them as required. Additionally, the agents extend this functionality to include the management nodes and MySQL Server nodes.
Installing, Configuring & Running MySQL Cluster Manager
On each host that will run Cluster nodes, install the MCM agent. To do this, just download the zip file from Oracle E-Delivery and then extract the contents into a convenient location:
$ unzip V27167-01.zip
$ tar xf mysql-cluster-manager-1.1.1-linux-rhel5-x86-32bit.tar.gz
$ mv mysql-cluster-manager-1.1.1-linux-rhel5-x86-32bit ~/mcm
Starting the agent is then trivial (remember to reapeat on each host though):
$ cd ~/mcm
$ bin/mcmd&
Next, some examples of how to use MCM.
Example 1: Create a Cluster from Scratch
The first step is to connect to one of the agents and then define the set of hosts that will be used for the Cluster:
$ mysql -h 192.168.0.10 -P 1862 -u admin -psuper --prompt='mcm> '
mcm> create site --hosts=192.168.0.10,192.168.0.11,192.168.0.12,192.168.0.13 mysite;
Next step is to tell the agents where they can find the Cluster binaries that are going to be used, define what the Cluster will look like (which nodes/processes will run on which hosts) and then start the Cluster:
mcm> add package --basedir=/usr/local/mysql_6_3_27a 6.3.27a;
mcm> create cluster --package=6.3.26 --processhosts=ndb_mgmd@192.168.0.10,ndb_mgmd@192.168.0.11,
ndbd@192.168.0.12,ndbd@192.168.0.13,ndbd@192.168.0.12, ndbd@192.168.0.13,mysqld@192.168.0.10,
mysqld@192.168.0.11 mycluster;
mcm> start cluster mycluster;
Example 2: On-Line upgrade of a Cluster
A great example of how MySQL Cluster Manager can simplify management operations is upgrading the Cluster software. If performing the upgrade by hand then there are dozens of steps to run through which is time consuming, tedious and subject to human error (for example, restarting nodes in the wrong order could result in an outage). With MySQL Cluster Manager, it is reduced to two commands – define where to find the new version of the software and then perform the rolling, in-service upgrade:
mcm> add package --basedir=/usr/local/mysql_7_1_8 7.1.8;
mcm> upgrade cluster --package=7.1.8 mycluster;
Behind the scenes, each node will be halted and then restarted with the new version – ensuring that there is no loss of service.
Example 3: Automated On-Line Add-Node
Automated On-Line Add-Node
Since MySQL Cluster 7.0 it has been possible to add new nodes to a Cluster while it is still in service; there are a number of steps involved and as with on-line upgrades if the administrator makes a mistake then it could lead to an outage.
We’ll now look at how this is automated when using MySQL Cluster Manager; the first step is to add any new hosts (servers) to the site and indicate where those hosts can find the Cluster software:
mcm> add hosts --hosts=192.168.0.14,192.168.0.15 mysite;
mcm> add package --basedir=/usr/local/mysql_7_1_8
--hosts=192.168.0.14,192.168.0.15 7_1_8;
The new nodes can then be added to the Cluster and then started up:
mcm> add process --processhosts=mysqld@192.168.0.10,mysqld@192.168.0.11,ndbd@192.168.0.14,
ndbd@192.168.0.15,ndbd@192.168.0.14,ndbd@192.168.0.15 mycluster;
mcm> start process --added mycluster;
The Cluster has now been extended but you need to perform a final step from any of the MySQL Servers to repartition the existing Cluster tables to use the new data nodes:
mysql> ALTER ONLINE TABLE <table-name> REORGANIZE PARTITION;
mysql> OPTIMIZE TABLE <table-name>;
Where can I found out more?
There is a lot of extra information to help you understand what can be achieved with MySQL Cluster Manager and how to use it:
Hello Andrew, in adding new data nodes:
mcm> add process –processhosts=mysqld@192.168.0.10,mysqld@192.168.0.11,ndbd@192.168.0.14,ndbd@192.168.0.15,ndbd@192.168.0.14,ndbd@192.168.0.15 mycluster;
I observed that you are also adding API (mysqld) nodes on the existing API servers. My question is since each API server instance has 2 mysqld running already, do we need to change the listening port of the second mysqld instance?
Also, am i correct to think that for each data node that will be added to the cluster, a corresponding API instance should be created.
Thanks. Great guide btw.
Hi John,
you’re correct that those mysqlds would need to have different port numbers to the mysqlds that were already on those hosts. I’d have to double check whether that is handled automatically – if not then when adding the new processes you could include the option…. –set=mysqld:port=3307
Thanks, Andrew.
Hello Andrew,
This is how I did it:
I stopped the cluster first, then did a
add process –processhosts=mysqld@site1,mysqld@site2,ndbd@site5,ndbd@site6,ndbd@site5,ndbd@site6 –set=port:mysqld=3307 mycluster;
That changed all ports of mysqld processess, I just moved existing mysqld to regular ports afterwards:
set port:mysqld:51=3306 mycluster;
set port:mysqld:52=3306 mycluster;
I don’t know how this can be done without stopping the cluster and without the long process above. (changing ports only on newly added mysqld’s)
Thanks
Hi John,
no need to stop the Cluster or modify the existing nodes; you can set the port numbers for the new mysqld processes as part of the “add process” command. Here’s an example…
How the Cluster looks initially:
mcm> show status -r mycluster;
+--------+----------+-------------------------------+---------+-----------+---------+
| NodeId | Process | Host | Status | Nodegroup | Package |
+--------+----------+-------------------------------+---------+-----------+---------+
| 1 | ndbd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 |
| 2 | ndbd | 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 | ndb_mgmd | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 |
| 51 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 |
| 52 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 |
| 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | |
| 54 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | |
| 55 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | |
| 56 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | |
+--------+----------+-------------------------------+---------+-----------+---------+
I then define the new processes:
mcm> add process -R mysqld@paas-23-56.osc.uk.oracle.com,mysqld@paas-23-57.osc.uk.oracle.com -s port:mysqld:57=3307,port:mysqld:58=3307 mycluster;
+----------------------------+
| Command result |
+----------------------------+
| Process added successfully |
+----------------------------+
1 row in set (2 min 10.79 sec)
Note that I was safely able to predict what the new node-ids would be and then override the default port assignment for each of them.
Next start the new mysqlds:
mcm> start process -added mycluster;
+------------------------------+
| Command result |
+------------------------------+
| Process started successfully |
+------------------------------+
1 row in set (5.32 sec)
mcm> show status -r mycluster;
+--------+----------+-------------------------------+---------+-----------+---------+
| NodeId | Process | Host | Status | Nodegroup | Package |
+--------+----------+-------------------------------+---------+-----------+---------+
| 1 | ndbd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 |
| 2 | ndbd | 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 | ndb_mgmd | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 |
| 51 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 |
| 52 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 |
| 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | |
| 54 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | |
| 55 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | |
| 56 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | |
| 57 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 |
| 58 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 |
+--------+----------+-------------------------------+---------+-----------+---------+
12 rows in set (0.02 sec)
As a final step, I can confirm the port assignments for all of the mysqld processes:
mcm> get -d port:mysqld mycluster;
+------+-------+----------+---------+----------+---------+---------+---------+
| Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment |
+------+-------+----------+---------+----------+---------+---------+---------+
| port | 3306 | mysqld | 51 | | | Default | |
| port | 3306 | mysqld | 52 | | | Default | |
| port | 3307 | mysqld | 57 | | | | |
| port | 3307 | mysqld | 58 | | | | |
+------+-------+----------+---------+----------+---------+---------+---------+
4 rows in set (0.06 sec)
Thanks a lot for the guide, that was very clear indeed. Predicting the node-id’s is a cool trick 🙂
I’m actually doing my simulation in HPCloud instances but I could not make the cluster consistently work. I create a working cluster, then delete it. Then create another, the second one wont come up. Sometimes I modify IndexMemory and DataMemory then suddenly, the cluster wont come up. Sorry for not providing you with more details.
Thanks Andrew,
Everything works now on my test environment:
mcm> show status -r mycluster;
+——–+———-+——-+———+———–+———+
| NodeId | Process | Host | Status | Nodegroup | Package |
+——–+———-+——-+———+———–+———+
| 49 | ndb_mgmd | site1 | running | | 7.2 |
| 50 | ndb_mgmd | site2 | running | | 7.2 |
| 1 | ndbd | site3 | running | 0 | 7.2 |
| 2 | ndbd | site4 | running | 0 | 7.2 |
| 3 | ndbd | site3 | running | 1 | 7.2 |
| 4 | ndbd | site4 | running | 1 | 7.2 |
| 51 | mysqld | site1 | running | | 7.2 |
| 52 | mysqld | site2 | running | | 7.2 |
| 53 | mysqld | site1 | running | | 7.2 |
| 54 | mysqld | site2 | running | | 7.2 |
| 5 | ndbd | site5 | running | 2 | 7.2 |
| 6 | ndbd | site6 | running | 2 | 7.2 |
| 7 | ndbd | site5 | running | 3 | 7.2 |
| 8 | ndbd | site6 | running | 3 | 7.2 |
+——–+———-+——-+———+———–+———+
14 rows in set (0.05 sec)
I still don’t understand though why new datanodes will need mysqld processes. Would it be possible for new datanodes by handled by existing mysqld processes?
Hi John,
you’re instincts are correct – there is no need to add new mysqlds just because you add more data nodes; only add them if you need the extra throughput or redundancy.
Regards, Andrew.
Thanks for confirming this. I appreciate it.
A very interesting topic for future guides is replacing failed nodes. Looking forward for it.
[…] http://www.clusterdb.com/mysql-cluster/mysql-cluster-manager-1-1-1-ga-available/ http://www.clusterdb.com/mysql-cluster/on-line-add-node-with-mcm-a-more-complex-example-2/?utm_source=rss&utm_medium=rss&utm_campaign=on-line-add-node-with-mcm-a-more-complex-example-2 […]
Andrew,
Thanks for giving a quick refresher on MCM. It helps people like me, who is new to MySQL world.
Is possible to run CM agents on different subnet than cluster processes? I’m in the process of setting up 8 nodes cluster on 7.2 CGE. Each hosts have 2 separate VLANs/subnets, app-db and mgmt respectively. We wanted use mgmt VLAN to manage the cluster including setting up SQL enterprise monitor agents to monitor cluster and send data to management repository running in a separate node in the management network. App-db VLAN will be primarily used for apps to talk to MySQLd nodes.
If this not supported, any input on how to workaround this setup?
Thanks,
Stalin
Hi Stalin,
In terms of communications:
– The MCM agents must all be able to talk to each other
– The MCM agents running on the same host as the ndb_mgmd nodes must be able communicate with their local ndb_mgmds over the management ‘wire protocol’
– The MCM agents on all hosts must be able to start and stop local processes
Regards, Andrew.
Thanks much for the response.
I shouldn’t see any issues in that case. I will know more this week when i do the install.
Thanks Again.
Stalin
Hi Andrew,
Nice blog, thanks for catrieng all Mysql cluster fundas on one site, I have one query, How to enable federated engine using MYSQL cluster manager, as we cannot modify config file manually. Please suggest.. Thanks in advance.
Don’t know a lot about the federated storage engine – what would you do to enable it if you weren’t using MCM?
Andrew.
Hi Andrew,
Nice Blog. It will be helpful for me if you can post steps of node deletion as I am new to MCM.
Regards,
Kevin
Hi Kevin,
at the moment there is no option in MCM to remove nodes from the Cluster. For Cluster itself, removing data nodes is a tricky process as it isn’t an on-line operation and involves performing a backup, shutdown, start-up and restore. We find that while “elastic scaling” is a popular idea, the reality is that Cluster users only ever seem to need to scale out and not back in again. Do you have a scale-in requirement/use-case?
Andrew.
Hi Andrew,
Actually , we are currently testing MCM and exploring the various administrative tasks.
As of now there is no requirement.
Can you also tell me how we can add two clusters in one site? As when I am trying to add hosts prompt is getting hung. When I am taking another mcm client session, its showing host as added, status available but version unknown.
Regards,
Kevin
Adding 2 hosts to the same site is straight-forward, you just repeat the
CREATE CLUSTERcommand and then make sure that you don’t have conflicting resources (for example, use the SET command to make sure thatportandportnumberare distinct for nodes on the same host. I just tried this and it works fine.Adding hosts to an existing site should also work – I’ve just tested it and it worked fine. Is
mcmdrunning on the new host (note that if you cloned the new host from one of the others then make sure you delete the mcm_data directory from it otherwise MCM will get confused and themcmdmay stop). I testedADD HOSTwithoutmcmdrunning on the new host and got exactly the behaviour you describe.