http://www.oracle.com/us/dm/29127-emeafm13045970mpp014-oem-2019447.html
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! 🙂
MySQL Fabric Presentation
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
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
tada!
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)
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
(get source from http://dev.mysql.com/downloads/cluster/7.3.html#downloads)
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)
- Download the image from a mirror or torrent
- Extract the image by double clicking on the download file
- Connect the SD card reader with the SD card inside; note: must be formatted in FAT32!
- 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.
- 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
- 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.
- 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!
- Wait a LONG time! (or see the note above about using /dev/rdisk#…)
- 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.
Mark Swarbrick - Everything MySQL In The UK 