Archive for MySQL Cluster

MySQL Cluster 7.4.0 Labs Release

MySQL Cluster LogoThe first version of MySQL Cluster 7.4 has now been released on MySQL Labs. Note that labs loads are not suitable for production use (in fact they’re even less mature than Development Milestone Releases); their purpose is to give users a chance to see what’s in the works, try it for themselves and then provide feedback. Having read that, if you’d like to try it out then Download MySQL Cluster 7.4 from MySQL Labs.

The focus of this first Cluster 7.4 load is performance and data node restart times.

Performance

MySQL Cluster 7.4 Sysbench Read-OnlyMySQL Cluster was designed from the outset to be a distributed, in-memory database and has been deployed that way for many, many years (it’s interesting to see that the idea of in-memory databases has now really come into vogue with excitement around new arrivals on the scene such as Hekaton). Not surprisingly when people are considering MySQL Cluster, performance and scalability are key features (High Availability is another) and so performance improvements are always a key focus of every release and MySQL CLuster 7.4 is no exception.

MySQL Cluster 7.4 Sysbench Read/WriteThe graphs show what’s already been acheived with Read Only Sysbench showing a 47% increase in throughput and a 38% improvement for the Read/Write benchmark. Even better improvements are seen when configuring the data nodes to use even more threads. For those not familiar with Sysbench, you should realise that each of the transactions involves quite a lot of work: 10 Primary Key lookups, 5 different types of scans where we fetch 100 records (normal select through ordered index followed by oder by, group by and so forth).

Restart Times

While less glamorous than performance, the time taken for a data node to restart can make a huge difference to how easy it is to manage your cluster. As the size and activity of the database increases, the restart time for a single data node will go up, if you then multiply that time by the number of data nodes you have, maintenance activities can start to take longer than you’d like.

This first MySQL Cluster 7.4 labs makes some signifficant improvements to the restart times – mostly by allowing more of the work to be done in parallel.





MySQL cluster management – webinar replay available

Migrating from MySQL Cluster Auto-Installer to MCM
Thomas Nielsen and I recently presented a webinar explaining the latest developments in managing MySQL Cluster. In case you weren’t able to attend (or wanted to refresh your memory) then the webinar replay and charts are now available.

As a reminder, this webinar covered what’s new in MySQL Cluster Manager 1.3 which recently went GA.

By their very nature, clustered environments involve more efforts and resources to administer than standalone systems and this holds true for MySQL Cluster, the database designed for web-scale throughput with carrier-grade availability.

The MySQL Cluster Auto-Installer guides you through defining and running a well configured MySQL Cluster database – combining auto-discovery of platform resources with built-in best practices in an intuitive web-based GUI.

MySQL Cluster Manager (available as part of the commercial MySQL Cluster Carrier Grade Edition) simplifies the ongoing management of MySQL Cluster by automating common management tasks, delivering higher administration productivity and enhancing cluster agility. Tasks that used to take 46 commands can be reduced to just one! These tasks include configuration, starting & stopping the cluster, upgrades and backup/restore and new for MCM 1.3, import a running Cluster.

These webinars are always a good opportunity to get your questions answered; here’s a catch up of the Q&A from this session:

  • One of the biggest problems I am faced with is that many common applications heavily rely on the InnoDB or MyISAM – storage engines.So I am concerned about different behaviors of them compared to the NDB.This is something that we’ve been working to address – for example, JOINs in MySQL are now a lot faster and MySQL Cluster now supports Foreign Keys.
  • What about transaction – Handling, Locking mechanisms, or even changes in supported statements which are related to instance – managment rather then being part of DDL, DML, or DQL? Yes, MySQL Cluster (NDB) is slightly different in these respects, mostly due to its real-time heritage and distributed nature.
  • Is the MySQL – Cluster auto-installer restricted to specific operating system distributions or versions? No, the auto-installer works on all platforms supported by MySQL Cluster
  • I hope there will be a white paper about the differences as this is the major reason why I am not changing to NDB right now after I changed from many – databases-in-one-instance to a one-database-per-instance approeach. There are a couple of resources available today: Reference Manual comparing InnoDB and MySQL Cluster and the MySQL Cluster Evaluation Guide
  • In the creation of the site in MySQL Cluster Manager can hosts be specified using the IPv6 addresses or it’s IPv4-based only? At the moment, MCM only supports IPv4 addresses.
  • Are performance criteria for the restoring of nodes in a cluster available? I’m not aware of performance benchmarks for a database restore but they’re fairly fast as they happen at the data node level (and so for example there’s no need to go through SQL). You can also configure high degrees of parallelism for the restore so that many records are restored at once.




MySQL Cluster 7.3.4 Released

MySQL Cluster LogoThe binary and source versions of MySQL Cluster 7.3.4 have now been made available at http://www.mysql.com/downloads/cluster/.

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.3.4 (compared to 7.3.3) is available from the 7.3.4 Change log.





MySQL Cluster: The Latest Developments in Management, Free webinar

Migrating from MySQL Cluster Auto-Installer to MCMOn Thursday 23rd January, Thomas Nielsen and I will be hosting a webinar explaining the latest developments in managing MySQL Cluster. As always the webinar is free but please register here.

Note that we’ll be covering what’s new in MySQL Cluster Manager 1.3 which went GA this week.

By their very nature, clustered environments involve more efforts and resources to administer than standalone systems and this holds true for MySQL Cluster, the database designed for web-scale throughput with carrier-grade availability.

The MySQL Cluster Auto-Installer guides you through defining and running a well configured MySQL Cluster database – combining auto-discovery of platform resources with built-in best practices in an intuitive web-based GUI.

MySQL Cluster Manager (available as part of the commercial MySQL Cluster Carrier Grade Edition) simplifies the ongoing management of MySQL Cluster by automating common management tasks, delivering higher administration productivity and enhancing cluster agility. Tasks that used to take 46 commands can be reduced to just one! These tasks include configuration, starting & stopping the cluster, upgrades and backup/restore and new for MCM 1.3, import a running Cluster.

Join this webcast to get up to speed on the latest developments in these tools and learn how to exploit them to make management of MySQL Cluster simple, efficient and reliable.

Times:

  • Thu, Jan 23: 09:00 Pacific time (America)
  • Thu, Jan 23: 10:00 Mountain time (America)
  • Thu, Jan 23: 11:00 Central time (America)
  • Thu, Jan 23: 12:00 Eastern time (America)
  • Thu, Jan 23: 15:00 São Paulo time
  • Thu, Jan 23: 17:00 UTC
  • Thu, Jan 23: 17:00 Western European time
  • Thu, Jan 23: 18:00 Central European time
  • Thu, Jan 23: 19:00 Eastern European time
  • Thu, Jan 23: 22:30 India, Sri Lanka
  • Fri, Jan 24: 01:00 Singapore/Malaysia/Philippines time
  • Fri, Jan 24: 01:00 China time
  • Fri, Jan 24: 02:00 日本
  • Fri, Jan 24: 04:00 NSW, ACT, Victoria, Tasmania (Australia)

Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.





MCM 1.3 is GA – Importing a running Cluster into MySQL Cluster Manager

MySQL Cluster Manager logo
MySQL Cluster Manager 1.3.0 is now Generally Available and can be downloaded from the Oracle Software Delivery Cloud. The release contains a number of enhancements including performance improvements, handling larger clusters and of course bug fixes. The other big feature is that you can now import an existing, running MySQL Cluster instance into MCM without having to stop it first – this is the topic for this post.

