Raspberry Pi, MySQL Cluster ‘n’ Cream.

Ok, so I’ve been playing around with the idea of setting up MySQL cluster on a couple of Raspberry Pi’s and this is how it has been going.

References

First of all, for anyone else who’s reading this, it’s not a new thing, I know, and I highly recommend reading A.Morgans blog, http://www.clusterdb.com/mysql-cluster/mysql-cluster-running-on-raspberry-pi/ as well as someone else’s blog: http://markswarbrick.wordpress.com/, cheers Mark.

So, to make it all possible, here’s what I bought:

Product Model Quantity
Raspberry Pi – Model B (Made in UK, 512MB) RASPIB 2
16GB Samsung SD Card Pre-Loaded with NOOBS SAM-16 2
HDMI (Male) to DVI Converter (Female) HDM-DVF 1
New Link 4 Port USB Hub (USB 2.0 with Mains Adaptor) New Link USB 1
Noodle – USB to Micro USB Cable (2m Red) NOODLE-RED 2
RJ45 Cat5e Ethernet LAN Cable 2m (Blue) RJ45-BLUE 2
NETGEAR 8 Port Fast Ethernet Switch FS308-100UKS/M01 Netgear 1

as well as having other things lying around that came in useful.

So, when you’ve got it all set up, you’ll need a USB keyboard & mouse, the HDM-DVI cable and a monitor / tv too.

Setup

Then I needed to set it all up, using the following software programs.
- SDFormatter_4e
- win32diskimager-v0.8-binary.zip
Basically, you don’t really need to format the sd card or anything if you buy the NOOBS pre-loaded cards. But if you’re reusing an old one, then it’s handy to know. If, like me, you had to repartition and reformat an old card whilst testing, then make sure you’ve also bought a fair load of patience.

Using “2013-05-29-wheezy-armel.img” Raspbian soft-float image.
I tried Pidora but ‘cos of the soft-float issue, it wasn’t working for MySQL Cluster.

- source: mysql-cluster-gpl-7.3.0.tar.gz (get it here: http://dev.mysql.com -> downloads -> MySQL Cluster (GPL) -> top left, just under Dev Zone/Downloads/Documentation tab bar, you’ll see “Current” & “Archives“. In Archives, you’ll find the 7.3.0 source bundle.)
- MySQL patch for Bug 17637

Plugged in, turned on, engines running:

  login: pi
  password: raspberry (default password)

sudo raspi-config
- change hostname
- enlarge f/s
- set locale & TZ
- update
You might also want to change the ‘pi’ user password too.. for security.

Some additional o.s. config

Using USB as SWAP:

Originally done on a 4Gb sd card.. later I used a 16Gb one.. but hey.. it might come in handy..
:: as root
  free
  swapoff /swap01
  ls -lrt /dev/disk/by-uuid
Ok, it’s a 1Gb pen drive.. but better than nothing:
  dd if=/dev/zero of=/dev/sda1 bs=1024 count=1000000
  mkswap /dev/sda1
  swapon /dev/sda1
  swapon /swap01
  free

SWAP on the SD card

When you’ve got a 16Gb sd card, try using a larger swpafile than the original / default 100M:
:: as root
  free
  dphys-swapfile swapoff
  vi /etc/dphys-swapfile  =>>  CONF_SWAPSIZE=2048
  dphys-swapfile setup
  dphys-swapfile swapon
  free

apt

And for apt-get to work:
  vi /etc/apt/apt.conf.d/01proxy
This means a new file creation:
  Acquire::http::proxy “http://my.proxy.address.com:80/”;

NTP date & time

It’s nice to have all members of a cluster with the right time, and as the Raspberry Pi hasn’t got a built in clock (it’s a hard life isn’t it) let’s use the network:
  vi /etc/ntp.conf
Comment out all the “server 0.debian.pool.ntp.org iburst” entries.
+ add “server pool.ntp.org” in that section.
+ add “restrict 192.168.1.2 mask 255.255.255.0 nomodify notrap” (with your own IP address) in the “Clients from this subnet..” section too.

Restart the service, to get it all working:
  service ntp stop
  service ntp start

or if the proxy doesn’t let it sync properly, “date -s ” always helps. I did and up having to execute “apt-get install ntpdate –fix-missing” and then it started working properly.

MySQL Cluster compile time

Now that the environment is as we want it, it’s time to get MySQL working.
Remember, Oracle currently does include ARM as a supported platform for MySQL Cluster so we’ll have compile it ourselves. This means that there are some pre-req’s we’ll be needing.

Pre-requisites:

:: as root / using ‘sudo’ (‘cos this is MY Pi and MY MySQL, I consider that there’s no one more powerful than I’ here so I’ll be doing everything as root, that’s if you don’t mind. ;-) )

  apt-get install -y cmake
  apt-get install -y libncurses5-dev (it’s “yum install ncurses-devel” on Pidora, btw).
  apt-get install -y openjdk-7-jdk

