Testing your shiny New MySQL Pi-Cluster

So you’ve done all the hard work, now what?

We need to test that everything is working. Ie:

  • That the Management Daemon can see the NDB instances.
  • That the NDB instances are running on the two cluster nodes
  • The MySQL client can connect and talk to the NDB presented tables

start the MySQL client

and execute the following command to show available NDB nodes:

 

show global status like 'ndb_number_of%';

 

mysql -uroot -p[password you set when first running mysql]

root@cgepi1:/var/lib/mysql-cluster# mysql -uroot -phelpdesk
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.25a-ndb-7.3.0-log Source distribution
Copyright (c) 2000, 2011, 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> show global status like 'ndb_number_of%'; 
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Ndb_number_of_data_nodes | 2 |
| Ndb_number_of_ready_data_nodes | 2 |
+--------------------------------+-------+
2 rows in set (0.01 sec)
mysql>

 

The above illustrates that NDB should be available on 2 nodes and both those nodes are available to MySQL.

 

You can then verify this by using the management command line utility

root@cgepi1:/var/lib/mysql-cluster# ndb_mgm -e show
Connected to Management Server at: 192.168.0.6:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @192.168.0.6 (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4 @192.168.0.11 (mysql-5.5.25 ndb-7.3.0, starting, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.6 (mysql-5.5.25 ndb-7.3.0)
[mysqld(API)] 5 node(s)
id=50 @192.168.0.6 (mysql-5.5.25 ndb-7.3.0)
id=51 (not connected, accepting connect from any host)
id=52 (not connected, accepting connect from any host)
id=53 (not connected, accepting connect from any host)
id=54 (not connected, accepting connect from any host)

Congratulations!! This now means everything is working ūüôā Not bad for ¬£50 worth of hardware!

 

 

 


					

Starting The MySQL Cluster services

Starting the services

MySQL Cluster Management Daemon (ndb_mgmd)

I prefer to start stuff manually for demos as my poor little brain likes to know what is running and how they started.

Starting the management Daemon

ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster ¬†–ndb-nodeid=1


Should give you an output similar to:

MySQL Cluster Management Server mysql-5.5.25 ndb-7.3.0

You can check its running with a ps -aux | grep mysql and check for the ndb_mgmd process

root     16868  3.2  0.6  30692  2804 ?        Ssl  13:17   0:03 ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster --ndb-nodeid=1

MySQL NDB Service

As root or sudo

sudo /usr/local/mysql/bin/ndbd &

you should get the following output:

2013-09-02 13:07:34 [ndbd] INFO -- Angel connected to '192.168.0.6:1186'
2013-09-02 13:07:34 [ndbd] INFO -- Angel allocated nodeid: 3

Again you can check whether the process is running with ps -aux |grep mysql

root     16112  0.0  0.5  19100  2276 ?        Ss   13:07   0:00 /usr/local/mysql/bin/ndbd

Starting the MySQL Service (mysqld)

sh /etc/init.d/mysql.server start

You should get a similar output to below:

Starting MySQL
[ ok .................................

Configuring MySQL Cluster (Data + Management Nodes)

 

Ok so before we go any further I guess I should draw a little picture (I hate using visio so he’s my best drawing)

 

diag

So on ‘pi1’ we’ll be configuring the following services:

 

ndb_mgmd (MySQL Management Service Daemon) – monitors the MySQL Cluster

ndbd (Network Database Daemon) – The Network database storage engine

mysqld (MySQL client) – The client allowing you to query the database.

 

On pi2 we are just running the NDBD Daemon

 

So we need to configure the ndb_mgmd daemon. Ie tell it where the two data nodes are and how to connect. 

 

mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi config.ini

[ndb_mgmd]
hostname=192.168.0.6
NodeId=1
[ndbd default]
noofreplicas=2
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]
hostname=192.168.0.6
datadir=/home/pi/mysql/ndb_data
NodeId=3
[ndbd]
hostname=192.168.0.11
datadir=/home/pi/mysql/ndbd_data
NodeId=4
[mysqld]
NodeId=50
[mysqld]
NodeId=51
[mysqld]
NodeId=52
[mysqld]
NodeId=53
[mysqld]
NodeId=54

Next is to configure the Data / MySQL Daemon

vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
ndbcluster
datadir=/home/pi/mysql/ndb_data
ndb-connectstring=192.168.0.6
[mysql_cluster]
ndb-connectstring=192.168.0.6
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout

 

 

Getting MySQL Cluster compiled & Installed

So there are a few prerequisite’s¬†to install first
Update everything
apt-get update
Install libncurses and openjdk
apt-get install cmake libncurses5-dev openjdk-7-jdk
Set the swap size to 2GB
sudo dphys-swapfile swapoff
sudo vi /etc/dphys-swapfile  =>>  CONF_SWAPSIZE=2048
sudo dphys-swapfile setup
sudo dphys-swapfile swapon

Install mysql cluster
cd /usr/local/src
tar xzfv /home/pi/mysql-cluster-gpl-7.3.0.tar.gz
cd mysql-cluster-gpl-7.3.0
Patch MySQL to workaround a pointer type bug with ARM chipsets
(get patch from http://bugs.mysql.com/file.php?id=17637)
cd sql-common
patch -l -f --verbose -i mysql-va-list.patch client_plugin.c
cd ..
Add users/groups
groupadd mysql
useradd -r -g mysql mysql
compile the software (note this takes hours, you can try cross compiling if you feel brave)
cmake .
make
make install

Getting the Raspberry Pi OS Installed

I choose the soft float Debian based ‘wheezy’ for compatibility with the java VM
2013-05-29-wheezy-armel.img
I used a 16Gb SDcard and installed the image
I followed the guide here:

Using system tools (mostly graphical interface)

  1. Download the image from a mirror or torrent
  2. Extract the image by double clicking on the download file
  3. Connect the SD card reader with the SD card inside; note: must be formatted in FAT32!
  4. From the¬†Apple (Ô£Ņ) menu, choose¬†About This Mac, then click on¬†More info…; if you are using Mac¬†OS¬†X 10.8.x Mountain Lion then click on¬†System report.
  5. Click on USB (or Card Reader if using an in-built SD card reader) then search for your SD card in the upper right section of the window; click it, then search for BSD name in the lower right section: must be something like diskn where n is a number (for example, disk4). Note this number
  6. Unmount the partition so that you will be allowed to overwrite the disk by opening Disk Utility and unmounting it (do not eject it, or you have to reconnect it). Note: On Mac¬†OS¬†X 10.8.x Mountain Lion, “Verify Disk” (before unmounting) will display the BSD name as “/dev/disk1s1″¬†(or similar), allowing you to skip the previous two steps.
  7. From the Terminal run:
    • sudo dd if=path_of_your_image.img¬†of=/dev/diskn¬†bs=1m
    • Remember to replace¬†n¬†with the number that you noted before!
  8. Wait a LONG time!¬†(or see the note above about using /dev/rdisk#…)
  9. You’re done! Insert it in the Raspberry Pi, and have fun

Once i’d got the os booting, I expanded the filesystem to make full use of the space on the SDCard. ¬†Next came the software installation.

Raspberry Pi – Running MySQL Cluster

So my colleague Andrew Morgan did this a year or so ago, but I wanted to repeat his experiment and add a few features in order to make it a live demo. It should show how MySQL Cluster (CGE) can run even on very modest hardware..

 

The hardware has been purchased

2x Raspberry Pi’s (model b)

1x Adafruit LCD Pi Plate (16×2 LCD)

USB Hub for power

HDMI -> DVI adaptor

Netgear 100/Mbs Switch

 
Watch this space ūüôā