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 <right_date_in_right_format>" 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_20130801.log &
- Started 19:12, Ended 19:21 = 9 minutes.
make
# nohup make > make_20130801.log &
- Started 19:39, Ended 00:39 = 5 hours.
make install
# nohup make install > make_install_20130802.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.