Following a typical Mysql install:

  groupadd mysql
  useradd -r -g mysql mysql
  tar zxvf mysql-cluster-gpl-7.3.0.tar.gz
  cd mysql-cluster-gpl-7.3.0

Download & apply MySQL patch for Bug 17637

Download: http://bugs.mysql.com/file.php?id=17637
Apply:       cd mysql-cluster../sql_common
patch -l -f –verbose -i mysql-va-list.patch client_plugin.c

eg.
  root@mypi01:/home/mysql/mysql-cluster-gpl-7.3.0/sql-common# patch -l -f –verbose -i mysql-va-list.patch client_plugin.c
  Hmm…  Looks like a unified diff to me…
  The text leading up to this was:
  ————————–
  |diff -Naur mysql-5.5.16.orig/sql-common/client_plugin.c mysql-5.5.16/sql-common/client_plugin.c
  |— mysql-5.5.16.orig/sql-common/client_plugin.c       2011-09-09 11:56:39.000000000 -0400
  |+++ mysql-5.5.16/sql-common/client_plugin.c    2011-10-16 23:00:00.708799138 -0400
  ————————–
  Patching file client_plugin.c using Plan A…
  Hunk #1 succeeded at 228.
  Hunk #2 succeeded at 246.
  Hunk #3 succeeded at 290.
  Hunk #4 succeeded at 308.
  done

Compile time

As per the documentation, we need to execute 3 commands now. Please be warned, and review the time they took me with the Model B Raspberry Pi.

cmake .
# nohup cmake . > cmake_20130726.log &
- Started 19:12, Ended 19:21 = 9 minutes.
make
# nohup make > make_20130726.log &
- Started 19:39, Ended 00:39 = 5 hours.

make install
# nohup make install > make_install_20130726.log &
- Started 00:47, Ended 00:49 = 2 minutes.

Total time compiling:
    5 hrs 11 mins.

Finish install procedures

setup the my.cnf file.

cd /usr/local/mysql
chown -R mysql:mysql .
mkdir conf
cd conf
vi my.cnf

   —- my.cnf —-
[client]
socket                          =/tmp/mysql.sock

   [mysql]
prompt                          =\R:\m \d>\_

   [mysqld]
ndbcluster
# ndb-connectstring               =mypi01:1186,mypi02:1186
ndb-connectstring               =mypi01:1186
datadir                         =/opt/mysql/mysql/data
user                            =mysql
port                            =3306
socket                          =/tmp/mysql.sock
general-log                     =1
log-output                      =FILE
log-error                       =mypi02_cluster730.err
slow-query-log                  =1
max_connections                 =200
innodb_log_buffer_size          =4M
innodb_buffer_pool_size         =50M
innodb_log_file_size            =10M
innodb_flush_log_at_trx_commit  =2
innodb_file_per_table           =1
innodb_data_home_dir            =/opt/mysql/mysql/data
innodb_data_file_path           =ibdata1:10M;ibdata2:10M:autoextend

[mysql_cluster]
# ndb-connectstring               =mypi01:1186,mypi02:1186
ndb-connectstring               =mypi01:1186
—- EOF —-

  ln -s /usr/local/mysql/conf/my.cnf /etc/my.cnf
The sym linking is NOT obligatory by far, but I like making my housekeeping as easy as possible, and keep error exposure minimum, i.e. edit once. You’ll see why later (DemoKit).

Careful here, as I was travelling around to different networks, I ended up setting the hostnames in all config files as the hostname, and modified the /etc/hosts file and commented out / uncommented the appropriate entry. REMEMBER THIS, should you move around with the raspberry pi ‘n’ cluster.

  scripts/mysql_install_db –user=mysql –datadir=/opt/mysql/mysql/data
  chown -R root .
  cd /opt/mysql/mysql/data
  chown -R mysql:mysql .

