MySQL Tech Tour – Coming to a town near you.. probably..

Come hear me talk about whats new with MySQL and loads of other interesting things.. come see the raspberry pi project in the flesh 🙂

Sign up today! 🙂






Oracle MySQL LAMP Stack event Tomorrow in London with Zend – Free Food! ;-)

So folks, bit short notice I know, but i’ll be talking at LAMP stack event tomorrow in London. Loads of interesting stuff about MySQL and the other pieces of the LAMP Stack. You can register here:

Agenda is below..

09:00 Registration and welcome coffee
09:30 Welcome and Introduction
Simon Deighton, MySQL Sales Manager, Oracle
09:45 Features and news around Oracle Linux and Oracle VM
Wayne Lewis, Principal Sales Consultant – Oracle Linux & Virtualisation, Oracle
10:30 Coffee Break
10:45 Building highly scalable databases with MySQL
Mark Swarbrick, MySQL Sales Consultant, Oracle
11:30 PHP stack: the professional enterprise solutions provided by Zend Technologies
Jan Burkl, Solution Consultant, Zend
12:15 Q&A
12:30 Light lunch and end of seminar

Running a Java Application To Demo The Cluster

So it’s all well and good having a nice MySQL cluster running on some tiny hardware, but what can you do with it?

Well in order to demo the software at some upcoming techtours my colleague put together a little Java application to showcase what can be done.

I want to run my raspberry pi’s headless so in order to get this to work I needed to install a vncserver

sudo apt-get install tightvncserver

Once this is installed make a note of the display you set up and check you can connect to it via a vnc client.

Install the java application

configure the config file for the MySQL credentials

Run the java application in a shell window on the remote VNC session making sure the screen variable has been set accordingly

export DISPLAY=1:4

When you have it all working it should look like the following

Screen Shot 2013-09-05 at 10.06.10

Monitoring The Cluster Using the LCD Panel

I thought it would be nice to have a display on pi1 (the primary node) so found an LCD kit that hopefully I could write a little script to hack around to display the status of each data node.

So I purchased the Adafruit Char 16×2 LCD Plate from:×2-LCD-kit

Then set about soldering it all up

20130715_135813_Macaulay Ave


20130715_143836_Macaulay Ave

I followed the guide here:×2-character-lcd-plus-keypad-for-raspberry-pi/assembly

Once the kit was all soldered up you have to enable the linux kernel to recognise it

I followed the guide here×2-character-lcd-plus-keypad-for-raspberry-pi/usage

I then modified the sample code to get the output from the cluster node status. The code I used is below

from time import sleep
from Adafruit_CharLCDPlate import Adafruit_CharLCDPlate
from subprocess import *
from time import sleep, strftime
from datetime import datetime

# Initialize the LCD plate. Should auto-detect correct I2C bus. If not,
# pass '0' for early 256 MB Model B boards or '1' for all later versions
lcd = Adafruit_CharLCDPlate()
# Clear display and show greeting, pause 1 sec
lcd.message("MySQL Cluster\n Running On Pi")
cmd = "ndb_mgm -e show |grep ^id=[3,4]|awk {'print $1 $2 $3}'|sed s/=//g|sed 's/[(]/ /g'"

def run_cmd(cmd):
 p = Popen(cmd, shell=True, stdout=PIPE)
 output = p.communicate()[0]
 return output
while 1:
 clusterstatus = run_cmd(cmd)
 lcd.message('%b %d %H:%M:%S\n'))
 lcd.message(( clusterstatus ))

# Poll buttons, display message & set backlight accordingly
btn = ((lcd.LEFT , 'left'),
 (lcd.UP , 'up'),
 (lcd.DOWN , 'down'),
 (lcd.RIGHT , 'right'),
 (lcd.SELECT, ''))
prev = -1
while True:
 for b in btn:
 if lcd.buttonPressed(b[0]):
 if b is not prev:
 prev = b

The final result

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
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)


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:
Cluster Configuration
[ndbd(NDB)] 2 node(s)
id=3 @ (mysql-5.5.25 ndb-7.3.0, Nodegroup: 0, Master)
id=4 @ (mysql-5.5.25 ndb-7.3.0, starting, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @ (mysql-5.5.25 ndb-7.3.0)
[mysqld(API)] 5 node(s)
id=50 @ (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 ''
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 .................................