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!

Upgrade exam or not to have upgrade exam

The MySQL 5.1 DBA exam is in alpha-test right now and some of the reviewers have had questions about upgrade exams. The new exams are no longer multiple choice. They are based on a candidates performance with a real database on a virtual server.
In the past I have stated that there was not going to be an upgrade exam. This was due to the change in exam format and the history of previous upgrades not being popular. Less than forty folks took the Core to Dev upgrade exam (about 5% with that certification) and the numbers are worse for the Pro to DBA upgrade. Why spend time on something that is not going to be cost justifiable?

But my reviewers are trying to convince me to have an upgrade. It would not be that much more work to offer a hands that could be taken over the Internet. You would have to SSH into a remote server and would have an hour to complete 3-5 short tasks featuring MySQL 5.1 new features. Hopefully you still remember your 5.0 knowledge.

So, would you a) want a 5.0 to 5.1 upgrade exam and b) what would be a reasonable cost?

Certification Magazine's 2008 survey

Certification Magazine has published their 2008 salary survey. But it is more than just a salary survey. Responses from more than 150 countries revealed some very interesting information.

Who has the certifications: 60% percent of those surveyed were in their current job for less than four years. The majority were in IT for less than a decade. But the majority had at least three certifications:

Who paid: 48.3% have their employers pay for their certifications.

How did they study for their exams: Over 48% used self-study books. Practice exams and on-the-job training were used by over 66%. About 75% viewed their prep materials as good or better.

What are they being paid: Figure 7

So what catches your eye in this survey?

Your help is needed on the MySQL 5.1 Developer Exam

The news of the MySQL 5.1 GA announcement brought many emails asking about the 5.1 exams. First, let me state that if you are studying for any 5.0 exam please keep on with your plans. The 5.1 DBA test is inching closer to alpha test and will not suddenly pop up without warning. It is on the way but most of you will not get to see it until well into 2009. Watch this blog for updates on how the testing is proceeding. Second, the 5.0 exams will not be disappearing anytime soon. Nor will they start 'expiring'.

But I do need help with the new 5.1 Developer exam. The new exam will be part multiple choice and part 'hands on' or performance based testing. Like 5.1 DBA exam mentioned earlier this year, I need the help of some of you to take a look at the new material and judge its relevance. No, getting a peek at this material will not help you with the new exam when you sit for the test. But if you want to help define just what it means to be a MySQL Certified Developer, this is opportunity knocking hard at your door.

So what does it really mean to be a MySQL Certified Developer? Below is a list of what an attendee at a MySQL for Developers class should be able to accomplish at the end of that class. But how many of you hard core coders do all or most of these tasks on a regular basis?

What task can you hand off to your local DBA or do you do both jobs? Are any of these items too simple, too complex (optimization?)? Is anything missing? What do you do to ascertain the skill level of someone who shows up at your office who wants to be a developer?

So put in your views in a response to this posting or send an email to Certification @ MySQL.com with the subject of 'DEV51help'

• Describe the MySQL client/server architecture
  
• Understand the steps necessary to invoke MySQL client programs
  
• Utilize the general-purpose mysql client program to run queries and retrieve results
  
• Perform queries and analysis of data utilizing the MySQL Query Browser graphical user interface (GUI)
  
• Explain the MySQL connectors that provide connectivity for client programs
  
• Select the best data type for representing information in MySQL
  
• Manage the structural characteristics of your databases
  
• Manage the structural characteristics of the tables within your databases
  
• Utilize the SELECT statement to retrieve information from database tables
  
• Utilize expressions in SQL statements to retrieve more detailed information
  
• Utilize SQL statements to modify the contents of database tables
  
• Write join expressions in your SQL statements to obtain information from multiple tables
  
• Utilize subqueries in your SQL statements
  
• Create views utilizing SELECT statements to produce "virtual tables" of specific data
  
• Perform bulk data import and export operations
  
• Create user defined variables, prepared statements and stored routines
  
• Create and manage triggers
  
• Use the INFORMATION_SCHEMA database to access metadata
  
• Debug MySQL applications
  
• Configure and Optimize MySQL
  

When MySQL splashes back too much

Sometimes MySQL is too helpful. Recently I was helping a very large man working on a very small laptop that had an overly sensitive mouse. Trying to scroll back and forth to examine the system variables with the mysql command-line client was very painful. It reminded me of the Atlantic Bottlenose Dolphin named Kai at the Texas State Aquarium pictured here that was too helpful when I tried to get him to splash 'just a little' for a picture. In this case the server was returning too much to see and the very large man in frustration handed his computer to me.


mysql> pager more
PAGER set to 'more'


I returned the laptop and the very large man could now control the scrolling. After a few moments we found what we were looking for.

'So how do I turn this pager off?'


mysql> nopager
PAGER set to stdout