One last thing before we start it all up:

Setup the config.ini: 
  cd /usr/local/mysql/conf
  vi config.ini
   —- config.ini —-
   [ndb_mgmd default]
   PortNumber                      =1186
   DataDir                         =/opt/mysql/mysql/mgmd_data

   [ndb_mgmd]
   NodeId                          =1
   HostName                        =mypi01

   #[ndb_mgmd]
   #NodeId                          =2
   #HostName                        =mypi02

   [ndbd default]
   noofreplicas                    =2
   DataDir                         =/opt/mysql/mysql/ndbd_data
   DataMemory                      =2M
   IndexMemory                     =1M
   DiskPageBufferMemory            =4M
   StringMemory                    =5
   MaxNoOfConcurrentOperations     =1K
   MaxNoOfConcurrentTransactions   =500
   SharedGlobalMemory              =500K
   LongMessageBuffer               =512K
   MaxParallelScansPerFragment     =16
   MaxNoOfAttributes               =100
   MaxNoOfTables                   =20
   MaxNoOfOrderedIndexes           =20

   [ndbd]
   NodeId                          =3
   HostName                        =mypi01
   DataDir                         =/opt/mysql/mysql/ndbd_data
   [ndbd]
   NodeId                          =4
   HostName                        =mypi02
   DataDir                         =/opt/mysql/mysql/ndbd_data

   [mysqld]
   NodeId                          =10
   HostName                        =mypi01
   [mysqld]
   NodeId                          =11
   HostName                        =mypi02

   [API]
   NodeId                          =12
   [API]
   NodeId                          =13
   —- EOF —-

  export PATH=$PATH:/usr/local/mysql/bin

MySQL Cluster Startup

Management Node First (INITIAL) Startup

REMEMBER: if you get any errors here, double check your IP’s and /etc/hosts.

I played around a little with this, and configured 2 management nodes, one on each Raspberry Pi. Although it is not going to give anyone any more HA, in fact, by pulling the plug on one of them, ndb_mgm operations can’t be done, but I thought I’d leave it all in, just in case someone is interested and looking to use it later in an architecture that will provide HA (i.e. buys another 2 Raspberry Pi’s for the sqlnode & management node).
So, that being said, on both the (temporarily ‘both’) ndb_mgm nodes (INITIAL only used on very first startup):
1st:
    ndb_mgmd –initial –config-dir=/usr/local/mysql/conf -f /usr/local/mysql/conf/config.ini
2nd:
    ndb_mgmd –config-dir=/usr/local/mysql/conf -f /usr/local/mysql/conf/config.ini
Once you’ve done all this and revisit / restart, INITIAL isn’t needed.

Also, make sure you use the –ndb-nodeid= option for each management node, so no entries appears with 127.0.0.1 IP’s.
eg.
  ndb_mgmd -f /usr/local/mysql/conf/config.ini –config-dir=/usr/local/mysql/conf –ndb-nodeid=1

NOTE:
Now, I’m going to make all contact via the management node on mypi01, i.e. now the commented out my.cnf:ndb-connectstring and the 2nd entry for config.ini:ndb_mgmd makes more sense. Apologies if it generates any complications.

Lets check it’s working:
  ndb_mgm -c mypi01:1186 -e show
  ndb_mgm -e show
If we had both management servers up n running, we could do the following too:
  ndb_mgm -c mypi02:1186 -e show

Datanode startup (FIRST / 1st startup):
On mypi01:
  ndbd –initial
On mypi02:
  ndbd –initial

Let’s check from mypi02:
  ndb_mgm -e show

On mypi01:
  mysqld –defaults-file=/usr/local/mysql/conf/my.cnf –user=mysql &
On mypi02:
  mysqld –defaults-file=/usr/local/mysql/conf/my.cnf –user=mysql &

Remember, security, on both sqlnodes: 
  mysqladmin -uroot password ‘pass’
  mysql -uroot -ppass

So what do we get? This is what we’d see if we had both mypi01 & mypi02 as management nodes:
  ndb_mgm -e show