In the past, we had a nice browser-based tool (the MySQL Clster Auto-Installer) to get a well configured cluster up and running (tuned to your environment) and we also had MySQL Cluster Manager to simplify the ongoing management of the cluster. Unfortunately, if you wanted to migrate the cluster you’d created with the auto-installer (or built by hand) then you first had to shut it down and then follow a manual procedure. MCM 1.3 introduces an import command that takes a running cluster and brings it under the control of MCM without having to stop the cluster (or suspend reads or writes). There are still some manual steps involved and the first half of this post will step through this process:

Once the import has been completed, the post will then step though a number of MCM tasks to test that everything has gone to plan and also to give a reminder of how simple operations such as upgrades, backup/restore and adding new nodes is once you’re using MCM.

 

Specify Cluster topology in MCM

For this example, a cluster is used that’s been created using the auto-installer that was a part of MySQL Cluster 7.3.2 (the version is signifficant as from MySQL Cluster 7.3.3, the auto-installer creates .conf) files for the mysqld processes rather than specifying everything on the command-line – that simplifies the import process).

Before going any further, some data is added to the database so that I can later check that it’s not been lost:

mysql@connect13a ~]$ mysql -h 127.0.0.1 -P3306 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.34-ndb-7.2.14-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE DATABASE clusterdb;USE clusterdb;
Query OK, 1 row affected (0.06 sec)

mysql> CREATE TABLE simples (id INT AUTO_INCREMENT PRIMARY KEY, time TIMESTAMP) ENGINE=ndb;
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO simples VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO simples VALUES ();
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO simples VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO simples VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM simples;
+----+---------------------+
| id | time                |
+----+---------------------+
|  3 | 2014-01-07 11:13:30 |
|  1 | 2014-01-07 11:13:28 |
|  2 | 2014-01-07 11:13:29 |
|  4 | 2014-01-07 11:13:30 |
+----+---------------------+
4 rows in set (0.00 sec)

The topology of the resulting cluster can be checked using the show command:

mysql@connect13b ~]$ cluster_7_2_14/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.103  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 0, Master)
id=2    @192.168.56.104  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=49   @192.168.56.101  (mysql-5.5.34 ndb-7.2.14)
id=52   @192.168.56.102  (mysql-5.5.34 ndb-7.2.14)

[mysqld(API)]   8 node(s)
id=50 (not connected, accepting connect from 192.168.56.101)
id=51 (not connected, accepting connect from 192.168.56.101)
id=53 (not connected, accepting connect from 192.168.56.102)
id=54 (not connected, accepting connect from 192.168.56.102)
id=55   @192.168.56.101  (mysql-5.5.34 ndb-7.2.14)
id=56   @192.168.56.101  (mysql-5.5.34 ndb-7.2.14)
id=57   @192.168.56.102  (mysql-5.5.34 ndb-7.2.14)
id=58   @192.168.56.102  (mysql-5.5.34 ndb-7.2.14)	

To define (but not create) the cluster in MCM, the following entities need to be defined:

  • site: the list of hosts that the cluster will run on
  • package: location of the cluster binaries on each of the hosts in the site
  • cluster: the cluster itself (the collection of nodes/processes that make up the cluster)

The MCM daemon mcmd must first be started on each of the target hosts and the mcm client run on any host before creating each of these entities:


[mysql@connect13a ~]$ ./mcm1.3/mcm1.3.0/bin/mcmd&
MySQL Cluster Manager 1.3.0 (64bit) started
Connect to MySQL Cluster Manager by running "/home/mysql/mcm1.3/mcm1.3.0/bin/mcm" -a connect13a.localdomain:1862

[mysql@connect13b ~]$ ./mcm1.3/mcm1.3.0/bin/mcmd&
[1] 3068

[mysql@connect13b ~]$ MySQL Cluster Manager 1.3.0 (64bit) started
Connect to MySQL Cluster Manager by running "/home/mysql/mcm1.3/mcm1.3.0/bin/mcm" -a connect13b.localdomain:1862

[mysql@connect13c ~]$  ./mcm1.3/mcm1.3.0/bin/mcmd&
[1] 1922
[mysql@connect13c ~]$ MySQL Cluster Manager 1.3.0 (64bit) started
Connect to MySQL Cluster Manager by running "/home/mysql/mcm1.3/mcm1.3.0/bin/mcm" -a connect13c.localdomain:1862

[mysql@connect13d ~]$  ./mcm1.3/mcm1.3.0/bin/mcmd&
[1] 1936
[mysql@connect13d ~]$ MySQL Cluster Manager 1.3.0 (64bit) started
Connect to MySQL Cluster Manager by running "/home/mysql/mcm1.3/mcm1.3.0/bin/mcm" -a connect13d.localdomain:1862

[mysql@connect13a ~]$ ./mcm1.3/mcm1.3.0/bin/mcm
MySQL Cluster Manager client started.
This wrapper will spawn the mysql client to connect to mcmd

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 1.3.0 MySQL Cluster Manager

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mcm> CREATE SITE --hosts=192.168.56.101,192.168.56.102,192.168.56.103,192.168.56.104 mysite;
+---------------------------+
| Command result            |
+---------------------------+
| Site created successfully |
+---------------------------+
1 row in set (1.34 sec)

mcm> add package --basedir=/home/mysql/cluster_7_2_14 7_2_14;
+----------------------------+
| Command result             |
+----------------------------+
| Package added successfully |
+----------------------------+
1 row in set (0.40 sec)

mcm> CREATE CLUSTER --import --package=7_2_14 --processhosts=ndb_mgmd:49@192.168.56.101,
ndb_mgmd:52@192.168.56.102,ndbmtd:1@192.168.56.103,ndbmtd:2@192.168.56.104,mysqld:55@192.168.56.101,
mysqld:56@192.168.56.101,mysqld:57@192.168.56.102,mysqld:58@192.168.56.102,ndbapi:50@192.168.56.101,
ndbapi:51@192.168.56.101,ndbapi:53@192.168.56.102,ndbapi:54@192.168.56.102 mycluster;
+------------------------------+
| Command result               |
+------------------------------+
| Cluster created successfully |
+------------------------------+
1 row in set (0.34 sec)

mcm> SHOW STATUS -r mycluster;
+--------+----------+-----------------+--------+-----------+---------+
| NodeId | Process  | Host            | Status | Nodegroup | Package |
+--------+----------+-----------------+--------+-----------+---------+
| 49     | ndb_mgmd | 192.168.56.101  | import |           | 7_2_14  |
| 52     | ndb_mgmd | 192.168.56.102  | import |           | 7_2_14  |
| 1      | ndbmtd   | 192.168.56.103  | import | n/a       | 7_2_14  |
| 2      | ndbmtd   | 192.168.56.104  | import | n/a       | 7_2_14  |
| 55     | mysqld   | 192.168.56.101  | import |           | 7_2_14  |
| 56     | mysqld   | 192.168.56.101  | import |           | 7_2_14  |
| 57     | mysqld   | 192.168.56.102  | import |           | 7_2_14  |
| 58     | mysqld   | 192.168.56.102  | import |           | 7_2_14  |
| 50     | ndbapi   | *192.168.56.101 | import |           |         |
| 51     | ndbapi   | *192.168.56.101 | import |           |         |
| 53     | ndbapi   | *192.168.56.102 | import |           |         |
| 54     | ndbapi   | *192.168.56.102 | import |           |         |
+--------+----------+-----------------+--------+-----------+---------+
12 rows in set (0.06 sec)

This post doesn’t attempt to go into details about all of the commands shown above but if you’re new to MCM then check out the MySQL Cluster documentation. The --import option sets the state of each of the nodes to import; they will stay in that state until the import command is run later.

