North Texas MySQL Meetup Feb 2nd

Join the North Texas MySQL Meetup on Monday February 2nd at 7:00PM as Said Syed will host an open Forum type discussion on Virtualizing MySQL.

Sun Office
16000 Dallas Pkwy
Suite 700
Dallas, TX 75248

A MySQL Cluster in your hand

The MySQL Cluster product seems to intimidate a lot of folks with its complexity. The North Texas MySQL Meetup had a presentation at the January meeting titled 'A Gentle Introduction to MySQL Cluster' where a cluster was setup on a single laptop. The presentation was full of information that needs to be shared to encourage others to try out this valuable piece of software.

The following covers how to set up a cluster on a single computer. The cluster will consist of two data nodes, a SQL node, and a management node. In this case the SQL node and the management node will be on one system. The data nodes will be virtual systems running on the same host. This is not the optimal performance configuration but it will let those new to MySQL Cluster try out the product with a minimal investment in equipment and time.

You will need two free software products -- Virtualbox and MySQl Cluster. You will also need a CDROM/DVD/ISO-image of your favorite supported operating system. In this example Virtualbox was installed on a MacBook and a copy of the Ubuntu desktop ISO was downloaded.

The latest MySQL Cluster binaries is 6.3 as this is written. For those of you not used to Cluster, the version number is out of sync with the MySQL Server GA release number so do not panic that 5.1 was just GA-ed and now 6.3 is on the loose.

The MacBook's IP is 192.168.15.101 in the examples while '103 and '104 are the virtual systems.

Install Virtualbox and then install at least two virtual systems. These two system will be the data nodes. In the example below, Ubuntu was installed, the virtual images were set to use the host's network card, and the software updates for Ubuntu where installed.

A new directory was created, /var/lib/mysql-cluster, and ownership was set to user mysql. Then the MySQL Cluster software binaries were downloaded. The software was installed in /usr/local and the instructions for installing a binary were followed.

Now repeat all that for the second data node and modify the following for your /etc/my.cnf

my.cnf for data node

[mysqld] ndbcluster ndb-connectstring=192.168.15.101 [mysql_cluster] ndb-connectstring=192.168.15.101

Install the MySql Cluster software on your SQL node, in this case the MacBook. Create a directory owned by mysql named /var/lib/mysql-cluster. Grab your favorite my.cnf from a previous installation or use one of the examples from the support-files directory and add the following.

my.cnf excerpt for SQL node

[mysqld] ndbcluster ndb-connectstring=192.168.15.101 . . . [ndb_mgmd] configfile=/var/lib/mysql-cluster/config.ini

You need put the following in you /var/lib/mysql-cluster and match it to your network addresses.

config.ini

[NDB_MGMD] Id=1 Hostname=192.168.15.101 Datadir=/var/lib/mysql-cluster [NDBD DEFAULT] NoOfReplicas=1 [NDBD] Id=2 Hostname=192.168.15.104 [NDBD] Id=3 Hostname=192.168.15.103 [MYSQLD] [MYSQLD] [MYSQLD] [MYSQLD]

On the SQL/Management node, run ndb_mgmd and then open a new terminal window to run ndb_mgm. On each of the data nodes, run ndbd --initial (you can drop the --initial after the first successful start) then start mysqld with mysqld_safe --user=mysql&. Back to the SQL Node and start mysqld just like you did on the data nodes. In your window where you left ndb_mgm running, enter show and you should see something like

bash-3.2$ /usr/local/mysql/bin/ndb

-- NDB Cluster -- Management Client --

ndb_mgm> show

Connected to Management Server at: 192.168.15.101:1186

Cluster Configuration

---------------------

[ndbd(NDB)]	2 node(s)

id=2	@192.168.15.104  (mysql-5.1.30 ndb-6.3.20, Nodegroup: 0, Master)

id=3	@192.168.15.103  (mysql-5.1.30 ndb-6.3.20, Nodegroup: 1)



[ndb_mgmd(MGM)]	1 node(s)

id=1	@192.168.15.101  (mysql-5.1.30 ndb-6.3.20)



[mysqld(API)]	4 node(s)

id=4	@192.168.15.103  (mysql-5.1.30 ndb-6.3.20)

id=5	@192.168.15.104  (mysql-5.1.30 ndb-6.3.20)

id=6	@192.168.15.101  (mysql-5.1.30 ndb-6.3.20)

id=7 (not connected, accepting connect from any host)





ndb_mgm> 

Note that in this case Node #2 is the Master data node and in Nodegroup #0 while Node #3 is in Nodegroup #1. There are lots of good articles online on MySQL Cluster but I really recommend the MySQl 5.1 Cluster Certification Guide as a reference book.

So do not be afraid of the configuration complexity or worry that you need several computer systems with corresponding network gear. Give MySQL Cluster a try!

2008 certification results, 2009 news

2008 was a good year for MySQL Certification. 1,986 folks took exams which is a 14.6% gain over 2007. 736 candidates earned certifications and that is a 14.4% gain. The biggest gains were in Associate (up 300%) and DBA (up 35%) certifications. Oddly the Developer cert was down 40%.

So what happens in 2009?

The DBA and Developer exams are both changing format to performance based tests. Candidates will be tested on a virtual server and provided with a list of tasks that need to be accomplished. The DBA exam is actually in alpha est and about to go to beta. This testing is being done on two sets of virtual environments which provide their own qualities. The Linux environment is probably closer to what most DBA's working environment but the Windows version allows the candidate more control over the system. More news here later in the month.

Computer certifications will take a big leap forward with the ITCC. The ITCC allows the companies that certify a common platform to promote certification, crack down on test cheat companies, and provide a location that will list all your certifications from member organizations.

I hope you all have a great year!