Connected to Management Server at: mypi01:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3    @192.168.1.17  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4    @192.168.1.16  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.1.17  (mysql-5.5.25 ndb-7.3.0)
id=2    @192.168.1.16  (mysql-5.5.25 ndb-7.3.0)

[mysqld(API)]   4 node(s)
id=10   @192.168.1.17  (mysql-5.5.25 ndb-7.3.0)
id=11   @192.168.1.16  (mysql-5.5.25 ndb-7.3.0)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

As our situation will be that we only have 1 managment node configured, this is what we’ll really see:
  ndb_mgm -e show

Connected to Management Server at: mypi01:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4    @10.0.0.7  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)

[mysqld(API)]   4 node(s)
id=10   @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)
id=11   @10.0.0.7  (mysql-5.5.25 ndb-7.3.0)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

If you want to see what happens when I test the HA of Cluster by pulling the plug on half of the architecture, including the 1 of the 2 management nodes, have a look at the other post from-2-management-nodes-down-to-1.

For Demo Purposes: Getting the Java app “DemoKit” running

For demo’s sake, I’ve used a home-made java app, “DemoKit” authored by Mario Beck
For it to work, an exported display is needed, for X windows, run from the sqlnode / datanode / management node.

From Windows, I use Cygwin.
1. Install Cygwin (Release 6.8.2-4)
2. execute “StartXwin” from Start – Cygwin menu. This allows remote access with an X windows to the MSWin desktop.
3. then execute “Cygwin Bash shell”. This allows us to communicate with the proper environment variables and execute X commands.
4. set the display “export DISPLAY=:0.0
5. ssh -Y root@mypi01
Once connected to mypi01, make sure DISPLAY is set: env | grep DISPLAY. Should say DISPLAY=localhost:10.0 or similar.
execute x-terminal-emulator and an xterm style window opens.
Test the X capability and run xpdf.
Once in, if you do a “su -” to another user, X doesn’t work, as it’s the user you did the “ssh” with who’s allowed to connect. Try reconnecting as that user.

Copy (WinSCP) the DemoKit.zip file to one of the pi’s.
Left it in the ~root directory on mypi01.
unzipped, it leaves the DemoKit dir.

The java app “DemoKit”:

Time to revamp demokit.cfg config, layout, etc.  :: Adapt for a nice layout, no overlaps, etc.
Getting load balance – Readonly error.  :: So update Connector J driver, just in case:
    /home/mysql/DemoKit/lib
Copy mysql-connector-java-5.1.26-bin.jar into this dir.
    /home/mysql/DemoKit/nbproject
Update the lines in “project.properties” using the old connector.

Sort out why the cluster part of the architecture wasn’t working:
:: After installing NetBeans and opening the project, and going into the code details, it was far simpler.
Seeing that it does a ndb_mgm mypi01:1186 -t 1 -e “3 status” and get a 2 line result of a that particular nodeid. All without me telling it any particular PATH or anything…
:: DemoKit.zip comes with a “ndb_mgm” “Mach-O executable i386” executable.
Looks like this might come from an Apple / MacOS X platform, or similar.
So, seeing as none of this was being detected, I renamed it to ndb_mgm.orig, and made a symbolic link to the platform specific binary, in this case, at ln -s /usr/local/mysql/bin/ndb_mgm /home/mysql/DemoKit/ndb_mgm:

root@mypi01:/home/mysql/DemoKit# ls -lrt
total 3012
drwxr-xr-x 2 mysql mysql    4096 Jun 22  2010 test
-rw-r–r– 1 mysql mysql      82 Jun 22  2010 manifest.mf
-rwxr-xr-x 1 mysql mysql 3032612 Aug 31  2010 ndb_mgm.orig
-rw-r–r– 1 mysql mysql    1621 Sep  3  2010 demokit-cluster4node.cfg
-rw-r–r– 1 mysql mysql    3642 Aug  6 22:53 build.xml
drwxr-xr-x 3 mysql mysql    4096 Aug  8 06:52 src
drwxr-xr-x 2 mysql mysql    4096 Aug  8 07:56 ressources
-rw-r–r– 1 mysql mysql    2177 Aug  8 08:18 demokit-original.cfg
drwxr-xr-x 3 mysql mysql    4096 Aug  8 08:22 dist
drwxr-xr-x 4 mysql mysql    4096 Aug  8 08:22 build
drwxr-xr-x 2 mysql mysql    4096 Sep  6 12:34 lib
drwxr-xr-x 3 mysql mysql    4096 Sep  6 12:37 nbproject
-rw-r–r– 1 mysql mysql    2758 Sep  6 13:48 demokit.cfg
lrwxrwxrwx 1 root  root       28 Sep  6 13:52 ndb_mgm -> /usr/local/mysql/bin/ndb_mgm