When the auto-installer created the cluster, it changed a number of the configuration parameters from their defaults (either with command-line options or using configuration files). Using the LINUX ps -ef command you can identify all of these settings (either directly or by examining the configuration files that are referenced):

[mysql@connect13a ~]$ ps -ef | grep ndb_mgmd
mysql     2766     1  2 Jan07 ?        00:34:27 /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial 
--ndb-nodeid=49 --config-dir=/home/mysql/MySQL_Cluster/49/ 
--config-file=/home/mysql/MySQL_Cluster/49/config.ini

[mysql@connect13b ~]$ ps -ef | grep ndb_mgmd
mysql     2426     1  2 Jan07 ?        00:32:26 /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial 
--ndb-nodeid=52 --config-dir=/home/mysql/MySQL_Cluster/52/ 
--config-file=/home/mysql/MySQL_Cluster/52/config.ini

[mysql@connect13a ~]$ ps -ef | grep mysqld
mysql     3289     1  1 Jan07 ?        00:15:07 /home/mysql/cluster_7_2_14/bin/mysqld --no-defaults 
--datadir=/home/mysql/MySQL_Cluster/55/ --tmpdir=/home/mysql/MySQL_Cluster/55/tmp 
--basedir=/home/mysql/cluster_7_2_14/ --port=3306 --ndbcluster --ndb-nodeid=55 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186, 
--socket=/home/mysql/MySQL_Cluster/55/mysql.socket
mysql     3392     1  1 Jan07 ?        00:15:08 /home/mysql/cluster_7_2_14/bin/mysqld --no-defaults 
--datadir=/home/mysql/MySQL_Cluster/56/ --tmpdir=/home/mysql/MySQL_Cluster/56/tmp 
--basedir=/home/mysql/cluster_7_2_14/ --port=3307 --ndbcluster --ndb-nodeid=56 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186, 
--socket=/home/mysql/MySQL_Cluster/56/mysql.socket

[mysql@connect13b ~]$ ps -ef | grep mysqld
mysql     2884     1  1 Jan07 ?        00:14:41 /home/mysql/cluster_7_2_14/bin/mysqld --no-defaults 
--datadir=/home/mysql/MySQL_Cluster/57/ --tmpdir=/home/mysql/MySQL_Cluster/57/tmp 
--basedir=/home/mysql/cluster_7_2_14/ --port=3306 --ndbcluster --ndb-nodeid=57 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186, 
--socket=/home/mysql/MySQL_Cluster/57/mysql.socket
mysql     2981     1  1 Jan07 ?        00:14:42 /home/mysql/cluster_7_2_14/bin/mysqld --no-defaults 
--datadir=/home/mysql/MySQL_Cluster/58/ --tmpdir=/home/mysql/MySQL_Cluster/58/tmp 
--basedir=/home/mysql/cluster_7_2_14/ --port=3307 --ndbcluster --ndb-nodeid=58 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186, 
--socket=/home/mysql/MySQL_Cluster/58/mysql.socket

[mysql@connect13c ~]$ ps -ef | grep ndbmtd
mysql     1822     1 12 Jan07 ?        02:35:06 /home/mysql/cluster_7_2_14/bin/ndbmtd 
--ndb-nodeid=1 --ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,
mysql     1823  1822 12 Jan07 ?        02:35:06 /home/mysql/cluster_7_2_14/bin/ndbmtd 
--ndb-nodeid=1 --ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,

[mysql@connect13d ~]$ ps -ef | grep ndbmtd | grep nodeid=2
mysql     1835     1  0 Jan07 ?        00:00:52 /home/mysql/cluster_7_2_14/bin/ndbmtd 
--ndb-nodeid=2 --ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,
mysql     1836  1835 12 Jan07 ?        02:30:46 /home/mysql/cluster_7_2_14/bin/ndbmtd 
--ndb-nodeid=2 --ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,

[mysql@connect13a ~]$ cat /home/mysql/MySQL_Cluster/49/config.ini

# Configuration file for MyCluster
#

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=49
HostName=192.168.56.101
DataDir=/home/mysql/MySQL_Cluster/49/
Portnumber=1186

[NDB_MGMD]
NodeId=52
HostName=192.168.56.102
DataDir=/home/mysql/MySQL_Cluster/52/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M

[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=58M
IndexMemory=9M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
DiskPageBufferMemory=64M
SharedGlobalMemory=20M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=16384
BatchSizePerLocalScan=512
FragmentLogFileSize=64M
NoOfFragmentLogFiles=16
RedoBuffer=32M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

[NDBD]
NodeId=1
HostName=192.168.56.103
DataDir=/home/mysql/MySQL_Cluster/1/

[NDBD]
NodeId=2
HostName=192.168.56.104
DataDir=/home/mysql/MySQL_Cluster/2/

[MYSQLD DEFAULT]

[MYSQLD]
NodeId=55
HostName=192.168.56.101

[MYSQLD]
NodeId=56
HostName=192.168.56.101

[MYSQLD]
NodeId=57
HostName=192.168.56.102

[MYSQLD]
NodeId=58
HostName=192.168.56.102

[API]
NodeId=50
HostName=192.168.56.101

[API]
NodeId=51
HostName=192.168.56.101

[API]
NodeId=53
HostName=192.168.56.102

[API]
NodeId=54
HostName=192.168.56.102

Now that we have a view of all of the configuration parameters, a subset of them need to be applied to the definition of the cluster in MCM (or else MCM will override them). Note that not all of the definitions need porting to MCM – for example Portnumber=1186 as that is already the default value and configdir as MCM will use its own. So, based on the command-line options provided to the executables and the configuration files, the configuration parameters for the cluster defined in MCM are set:

mcm> SET SendBufferMemory:ndbmtd+ndbmtd=4M,ReceiveBufferMemory:ndbmtd+ndbmtd=4M,
SendBufferMemory:ndbmtd+mysqld=4M,ReceiveBufferMemory:ndbmtd+mysqld=4M,BackupLogBufferSize:ndbmtd=4M,
BackupMemory:ndbmtd=20M,BackupReportFrequency:ndbmtd=10,MemReportFrequency:ndbmtd=30,
LogLevelStartup:ndbmtd=15,LogLevelShutdown:ndbmtd=15,LogLevelCheckpoint:ndbmtd=8,
LogLevelNodeRestart:ndbmtd=15,DataMemory:ndbmtd=58M,IndexMemory:ndbmtd=9M,MaxNoOfTables:ndbmtd=4096,
MaxNoOfTriggers:ndbmtd=3500,SharedGlobalMemory:ndbmtd=20M,LongMessageBuffer:ndbmtd=32M,
MaxNoOfConcurrentTransactions:ndbmtd=16384,BatchSizePerLocalScan:ndbmtd=512,
FragmentLogFileSize:ndbmtd=64M,StopOnError:ndbmtd=true,LockPagesInMainMemory:ndbmtd=1,
TimeBetweenEpochsTimeout:ndbmtd=32000,TimeBetweenWatchdogCheckInitial:ndbmtd=60000,
TransactionInactiveTimeout:ndbmtd=60000,HeartbeatIntervalDbDb:ndbmtd=15000,
HeartbeatIntervalDbApi:ndbmtd=15000,DataDir:ndbmtd:1=/home/mysql/MySQL_Cluster/1/,
DataDir:ndbmtd:2=/home/mysql/MySQL_Cluster/2/,DataDir:mysqld:55=/home/mysql/MySQL_Cluster/55/,
DataDir:mysqld:56=/home/mysql/MySQL_Cluster/56/,DataDir:mysqld:57=/home/mysql/MySQL_Cluster/57/,
DataDir:mysqld:58=/home/mysql/MySQL_Cluster/58/,tmpdir:mysqld:55=/home/mysql/MySQL_Cluster/55/tmp,
tmpdir:mysqld:56=/home/mysql/MySQL_Cluster/56/tmp,tmpdir:mysqld:57=/home/mysql/MySQL_Cluster/57/tmp,
tmpdir:mysqld:58=/home/mysql/MySQL_Cluster/58/tmp,
socket:mysqld:55=/home/mysql/MySQL_Cluster/55/mysql.socket,
socket:mysqld:56=/home/mysql/MySQL_Cluster/56/mysql.socket,
socket:mysqld:57=/home/mysql/MySQL_Cluster/57/mysql.socket,
socket:mysqld:58=/home/mysql/MySQL_Cluster/58/mysql.socket,port:mysqld:56=3307,
port:mysqld:58=3307 mycluster;

+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (0.44 sec)

Note that as MCM is not yet managing the running cluster, you can break this up into multiple SET commands as it doesn’t need to restart any processes.
 

Prepare running cluster for import

As MCM takes over some functions from MySQL Cluster, we need to ensure that a couple of rules are imposed on the cluster so that there are no conflicts.

MCM is responsible for making sure that the management nodes are using the correct version of the configuration data and so we don’t want the management nodes holding onto older versions – this leads to the first rule, that the configuration cache should be disabled. This means restarting the management nodes with the config-cache parameter set to FALSE:

mysql@connect13a ~]$ ps -ef | grep ndb_mgmd
mysql     2766     1  2 Jan07 ?        00:34:27 /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial 
--ndb-nodeid=49 --config-dir=/home/mysql/MySQL_Cluster/49/ 
--config-file=/home/mysql/MySQL_Cluster/49/config.ini
[mysql@connect13a ~]$ kill -9 2766
[mysql@connect13a ~]$  /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial --ndb-nodeid=49 
--config-dir=/home/mysql/MySQL_Cluster/49/ --config-file=/home/mysql/MySQL_Cluster/49/config.ini 
--config-cache=FALSE