You can set the pager to a file or a program on any system but Windows (no popen() function). And it is one of those little things you need to pass on to MySQL novices to keep them from trying to 'drink from a fire hose'.

Getting free tools for your next certification exam

One of the problems with living in times as they are now is the lack of funds for what I like to call non-directed autodidactic exploration. Some of you would call this 'investigating new tools' but most would call it getting 'toys'. How do you know if D-trace, the MySQL Query Analyzer, or the next new announcement is that new tool that you can not live without if you do not get a chance to live with it? In the past I was able to rig an old laptop, my new laptop and an old development system to play, er, experiment with MySQL Cluster before taking my Cluster DBA exam.

Cisco CCIE candidates end up with an expensive pile of routers, hubs, switches, and cables in preperation for their lab exam. I wanted to get back up to speed on Solaris and went hunting used hardware for Sparc system so I could learn all the new items added since the 2.5.1/2.6 days (and boy did they add stuff). In the back of my mind is using some holiday time to work on a Solaris 10 Certification.

I also wanted hardware to let me investigate OpenSolaris, Glassfish, and the latest BSD-based releases. Plus there is that experiment I want to do with the Proxy Server and sharding. The list of wants was a lot longer than the funds on hand. And the idea of adding a pyramid of box past their prime did not appeal to me nor did the prospect of a higher electrical bill.

Virtual Box is a virtualization software that is freely available under a GPL license. Very quickly I was able to install Virtual box on my Mac and configure Ubuntu and OpenSolaris clients.

Nothing is really free. Each client takes a dozen or so GB of disk space. Running both clients, NeoOffice and Thunderbird at the same time makes my MacBook work hard. The good news is that is easy to remove the clients so that old opportunity cost is relatively low.

Now I have Glassfish and OpenSolaris on one virtual machine and can use my Mac and the virtual Ubuntu as clients. I can then query a MySQL 5.1 instance on the Ubuntu system and watch the Proxy Server re-route the request. Plus I was able to load the latest Zend Framework without having to disturb an older version on my main development system. Total cost out of pocket $0. Disk space cost about 25 GB. Time spent? Well that took about an hour for the first virtual server and twenty minutes for the second.

But now I have systems that I can use to explore all those new tools without having to crack open the wallet. And I have all these new toys, er, tools to learn. Times are good.

Sun's CEC MySQL Certification

Sun's Customer Engineer Conference is now history and over forty new Certified MySQL Associates are on the street. Track 15 aka MySQL was a new session for Sun and CEC was new for George and yours truely.

George Trujillo did a wonderful job, as he always does, in getting a large room full of people with various skills up to speed with MySQL. Three sessions a day most for three days after a long Monday were tough but the folks in Track 15 hung in there. A few had Oracle or other database experience but most were new to the subject. By Thursday night they were replicating databases. Zero to replication in eight sessions. That is very impressive.

This morning was my turn and the candidates listened intently to my brief review and then tore into the test. As they finished, I made a point to try to ask each of them how they did on the test and if they thought the class has been worth the time. All agreed that the class was a great. George and I had concerns before the track started about providing a quality experience and worked hard to provide guest speakers, relevant labs, along with the outstanding materials used in MySQL Training Classes.

Now Las Vegas awaits and I am off to see more than just the conference hotel.

Every Techie under 35 has has used MySQL in one way or another

Sun's 2008 Customer Engineering Conference (CEC) is an opportunity for Sun engineers to get in depth training. So over 2,000 Sun engineers are kept indoors at Paris Las Vegas from eight in the morning to eight or ten at night to get up to speed on new subjects.  This year MySQL is one of tracks and over eighty engineers are being trained by George Trujillo.  This is also a record class size for a MySQL training class.  And at the end of the week they will take a MySQL Certification exam.

Rather than a standard DBA class, George has opened up some time to other to teach.  Cliff Conklin brought up valuable insights on storage engines.  Anders "Make ZFS GPL" Karlsson provided the students a view into how MySQL is integrated into organizations, clustering and database performance.  We even had the SVP Marten Mickos take some time out of his heavy schedule to discuss a high level view of where MySQL came from and where it is going.  Later in the week there are other speakers scheduled.

The quote I am using to title this entry 'Every techie under 35 has used  MySQL in one way or another' caught me by surprise.  Marten made this statement and I noticed that very few people in the room jumped when they heard that.  The engineers in attendance are working hard to understand how to best fit MySQL to support their customers.  A good number of them have DBA level experience with other DBMSes. Many have confessed they have been getting queries from customers about MySQL since the January purchase.  George is providing the heavy technical training and the cameo appearances are filling the other gaps. And the attendees are really soaking up the material.

Many are cramming with the MySQL 5.0 Certification Study Guide for the certification exam on Friday.  That is a lot of dedication considering the many available 'distractions' in Las Vegas.  And it gives a good feeling for the customers of these engineers after seeing their dedication.