Run it:
:: as root:
cd /root/DemoKit
java -jar dist/DemoKit.jar

So, now that it’s showing me all 5 components of Cluster, time to look at the High Availability scenarios.

DemoKit java app, deployed from mypi01, showing all MySQL Cluster components status.

Cluster robustness: Do we have a Cluster?

Now, let’s test the HA properly. Considering nodeid=1 is the sole management_node (even though it’s on the same server as ndbd nodeid=3 and mysqld nodeid=10.) we can kill any ndbd process as if it were a power failure:
Show them the java app DemoKit and that all 5 components are working fine.

Killing id=4 on mypi02:
  ps -ef | grep ndbd
root      2300     1  0 15:58 ?        00:00:00 ndbd -c mypi01
root      2301  2300  4 15:58 ?        00:00:16 ndbd -c mypi01

kill -9 2301
ps -ef | grep ndbd
root      2368  2126  0 16:05 pts/0    00:00:00 grep ndbd
  
  ndb_mgm -e show
Connected to Management Server at: mypi01:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4 (not connected, accepting connect from mypi02)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)

[mysqld(API)]   4 node(s)
id=10   @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)
id=11   @10.0.0.7  (mysql-5.5.25 ndb-7.3.0)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

Check NDBD log, on mypi02:
  vi + /opt/mysql/mysql/ndbd_data/ndb_4_out.log:
2013-09-04 16:05:36 [ndbd] ALERT    — Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.

Check MYSQLD error log, on mypi02:
  vi + /opt/mysql/mysql/data/mypi02_cluster730.err:
130904 16:05:37 [Note] NDB Binlog: Node: 4, down, Subscriber bitmask 00

Check NDB_MGMT log, on mypi01:
2013-09-04 16:05:36 [MgmtSrvr] ALERT    — Node 3: Node 4 Disconnected
2013-09-04 16:05:36 [MgmtSrvr] INFO     — Node 3: Communication to Node 4 closed
2013-09-04 16:05:36 [MgmtSrvr] ALERT    — Node 3: Network partitioning – arbitration required
2013-09-04 16:05:36 [MgmtSrvr] INFO     — Node 3: President restarts arbitration thread [state=7]
2013-09-04 16:05:36 [MgmtSrvr] ALERT    — Node 1: Node 4 Disconnected
2013-09-04 16:05:36 [MgmtSrvr] ALERT    — Node 3: Arbitration won – positive reply from node 1
2013-09-04 16:05:36 [MgmtSrvr] ALERT    — Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.
2013-09-04 16:05:36 [MgmtSrvr] INFO     — Node 3: Started arbitrator node 1 [ticket=0aef0002012295fe]
2013-09-04 16:05:40 [MgmtSrvr] INFO     — Node 3: Communication to Node 4 opened

Imagine we’ve recovered the NDBD nodeid=4, and now restart:
On mypi02:
  ndbd -c mypi01
2013-09-04 16:14:40 [ndbd] INFO     — Angel connected to ‘mypi01:1186′
2013-09-04 16:14:40 [ndbd] INFO     — Angel allocated nodeid: 4

Status check:
  ndb_mgm -e show
Connected to Management Server at: mypi01:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4    @10.0.0.7  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)

[mysqld(API)]   4 node(s)
id=10   @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)
id=11   @10.0.0.7  (mysql-5.5.25 ndb-7.3.0)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

Cluster didn’t go down when we lost half of our Node Group 0, i.e. 1 of our 2 datanodes.
Look at DemoKit monitor application:

Cluster didn’t go down when we lost half of our Node Group 0, i.e. 1 of our 2 datanodes.

Taking a look at DemoKit monitor application:

Datanode 2 down
Datanode 2 starting up
All back to normal.

We’ve lost half our Data Centre: datanode & sqlnode