[mysql@connect13b ~]$ ps -ef | grep ndb_mgmd
mysql     2426     1  2 Jan07 ?        00:32:26 /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial 
--ndb-nodeid=52 --config-dir=/home/mysql/MySQL_Cluster/52/ 
--config-file=/home/mysql/MySQL_Cluster/52/config.ini
[mysql@connect13b ~]$ kill -9 2426
[mysql@connect13b ~]$  /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial --ndb-nodeid=52 
--config-dir=/home/mysql/MySQL_Cluster/52/ --config-file=/home/mysql/MySQL_Cluster/52/config.ini 
--config-cache=FALSE

MCM is responsible for making sure that a node is restarted in the event of the process stopping (only happens if StopOnError is set to FALSE) and so the data nodes no longer need their angel processes (the first of the 2 ndbmtd processes we see for each data node). This means the second rule is that all of these angel processes must be killed:

[mysql@connect13d ~]$ ps -ef | grep ndbmtd | grep nodeid=1
mysql     1822     1  0 Jan07 ?        00:00:52 /home/mysql/cluster_7_2_14/bin/ndbmtd --ndb-nodeid=1 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,
mysql     1823  1822 12 Jan07 ?        02:30:46 /home/mysql/cluster_7_2_14/bin/ndbmtd --ndb-nodeid=1 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,
[mysql@connect13d ~]$ kill -9 1822

[mysql@connect13d ~]$ ps -ef | grep ndbmtd | grep nodeid=2
mysql     1835     1  0 Jan07 ?        00:00:52 /home/mysql/cluster_7_2_14/bin/ndbmtd --ndb-nodeid=2 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,
mysql     1836  1835 12 Jan07 ?        02:30:46 /home/mysql/cluster_7_2_14/bin/ndbmtd --ndb-nodeid=2 
--ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,
[mysql@connect13d ~]$ kill -9 1835

In order to manager the cluster, MCM needs to be able to connect to each of the MySQL Servers and so the mcmd user must be created on each of the MySQL Servers (unless you’re exploiting the ability to share user credentials between multiple MySQL Servers in which case it only needs doing once):

[mysql@connect13a ~]$ mysql -h 127.0.0.1 -P3306 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.34-ndb-7.2.14-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademarkshow  of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER 'mcmd'@'%' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mcmd'@'127.0.0.1' IDENTIFIED BY 'super' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

[mysql@connect13a ~]$ mysql -h 127.0.0.1 -P3307 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.34-ndb-7.2.14-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademarkshow  of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER 'mcmd'@'%' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mcmd'@'127.0.0.1' IDENTIFIED BY 'super' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

[mysql@connect13b ~]$ mysql -h 127.0.0.1 -P3306 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.34-ndb-7.2.14-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademarkshow  of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER 'mcmd'@'%' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mcmd'@'127.0.0.1' IDENTIFIED BY 'super' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

[mysql@connect13b ~]$ mysql -h 127.0.0.1 -P3307 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.34-ndb-7.2.14-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademarkshow  of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE USER 'mcmd'@'%' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mcmd'@'127.0.0.1' IDENTIFIED BY 'super' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

As the running cluster was generated by a pre-MySQL Cluster 7.3.3 version of the auto-installer, all of the mysqld settings were specified as command-line options. The MCM import command is very restrictive about what command-line options are allowed and so most of the options need moving to .cnf files and the the mysqld processes need to be restarted to use those configuration files (note that the cluster is still available through this process but there is a rolling restart of the MySQL Servers and so applications may need to switch ones they’re connected to temporarily – if this isn’t already handled by load ballancing). These are the configuration files that were manually created and the commands to restart each MySQL Server:

[mysql@connect13a ~]$ cat  /home/mysql/MySQL_Cluster/55.cnf
[mysqld]
tmpdir=/home/mysql/MySQL_Cluster/55/tmp
datadir=/home/mysql/MySQL_Cluster/55/
basedir=/home/mysql/cluster_7_2_14/
socket=/home/mysql/MySQL_Cluster/55/mysql.socket
port=3306
ndbcluster
ndb-nodeid=55
ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186

[mysql@connect13a ~]$ mysqladmin -h 127.0.0.1 -P 3306 -u root shutdown
[mysql@connect13a ~]$ /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/55.cnf&

[mysql@connect13a ~]$ cat /home/mysql/MySQL_Cluster/56.cnf
[mysqld]
tmpdir=/home/mysql/MySQL_Cluster/56/tmp
datadir=/home/mysql/MySQL_Cluster/56/
basedir=/home/mysql/cluster_7_2_14/
socket=/home/mysql/MySQL_Cluster/56/mysql.socket
port=3307
ndbcluster
ndb-nodeid=56
ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186

[mysql@connect13a ~]$ mysqladmin -h 127.0.0.1 -P 3307 -u root shutdown
[mysql@connect13a ~]$ /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/56.cnf&

[mysql@connect13b ~]$ cat /home/mysql/MySQL_Cluster/57.cnf
[mysqld]
tmpdir=/home/mysql/MySQL_Cluster/57/tmp
datadir=/home/mysql/MySQL_Cluster/57/
basedir=/home/mysql/cluster_7_2_14/
socket=/home/mysql/MySQL_Cluster/57/mysql.socket
port=3306
ndbcluster
ndb-nodeid=57
ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186

[mysql@connect13b ~]$ mysqladmin -h 127.0.0.1 -P 3306 -u root shutdown
[mysql@connect13b ~]$ /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/57.cnf&

[mysql@connect13b ~]$ cat /home/mysql/MySQL_Cluster/58.cnf
[mysqld]
tmpdir=/home/mysql/MySQL_Cluster/58/tmp
datadir=/home/mysql/MySQL_Cluster/58/
basedir=/home/mysql/cluster_7_2_14/
socket=/home/mysql/MySQL_Cluster/58/mysql.socket
port=3307
ndbcluster
ndb-nodeid=58
ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186

[mysql@connect13b ~]$ mysqladmin -h 127.0.0.1 -P 3307 -u root shutdown
[mysql@connect13b ~]$ /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/58.cnf&

 

Ensure correct PID files in place

MCM tracks each of the processes in the cluster using the process IDs held in pid files. For the data nodes, MCM will automatically fetch the process IDs from the pid files from the running cluster and so we need to make sure that they’re accurate (that they’re in the right place and contain the correct IDs):

[mysql@connect13c ~]$ ps -ef | grep ndbmtd
mysql     1823     1 12 Jan07 ?        02:35:06 /home/mysql/cluster_7_2_14/bin/ndbmtd 
--ndb-nodeid=1 --ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,

[mysql@connect13c ~]$ echo 1823 > MySQL_Cluster/1/ndb_1.pid

[mysql@connect13d ~]$ ps -ef | grep ndbmtd
mysql     1836     1 12 Jan07 ?        02:34:03 /home/mysql/cluster_7_2_14/bin/ndbmtd 
--ndb-nodeid=2 --ndb-connectstring=192.168.56.101:1186,192.168.56.102:1186,

[mysql@connect13d ~]$ echo 1836 > MySQL_Cluster/2/ndb_2.pid

For MySQL Servers and management nodes, the pid files need be created within the MCM directory:

[mysql@connect13a ~]$ ps -ef | grep mysqld
mysql    11145  1226  1 09:49 pts/1    00:00:02 /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/55.cnf
mysql    11178  1226  1 09:50 pts/1    00:00:01 /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/56.cnf

[mysql@connect13a ~]$ echo 11145 > mcm1.3/mcm_data/clusters/mycluster/pid/pid.55
[mysql@connect13a ~]$ echo 11178 > mcm1.3/mcm_data/clusters/mycluster/pid/pid.56

mysql@connect13b ~]$ ps -ef | grep mysqld
mysql     11247  8477  1 09:55 pts/1    00:00:03 /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/57.cnf
mysql     11253  8477  0 09:57 pts/1    00:00:01 /home/mysql/cluster_7_2_14/bin/mysqld 
--defaults-file=/home/mysql/MySQL_Cluster/58.cnf

[mysql@connect13b ~]$ echo 11247 > mcm1.3/mcm_data/clusters/mycluster/pid/pid.57
[mysql@connect13b ~]$ echo 11253 > mcm1.3/mcm_data/clusters/mycluster/pid/pid.58

[mysql@connect13a pid]$ ps -ef | grep ndb_mgmd
mysql     8859     1  2 08:23 ?        00:00:23 /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial 
--ndb-nodeid=49 --config-dir=/home/mysql/MySQL_Cluster/49/ 
--config-file=/home/mysql/MySQL_Cluster/49/config.ini --config-cache=FALSE

[mysql@connect13b ~]$ echo 8859 > mcm1.3/mcm_data/clusters/mycluster/pid/pid.49

[mysql@connect13b pid]$ ps -ef | grep ndb_mgmd
mysql     7092     1  1 08:25 ?        00:00:17 /home/mysql/cluster_7_2_14/bin/ndb_mgmd --initial 
--ndb-nodeid=52 --config-dir=/home/mysql/MySQL_Cluster/52/ 
--config-file=/home/mysql/MySQL_Cluster/52/config.ini --config-cache=FALSE

[mysql@connect13b ~]$ echo 7092 > mcm1.3/mcm_data/clusters/mycluster/pid/pid.52

The good news is that if there are any mistakes in any of these files then the import cluster --dryrun command will fail with a useful error message and so you can come back to fix things up.
 

Import cluster into MCM

OK – you’ve now done the hard bits which is the prep work ahead of the actual importing of the cluster into MCM control, now it’s MCM’s turn to automate the actual install.

Before the real import, we use MCM to perform a dry-run to make sure that all of the prep work has been completed succesfully:

mcm> import cluster --dryrun mycluster;
+-------------------------------------------------+
| Command result                                  |
+-------------------------------------------------+
| Import checks passed. Cluster ready for import. |
+-------------------------------------------------+
1 row in set (0.80 sec)

All that’s left before running the final import is to take a backup (just in cas something should go very wrong):

mysql@connect13a ~]$ ndb_mgm
-- NDB Cluster -- Management Client --

ndb_mgm> start backup
Waiting for completed, this may take several minutes
Node 1: Backup 1 started from node 49
Node 1: Backup 1 started from node 49 completed
StartGCP: 35747 StopGCP: 35750
#Records: 2063 #LogRecords: 0
Data: 51472 bytes Log: 0 bytes

And now, finally the import itself can be run:

mcm> import cluster mycluster;
+-------------------------------+
| Command result                |
+-------------------------------+
| Cluster imported successfully |
+-------------------------------+
1 row in set (2.98 sec)

After all of the prep work, that seems a bit of an anticlimax! As a first check, make sure that all of the nodes (processes) have been imported correctly:

mcm> show status -r mycluster;
+--------+----------+-----------------+---------+-----------+---------+
| NodeId | Process  | Host            | Status  | Nodegroup | Package |
+--------+----------+-----------------+---------+-----------+---------+
| 49     | ndb_mgmd | 192.168.56.101  | running |           | 7_2_14  |
| 52     | ndb_mgmd | 192.168.56.102  | running |           | 7_2_14  |
| 1      | ndbmtd   | 192.168.56.103  | running | 0         | 7_2_14  |
| 2      | ndbmtd   | 192.168.56.104  | running | 0         | 7_2_14  |
| 55     | mysqld   | 192.168.56.101  | running |           | 7_2_14  |
| 56     | mysqld   | 192.168.56.101  | running |           | 7_2_14  |
| 57     | mysqld   | 192.168.56.102  | running |           | 7_2_14  |
| 58     | mysqld   | 192.168.56.102  | running |           | 7_2_14  |
| 50     | ndbapi   | *192.168.56.101 | added   |           |         |
| 51     | ndbapi   | *192.168.56.101 | added   |           |         |
| 53     | ndbapi   | *192.168.56.102 | added   |           |         |
| 54     | ndbapi   | *192.168.56.102 | added   |           |         |
+--------+----------+-----------------+---------+-----------+---------+
12 rows in set (0.10 sec)

As a second check, make sure that the data hasn’t been lost:

[mysql@connect13a ~]$ mysql -h 127.0.0.1 -P 3307 -u root -e 'SELECT * FROM clusterdb.simples'
+----+---------------------+
| id | time                |
+----+---------------------+
|  3 | 2014-01-07 12:08:40 |
|  5 | 2014-01-07 12:08:41 |
|  1 | 2014-01-07 12:08:38 |
|  2 | 2014-01-07 12:08:39 |
|  4 | 2014-01-07 12:08:41 |
+----+---------------------+

So that’s it, the cluster is now safely under the control of MCM. The next section performs some more tests of MCM and at the same time illustrates how much simpler some of these management steps are now we have MCM to help.
 

Try out MCM on the imported cluster

This final section shows how to exploit some of the MCM features on the cluster.

On-line backup and (especially) restore is very simple using MCM but before that can be done, we want to add extra ndbapi slots so that the restore command can be executed on the hosts running the data nodes – fortunately this is straight-forward to do:

mcm> add process -R ndbapi:59@192.168.56.103,ndbapi:60@192.168.56.104 mycluster;

+----------------------------+
| Command result             |
+----------------------------+
| Process added successfully |
+----------------------------+
1 row in set (2 min 32.91 sec)

Note that this process took more than 2 minutes – the reason for that is that behind the scenes it performed a rolling restart of all of the existing nodes (processes) to make them aware of the new nodes. We can now confirm that these nodes have been added:

mcm> show status -r mycluster;
+--------+----------+-----------------+---------+-----------+---------+
| NodeId | Process  | Host            | Status  | Nodegroup | Package |
+--------+----------+-----------------+---------+-----------+---------+
| 49     | ndb_mgmd | 192.168.56.101  | running |           | 7_2_14  |
| 52     | ndb_mgmd | 192.168.56.102  | running |           | 7_2_14  |
| 1      | ndbmtd   | 192.168.56.103  | running | 0         | 7_2_14  |
| 2      | ndbmtd   | 192.168.56.104  | running | 0         | 7_2_14  |
| 55     | mysqld   | 192.168.56.101  | running |           | 7_2_14  |
| 56     | mysqld   | 192.168.56.101  | running |           | 7_2_14  |
| 57     | mysqld   | 192.168.56.102  | running |           | 7_2_14  |
| 58     | mysqld   | 192.168.56.102  | running |           | 7_2_14  |
| 50     | ndbapi   | *192.168.56.101 | added   |           |         |
| 51     | ndbapi   | *192.168.56.101 | added   |           |         |
| 53     | ndbapi   | *192.168.56.102 | added   |           |         |
| 54     | ndbapi   | *192.168.56.102 | added   |           |         |
| 59     | ndbapi   | *192.168.56.103 | added   |           |         |
| 60     | ndbapi   | *192.168.56.104 | added   |           |         |
+--------+----------+-----------------+---------+-----------+---------+
14 rows in set (0.03 sec)

Taking a backup of the database is a single command:

mcm> backup cluster mycluster;
+-------------------------------+
| Command result                |
+-------------------------------+
| Backup completed successfully |
+-------------------------------+
1 row in set (5.76 sec)

Before performing the restore, the test data can be removed from the database:

[mysql@connect13a ~]$ mysql -h 127.0.0.1 -P 3307 -u root -e 'DELETE FROM clusterdb.simples'

To restore the database, check the available backups and then restore the most recent one (note as only the data has been deleted, the -M option is used to indicate that meta-data doesn’t need to be restored). The -I option is used to specify that the backup with Id of 2 should be used:

mcm> list backups mycluster;
+----------+--------+----------------+---------------------+---------+
| BackupId | NodeId | Host           | Timestamp           | Comment |
+----------+--------+----------------+---------------------+---------+
| 1        | 1      | 192.168.56.103 | 2014-01-08 08:31:35 |         |
| 1        | 2      | 192.168.56.104 | 2014-01-08 08:31:33 |         |
| 2        | 1      | 192.168.56.103 | 2014-01-09 05:03:13 |         |
| 2        | 2      | 192.168.56.104 | 2014-01-09 05:03:12 |         |
+----------+--------+----------------+---------------------+---------+
4 rows in set (0.19 sec)

mcm> restore cluster -I 2 -M mycluster;
+--------------------------------+
| Command result                 |
+--------------------------------+
| Restore completed successfully |
+--------------------------------+
1 row in set (9.40 sec)

To confirm that the restore has indeed been successful, check that the test data is back in the database:

[mysql@connect13a ~]$ mysql -h 127.0.0.1 -P 3307 -u root -e 'SELECT * FROM clusterdb.simples'
+----+---------------------+
| id | time                |
+----+---------------------+
|  3 | 2014-01-07 12:08:40 |
|  5 | 2014-01-07 12:08:41 |
|  1 | 2014-01-07 12:08:38 |
|  2 | 2014-01-07 12:08:39 |
|  4 | 2014-01-07 12:08:41 |
+----+---------------------+

As a final test, the cluster is upgraded to MySQL Cluster 7.3.3; all that’s needed is to define the package (telling MCM where to find the new binaries on the target hosts) and then perform the upgrade:

mcm> add package --basedir=/home/mysql/mcm1.3_cluster/cluster 7_3_3;
+----------------------------+
| Command result             |
+----------------------------+
| Package added successfully |
+----------------------------+
1 row in set (0.65 sec)

mcm> upgrade cluster --package=7_3_3 mycluster;
+-------------------------------+
| Command result                |
+-------------------------------+
| Cluster upgraded successfully |
+-------------------------------+
1 row in set (4 min 15.96 sec)

Conclusion

The most demanded feature for MySQL Cluster Manager was the ability to take an existing (running) cluster and bring it under the control of MCM (rather than having to create the cluster using MCM in the first place). MCM 1.3 delivers this. As you’ll have noticed, the migration process involves a non-trivial amount of prep work; the reason for this is that making configuration changes to cluster is fairly involved, with a lot of moving parts. Hopefully you’ll also have obderved that once the cluster is under the control of MCM, the management process becomes much simpler and less prone to user error.

It would be great to hear how people get on with MCM 1.3 in general and with the import in particular, please download the MySQL Cluster Manager software and try it out.





MySQL Cluster 7.3.3 Released

MySQL Cluster LogoThe binary and source versions of MySQL Cluster 7.3.3 have now been made available at http://www.mysql.com/downloads/cluster/.

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.3.3 (compared to 7.3.2) is available from the 7.3.3 Change log.





MySQL Cluster 7.2.14 Released

MySQL Cluster LogoThe binary version for MySQL Cluster 7.2.14 has now been made available at http://www.mysql.com/downloads/cluster/.

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.2.14 (compared to 7.2.13) is available from the 7.2.14 Change log.





MySQL Cluster 7.1.29 Released

MySQL Cluster LogoThe binary and source versions of MySQL Cluster 7.1.29 have now been made available at http://www.mysql.com/downloads/cluster/.

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.29 (compared to 7.1.28) is available from the 7.1.29 Change log.





Webinar Replay + Q&A – Developing JavaScript Applications for Node.js with MySQL and NoSQL

MySQL Cluster driver for JavaScript/Node.jsOn Thursday 12th September I co-presented a webinar on how MySQL Cluster delivers the key benefits of NoSQL Data Stores without having to give up the features that people rely on from relational databases (consistency, SQL etc.). There was a particular focus on how to use the new node.js JavaScript API which was recently released as part of MySQL Cluster 7.3. If you weren’t able to attend the live event then the webinar replay is available here. If you scroll down to the bottom of this post you’ll see a summary of the Questions and Answers from the session.

The new MySQL JavaScript Connector for Node.js is designed to deliver simplicity and high performance for JavaScript users.

Enables end-to-end JavaScript development, from the browser to the server and now to the world’s most popular open source database.

Provides native NoSQL access to the MySQL Cluster storage engine without first going through SQL transformations and parsing

This session gives you everything you need in order to get started on building highly scalable, event-driven Web, mobile, and social applications.

It discusses:

  •  Implementation of the connector
  • Database connection and metadata management
  • Creating and executing queries
  • Use with InnoDB (via an internal SQL conversion) and MySQL Cluster (direct)

WHO:

  • Andrew Morgan, Principal Product Manager
  • John Duncan, Senior Software Developer
  • Craig Russell, Principal Software Developer

Q & A

  • What is the benefit of using an asynchrous run-time model? The async everywhere idea in Node.JS means the one execution thread is always doing work for a user. Never waiting on I/O. It allows you to minimize the cost of waiting on the network for i/o, and that’s a big part of how it can handle so many simultaneous requests.
  • Can you please ellaborate more about multi-threading in node.js? Node.js has a just one thread that runs JavaScript. Then there is also a small thread pool that handles some background i/o tasks.
  • Why can’t you use a synchrous model? Would there be any drawbacks? The async programming style can be hard to get used to … but in JavaScript, async is really “how things are done”. Just like browser-side JavaScript responding to a mouse event, this is server-side JS responding to a database event or a network event.
  • Is there also a synchronous Session function? There is a synchronous version of connect(). There are some “immediate” calls on session (which don’t have to do any waiting), but there are no synchronous calls on session that wait for anything
  • Most applications run as responses to requests and so most of the logic needs to be executed sequentially. How are asynchronous calls handled? Is there any mechanism to wait and join multiple asynch call for a particular section of logic? If you have multiple database operations to execute, you can use the batch feature which will wait for all operations to complete and then call back. If you have other operations you need to have your own synchronization mechanism.
  • It was mentioned that you can use this APi to access the MySQL Cluster data nodes directly or MyISAM/InnoDB data via SQL and a MySQL server – how do I configure that? In the connection properties for your database connection, you use adapter=ndb for a native NDB connection, or adapter=mysql for a MySQL connection.
  • Are results mapped into objects? Yes. When the inner layer reads a row from the database, it creates a JavaScript object (according to the mapping) and it calls your Constructor with no arguments and with this set to the newly created object.
  • So there is seperate mapping for tables and for results? No, a single mapping.
  • Does the object mapping support the entity relationships like in JPA implementations? Can it be referenced with JSON ojects with one-to-one, one-to-many, etc relationships? The current release does not support relationships/JOINs.
  • JavaScript is weakly typed. How are different data types handled with the same reffrence? Dynamic typing means that values (objects, exspanssions) have types (i.e. a set of available operations and properties) but not variables, so a variable can, over time, refer to values of different types.
  • Are there sql like transaction through the Node.js NoSQL API? Yes, the API looks something like this: session.currentTransaction().begin() … define operations … session.currentTransaction.commit()
  • So, we can use session to track any transaction? Right, a session owns its single transaction. If you don’t call begin() on the transaction, then your operation will run “auto-commit”.
  • Does adapter=mysql option use https://github.com/felixge/node-mysql‎ adapter adapter? Yes.
  • Is this library similar to what mongoose is to mongodb? I’m not very familiar with mongoose — but it is broadly similar to many object-to-database mapping layers.
  • Is there a working “Hello World” example showing all of these technologies as a big happy family? You could start with this… Using JavaScript and Node.js with MySQL Cluster – First steps
  • So, just for clarification, the chief advantage of this is it’s moving the heavy lifting from the server side (like with PHP) to the client side? Not quite, node.js is server-side javascript. The main advantage is an asynchronous processing of requests that never block. In contrast, a large number of server-threads need to be provided with requests that can block.
  • Node.js runs on V8 which developed by google, its run fast any browser or only in Chrome? Node.js actually runs in the app server rather than in your browser and so it will play nicely when accessed via any browser (or other client)




MySQL Cluster Asynchronous Replication – conflict detection & resolution

Fig. 1 Multi-master replication leading to inconsistencies

I was rooting through past blog entries and I stumbled accross a draft post on setting up multi-master (update anywhere) asynchronous replication for MySQL Cluster. The post never quite got finished and published and while the material is now 4 years old it may still be helpfull to some and so I’m posting it now. Note that a lot has happened with MySQL Cluster in the last 4 years and in this area, the most notable change has been the Enhanced conflict resolution with MySQL Cluster active-active replication feature introduced in MySQL Cluster 7.2 and if you’re only dealing with a pair of Clusters, that’s your best option as it removed the need for you to maintain the timestamp columns and backs out entire transactions rather than just the conflicting rows. So when would you use this “legacy” method? The main use case is when you want conflict detection/resolution among a ring of more than 2 Clusters. Note also that MySQL 5.6 (and so MySQL Cluster 7.3) added microsecond precision to timestamps and so you may not need the custom plugin that this post referred to.

Anyway, here’s the original post…

————————————————————
MySQL Cluster asynchronous replication allows you to run in a multi-master mode with the application making changes to both sites (or more than 2 sites using a replication ring). As the replication is asynchronous, if the application(s) modified the same row on both sites at ‘about the same time’ then there is a potential for a collision. Left to their own devices, each site would store (and provide to the application) different data indefinitely. This article explains how to use MySQL Cluster collision detection and resolution to cope with this.

Fig. 1 Multi-master replication leading to inconsistencies

Fig. 1 Multi-master replication leading to inconsistencies

Fig. 1 shows the timeline that can result in a conflict. The same or two different applications make a change to the same row in the table but to the 2 different instances of MySQL Cluster. Each cluster synchronously replicates the data amongst its local data node in order to provide local High Availability (everything there is safe!). At some point later (normally a fraction of a second), the changes are replicated to the remote site asynchronously – this delay opens a window for a conflict where Cluster 2 is updated by the application just before it receives the earlier update from Cluster 1. Cluster 2 will overwrite it’s row with the value (5) it has received but only after its earlier change (directly from the application) is written to the binary log ready for replication to Cluster 1 which in turn will cause that value (15) to be stored by Cluster 1. Each Cluster instance replicates what it believes to be the correct data to the other site – overwriting what that site had previously stored. In our example, that leaves one database holding the value 15 for key ‘A’ while the other stores 5.

It’s often the case that the application will tend to go to the same site during a particular time when accessing the same data and so the chances of a conflict are reduced but the application may still want to guard against (even rare) race conditions. If replication slows down (for example due to a backlog of updates to be applied) or stops temporarily (for example due to network failure to the geographically remote site) then the chances of a collision greatly increase.

For information on setting up multi-master asynchronous replication with MySQL Cluster, please take a look at Setting up MySQL Asynchronous Replication for High Availability.

Conflict Detection & Resolution using MySQL Cluster

MySQL Cluster provides two different schemes to handle these collisions/conflicts. The first scheme (referred to as “greatest timestamp wins”) detects that a conflict occurs and automatically resolves it (the change most recently received from the application is stored on both Clusters). The second scheme (referred to as “same value wins”) detects that a conflict has occurred but does not fix it – instead the conflict is recorded in such a way that the application (or user) can figure out how best to resolve it based on a full understanding of the schema, what the data means and how it’s used. It is up to the developer which approach they use (if any) – it is selected on a per-table basis.

Common prerequisite steps

These steps should be followed regardless of whether you want to use conflict resolution or conflict detection (where the application decides how to resolve it).

  1. Set up multi-master replication as described in Setting up MySQL Asynchronous Replication for High Availability
  2. Create the function “inttime” for use in the stored procedures as described in Creating a MySQL plugin to produce an integer timestamp Note that you will need to install inttime.so on each host

Setting up Automatic Conflict Resolution (Greatest timestamp wins)

This is the simplest way to handle conflicts with MySQL Cluster when implementing multi-master asynchronous replication (actually, the simplest is to do nothing and accept that if your application(s) update the same row at about the same time at both Clusters then those Clusters may be left with different data until the application(s) next update that row).

Remember that this mechanism works by checking that the timestamp field of the update received by the slave is later than the one already stored. In the example that follows, the ‘ts’ column is used for the timestamp.

