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:

http://www.oracle.com/us/dm/17128-emeafm13045970mpp005-oem-1989140.html

Agenda is below..

Agenda
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:

https://www.modmypi.com/raspberry-pi-expansion-boards/raspberry-pi-blue-and-white-16×2-LCD-kit

Then set about soldering it all up

20130715_135813_Macaulay Ave

tada!

20130715_143836_Macaulay Ave

I followed the guide here:

http://learn.adafruit.com/adafruit-16×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

http://learn.adafruit.com/adafruit-16×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

#!/usr/bin/python
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.clear()
lcd.message("MySQL Cluster\n Running On Pi")
sleep(1)
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:
 lcd.clear()
 clusterstatus = run_cmd(cmd)
 lcd.message(datetime.now().strftime('%b %d %H:%M:%S\n'))
sleep(2)
 lcd.clear()
 lcd.message(( clusterstatus ))
 sleep(2)

# 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:
 lcd.clear()
 lcd.message(b[1])
 prev = b
 break

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