This time, unplugging mypi02.

So, someone has pulled half of the data centre (the datanode & sqlnode):

  ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4 (not connected, accepting connect from mypi02)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)

[mysqld(API)]   4 node(s)
id=10   @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)
id=11 (not connected, accepting connect from mypi02)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

And let’s confirm from within the cluster:
On mypi01:
  mysql -uroot -p
  mysql> status;  <– shows uptime is good, i.e. longer than since we lost mypi02.
mysql> show global status like ‘ndb_number_%’;
+——————————–+——-+
| Variable_name                  | Value |
+——————————–+——-+
| Ndb_number_of_data_nodes       | 2     |
| Ndb_number_of_ready_data_nodes | 1     |
+——————————–+——-+

Now for a logical data test

With only 1 datanode active:
  mysql> create database clustertest;
  Query OK, 1 row affected (0.05 sec)

  mysql> use clustertest;
  Database changed
  mysql> create table ndbtest (i int) engine=NDBCLUSTER;
  Query OK, 0 rows affected (0.27 sec)

  mysql> insert into ndbtest () values (1), (2), (3), (4);
  Query OK, 4 rows affected (0.01 sec)
  Records: 4  Duplicates: 0  Warnings: 0

  mysql> select i from ndbtest;
  +——+
  | i    |
  +——+
  |    1 |
  |    4 |
  |    3 |
  |    2 |
  +——+
  4 rows in set (0.01 sec)

Remember, datanode nodeid=4 is down. Let’s start it up.
  ndbd -c mypi01
  2013-09-04 16:34:06 [ndbd] INFO     — Angel connected to ‘mypi01:1186′
2013-09-04 16:34:06 [ndbd] INFO     — Angel allocated nodeid: 4

mysqld –defaults-file=/usr/local/mysql/conf/my.cnf –user=mysql &

Let’s use sqlnode nodeid=11 on mypi02:
  mysql -uroot -p
mysql> status;

uptime shows the number of seconds it’s been running.

Now double check from within cluster:
  mysql> show databases;
we can see the newly created database “clustertest”.

  mysql> use clustertest; select * from ndbtest;
Database changed
+——+
| i    |
+——+
|    1 |
|    4 |
|    3 |
|    2 |
+——+
4 rows in set (0.00 sec)

Let’s complicate things:

Now to kill data node nodeid=3, without exiting the mysql session on the other sqlnode, with the freshly started datanode 4.
  ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3 (not connected, accepting connect from mypi01)
id=4    @10.0.0.7  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)

..
Just in case the data is cached, exit and login again:
  mysql -uroot -p clustertest -e ‘ use clustertest; select * from ndbtest;’
+——+
| i    |
+——+
|    3 |
|    2 |
|    1 |
|    4 |
+——+

Notice the rows have swapped around, i.e. the first half of the original data (numbers 1 & 4) is now the latter half, and viceversa.

Kill the Management node, with active sessions on the sqlnodes.

NOTE! That killing the management node will affect the DemoKit’s ability to detect things…
Let’s see (watch the time displayed):

sqlnode 10 (mypi01):
16:19 clustertest> select * from ndbtest;
+——+
 
  | i    |
  +——+ 
  |    3 |
 |    4 |
  |    2 |
 |    1 |
 +——+
 4 rows in set (0.01 sec)

sqlnode 11 (mypi02):
  16:18 clustertest> select * from ndbtest;
 +——+
 | i    | 
  +——+ 
  |    1 | 
  |    3 | 
  |    4 |
 |    2 | 
  +——+ 
  4 rows in set (0.01 sec)

Leaving those sessions there…

ps -ef | grep ndb_mgm
root      2181     1  3 14:41 ?        00:03:13 ndb_mgmd -f /usr/local/mysql/conf/config.ini –config-dir=/usr/local/mysql/conf/root      4590  2677  8 16:20 pts/2    00:00:01 ./ndb_mgm mypi01 -t 1 -e 4 status
root      4591  2677  8 16:20 pts/2    00:00:01 ./ndb_mgm mypi01 -t 1 -e 1 status
root      4598  2677  8 16:20 pts/2    00:00:00 ./ndb_mgm mypi01 -t 1 -e 3 status

kill -9 2181