Create the database on either cluster (replication will make sure that it appears in both Clusters):

mysql> create database clusterdb;

Before creating the application tables, set  up the ndb_replication system table (again, in either Cluster):

mysql> CREATE TABLE mysql.ndb_replication ( db VARBINARY(63), table_name VARBINARY(63), server_id INT UNSIGNED, binlog_type INT UNSIGNED, conflict_fn VARBINARY(128), PRIMARY KEY USING HASH (db, table_name, server_id) ) ENGINE=NDB PARTITION BY KEY(db,table_name);

mysql> insert into mysql.ndb_replication values ('clusterdb', 'tab1', 7, NULL, 'NDB$MAX(ts)');

After that, you can create the application table:

cluster1 mysql> use clusterdb;

cluster1 mysql> create table tab1 (NAME varchar(30) not null primary key,VALUE int, ts BIGINT UNSIGNED default NULL) engine=ndb;

To test that the basic replication is working for this table, insert a row into cluster1, check it’s there in cluster2, add a second row to cluster2 and make sure it’s visible in cluster1:

cluster1 mysql> insert into tab1 values ('Frederick', 1, 0);

cluster2 mysql> use clusterdb;
cluster2 mysql> select * from tab1;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| Frederick |     1 |    0 |
+-----------+-------+------+
1 row in set (0.00 sec)
cluster2 mysql> insert into tab1 values ('William',20,0);

cluster1 mysql> select * from tab1;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| Frederick |     1 |    0 |
| William   |    20 |    0 |
+-----------+-------+------+
2 rows in set (0.00 sec)

For both rows, the timestamp was set to 0 to represent ‘the start of time’, from this point on, whenever making a change to those rows, the timestamp should be increased. Later on on in this article, I’ll show how to automate that process.

We’re now ready to test that the conflict resolution is working; to do so replication is stopped (in both directions) to increase the window for a conflict and the same tuple updated on each Cluster. Replication is then restarted and then I’ll confirm that the last update wins on both clusters:

cluster1 mysql> slave stop;

cluster2 mysql> slave stop;

cluster1 mysql> update tab1 set VALUE=10,ts=1 where NAME='Frederick';

cluster2 mysql> update tab1 set VALUE=11,ts=2 where NAME='Frederick';

cluster1 mysql> slave start;

cluster2 mysql> slave start;

cluster1 mysql> select * from tab1;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| William   |    20 |    0 |
| Frederick |    11 |    2 |
+-----------+-------+------+
2 rows in set (0.00 sec)

clusrer2 mysql> select * from tab3;
+-----------+-------+------+
| NAME      | VALUE | ts   |
+-----------+-------+------+
| William   |    20 |    0 |
| Frederick |    11 |    2 |
+-----------+-------+------+
2 rows in set (0.00 sec)

This confirms that the later update (timestamp of 2) is stored in both Clusters – conflict resolved!

Automating the timestamp column

Manually setting the timestamp value is convenient when testing that the mechanism is working as expected could be a nuisance in a production environment (for example, you would need to get the clocks of all application nodes exactly in sync wherever in the world they’re located). This section describes how that can be automated using stored procedures (note that stored procedures don’t work when using the NDB API to make changes but in that situation it should be straight-forward to provide wrapper methods that manage the timestamp field). Note that the timestamp must be an integer field (and needs a high level of precision) and so you can’t use the regular MySQL TIMESTAMP type.

This mechanism assumes that you’ve built “inttime.so” and deployed it to the hosts running the mysqld processes for each cluster (refer to the prerequisite section).

cluster1 mysql> create trigger tab1_insert before insert on tab3 for each row set NEW.ts=inttime;
cluster1 mysql> create trigger tab1_update before update on tab3 for each row set NEW.ts=inttime;
cluster1 mysql> insert into tab1 (NAME,VALUE) values ('James',10),('David',20);
cluster1 mysql> select * from tab1;
+-----------+-------+------------------+
| NAME      | VALUE | ts               |
+-----------+-------+------------------+
| William   |    20 |                0 |
| David     |    20 | 1250090500370307 |
| James     |    10 | 1250090500370024 |
| Frederick |    11 |                2 |
+-----------+-------+------------------+
4 rows in set (0.00 sec)

cluster2 mysql> update tab1 set VALUE=55 where NAME='William';
cluster2 mysql> select * from tab1;
+-----------+-------+------------------+
| NAME      | VALUE | ts               |
+-----------+-------+------------------+
| James     |    10 | 1250090500370024 |
| Frederick |    11 |                2 |
| William   |    55 | 1250090607251846 |
| David     |    20 | 1250090500370307 |
+-----------+-------+------------------+
4 rows in set (0.00 sec)

Setting up Conflict Detection (Same timestamp wins)

With this method, conflicts are detected and recorded but not automatically resolved. The intent is to allow the application to decide how to handle the conflict based on an understanding of what the data means.

Create the database on either cluster (replication will make sure that it appears in both Clusters):

mysql> create database clusterdb;

Before creating the application tables, set  up the ndb_replication system table (again, in either Cluster):

mysql> CREATE TABLE mysql.ndb_replication ( db VARBINARY(63), table_name VARBINARY(63), server_id INT UNSIGNED, binlog_type INT UNSIGNED, conflict_fn VARBINARY(128), PRIMARY KEY USING HASH (db, table_name, server_id) ) ENGINE=NDB PARTITION BY KEY(db,table_name);

mysql> insert into mysql.ndb_replication values ('clusterdb', 'SubStatus', 7, NULL, 'NDB$OLD(ts)');

After that, you can create the application table and its associated exception table:

cluster1 mysql> use clusterdb;
cluster1 mysql> create table SubStatus$EX (server_id INT UNSIGNED,master_server_id INT UNSIGNED,master_epoch BIGINT UNSIGNED,count INT UNSIGNED,sub_id int not null,notes VARCHAR(30) DEFAULT 'To be resolved', PRIMARY KEY (server_id, master_server_id, master_epoch, count)) engine=ndb;
cluster1 mysql> create table SubStatus (sub_id int not null primary key, ActivationStatus varchar(20), ts BIGINT default 0) engine=ndb;

To test that the exception table gets filled in, add some rows to the table and then cause an update conflict (in a similar way to the conflict resolution example but after setting up the timestamp automation):

cluster1 mysql> create trigger SubStatus_insert before insert on SubStatus for each row set NEW.ts=inttime();
cluster1 mysql> insert into SubStatus (sub_id, ActivationStatus) values (1,'Active'),(2,'Deactivated');
cluster1 mysql> select * from SubStatus;
+--------+------------------+------------------+
| sub_id | ActivationStatus | ts               |
+--------+------------------+------------------+
|      1 | Active           | 1250094170589948 |
|      2 | Deactivated      | 1250094170590250 |
+--------+------------------+------------------+
2 rows in set (0.00 sec)

cluster2 myql> use clusterdb;
cluster2 mysql> select * from SubStatus;
+--------+------------------+------------------+
| sub_id | ActivationStatus | ts               |
+--------+------------------+------------------+
|      1 | Active           | 1250094170589948 |
|      2 | Deactivated      | 1250094170590250 |
+--------+------------------+------------------+
2 rows in set (0.00 sec)

cluster1 mysql> slave stop;

cluster2 mysql> slave stop;

...

(at this point, just go on to test as with the conflict resoultion example but in this case expect to see that the confict is not resolved but an entry is added into the conflict table).

Of course, you can always add a trigger on the conflict table and use that to spur the application into initiating its own conflict resolution algorithm.