ps -ef | grep ndb_mgm
root      4647  2677 11 16:21 pts/2    00:00:01 ./ndb_mgm mypi01 -t 1 -e 3 status
root      4651  2677  9 16:21 pts/2    00:00:00 ./ndb_mgm mypi01 -t 1 -e 1 status
root      4655  2677  9 16:21 pts/2    00:00:00 ./ndb_mgm mypi01 -t 1 -e 4 status

DemoKit shows:

No datanodes in the architecture, but they are still up and running, it’s just that the ndb_mgm command fails to get in contact with the ndb_mgmd.

Back to our user sessions:

sqlnode 10 (mypi01):16:23 clustertest> select * from ndbtest;
+——+
| i    |
+——+
|    3 |
|    4 |
|    2 |
|    1 |
+——+
4 rows in set (0.01 sec)

sqlnode 11 (mypi02):
16:23 clustertest> select * from ndbtest;
+——+
| i    |
+——+
|    1 |
|    3 |
|    4 |
|    2 |
+——+
4 rows in set (0.01 sec)

Now, any command sent to the management node process will fail with:
  root@mypi01:~# ndb_mgm -e show
  Unable to connect with connect string: nodeid=0,mypi01:1186
  Retrying every 5 seconds. Attempts left: 2 1, failed.

We can exit and reconnect to the cluster:
  mysql -uroot -poracle clustertest -e “select sysdate(); select * from ndbtest”
+———————+
| sysdate()           |
+———————+
| 2013-09-06 16:27:03 |
+———————+
+——+
| i    |
+——+
|    1 |
|    3 |
|    4 |
|    2 |
+——+

So the Cluster is still working…
mypi01:
  root@mypi01:~# ps -ef | grep ndbd
  root      4464     1  0 16:17 ?        00:00:00 ndbd
  root      4465  4464  6 16:17 ?        00:00:39 ndbd
  
  root@mypi01:~# ps -ef | grep mysqld
  mysql     2279  2159 12 14:42 pts/0    00:13:18 mysqld –defaults-file=/usr/local/mysql/conf/my.cnf –user=mysql

mypi02:
  root@mypi02:~# ps -ef | grep ndbd
  root      2143     1  0 15:43 ?        00:00:01 ndbd
  root      2144  2143  5 15:43 ?        00:02:16 ndbd

  root@mypi02:~# ps -ef | grep mysqld
  mysql     2190  2127  4 15:43 pts/0    00:02:05 mysqld –defaults-file=/usr/local/mysql/conf/my.cnf –user=mysql

Restart management node:
  ndb_mgmd -f /usr/local/mysql/conf/config.ini –config-dir=/usr/local/mysql/conf
MySQL Cluster Management Server mysql-5.5.25 ndb-7.3.0

And see if we can check the Cluster status now:

root@mypi02:~# ndb_mgm -e show

Connected to Management Server at: mypi01:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=3    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0)
id=4    @10.0.0.7  (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)

[mysqld(API)]   4 node(s)
id=10   @10.0.0.6  (mysql-5.5.25 ndb-7.3.0)
id=11   @10.0.0.7  (mysql-5.5.25 ndb-7.3.0)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

DemoKit displays the datanode & management node again. We’re back up and running.

About these ads

Tags: , ,

3 responses to “Raspberry Pi, MySQL Cluster ‘n’ Cream.”

  1. Rudy says :

    Hi,

    Very interesting. Could you give me the memory consumption on your raspberry’s with the cluster up and running?

    You will understand why I am asking that by looking at my page: http://wiki.ryayon.com/doku.php?id=mysql:howto:how_i_installed_mysql_cluster_on_my_raspberry_pi

    Regards,
    Rudy

    • Keith Hollman says :

      Thanks for the comment Rudy, and apologies for not replying sooner.
      To be honest, mine was just a mini project to showcase MySQL Cluster on a small solution. As you quite correctly state, Cluster is aimed at storing as much as it can in memory, so, this needs to be taken into account when deploying Cluster on any platform, not just R.Pi ‘architectures’. For a proper solution, it’s always best to evaluate Cluster for our app that, maybe it’s currently using InnoDB, and we want to go 99.999% with it. The MySQL Cluster Evaluation Guide should help us understand whether Cluster is, or isn’t, an appropriate fit. In a R.Pi we’d have to think about exactly what we want to achieve first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: