If only your database would just tell you that replication had failed or that the disk was full…
Ok some database servers do but MySQL doesn’t (yet). Another excuse to write a script

In my pursuit for total database visibility I have been searching for a tool that would tell me when something went wrong, that would simulate regular usage, and let me know if anything failed. After all SNMP can only probe so far and if your database is secure the last thing you want to do is open up another port on your server.

Don’t get me wrong there are tools out there that do a decent job of monitoring MySQL. MySQL Entperprise Monitor is one such tool. But if you want to look under the hood or add some functionality specific to your environment things start to get tricky.

So what’s this dbAlerter?

dbAlerter is the name of a project (Script) that I started developing to provide notification of key server events. It’s written in Python; Why?, I wanted something lightweight. Java would have been my first choice but the JVM eats too much ram and felt too heavy for such simple functionality.
Python on the other hand feels lighter and when running a script as a daemon it seems to take up less resources allowing the DB to use more memory and continue doing it’s job. Plus it’s been a while since I wrote anything with Python, variety is the spice of life!

Speaking of Java, if you have ever used tools like JConsole and jmap (and if not why not!) you may not have realised that since JDK 6 Update 7 there’s now one tool to rule them all - VisualVM.

VisualVM pulls together several commandline tools like JConsole, jstat, jinfo, jstack, and jmap to make monitoring and profiling a more visual experience.

Now that the changes from BSD Java have been merged into the OpenJDK and the FreeBSD Foundation have updated the JDK to 1.6.0-7 I’m pretty sure the original concept of write once run anywhere is closer than ever.

Databases are used by many different disciplines, from your DBA to your Developer, right down to the End User. They can also be viewed from many different perspectives…

DBA’s are usually interested in the server configuration, index usage etc. Developers however are often concerned with retrieval methods, datatypes and occasionally stored procedures. One thing is for sure, your database if used effectively will be changing frequently, and with so many people accessing your database do you know what the last change was? Would you know if someone added an index to a table? (would you care… )

Revision Control is often used to track changes to source code and other documents, so why not your database?.

When I first started to think about change control for my database servers one tool sprung to mind, Rancid. If you manage more than one network device you may already be familiar with the Really Awesome New Cisco confIg Differ.

Using a combination of scripts Rancid will connect to your network devices, check for changes by comparing the config with a VCS managed copy and notify you with details of the changes. Why not do the same for your database? Enter dbRecorder.

Rather than extend Rancid I decided to prove the concept by writing a quick Java application that utilised Open Source libraries like SVNKit and jYAML. The features continued to creep and before I knew it I was recording more than just table definitions. Server configuration and grant tables all found their way into my Subversion repository coupled with e-mail notification whenever anything changed - nice huh!.

The aptly named dbRecorder is currently targeted at my database of choice, MySQL. It’s far from a finished product (then again so are most of my ideas) but it’s working to a degree and can only improve over time….

One thing I never got around to was making this site work well with IE6 (or even just splash the screen with some silly message when someone using IE6 tries to connect) and with Firefox 3 soon to be released I am hoping I won’t have to. If you have not tried Firefox 3 already you can visit the site today and pledge your download to help break a world record as soon as it is released!.

It’s been a busy month so far and with Easter now upon us the year is showing no sign of deceleration.

I spent the last few weeks working hard to recover from my initial attempt at passing the Zend Certified Engineer exam. Having used PHP and other languages for several years I grabbed a copy of the Php|architect’s Zend PHP 5 Certification Study Guide, bought a few online practice tests from Zend and spent a few days scoring Excellent before heading down to the test centre confident I would be leaving with a Pass.
Forty five minutes into the exam I was racing through the questions thinking I had it in the bag only to be presented with a Fail upon clicking the End button…..Bummer

The score report gave no real indication of how close I came to passing, so wondering where I went wrong I scoured the net for any information that could help me prepare for my next attempt (which you get a 10% discount on if you ask Zend nicely). During my search I found the Zend Certification Forum, however it was broken and I had to ask for it to be fixed (which took a couple of days) before I could read others views on the exam. The Zend forum and site I have to say are not good adverts for PHP. The site often takes ages to load (is this down to PHP? people will think) and the forum feels pre web 2.0.

After a few hours of searching I finally found this PDF, the PHP 5 course material that outlines everything you need to focus on for the exam and provides enough examples to to get your head around the “gotchas” that are hidden in many of the questions. Second time round I passed, although many questions were similar to my first attempt and I answered them no different, I can only put my initial fail down to the PHP “gotchas” like:

What is the output of echo 09;

Post certification I had to ask for the Yellow Pages entry to be added (a common problem it seems), and after finally getting the page updated it seems to have reverted to its former state…..If only it was written in Java I keep thinking to myself.
Don’t get me wrong I am a big fan of PHP but feel its place is within the presentation layer (this site is written using PHP). When it comes down to middle tier processing I am yet to be convinced…

Speaking of Java, I decided that along with moving house this Easter (I need a server room ), I would open my mind to the world of software that is Oracle. The Oracle Database is hardly Open Source, however Oracle as a company do support the Open Source Community and to be honest I’m a knowledge junkie and need something fresh to feed my mind with (fear not MySQL).
Besides at the end of the day everything I learn from taking apart Oracle can be directly applied to the Open Source world, that is if it has not been applied already.

You may have noticed the Easter Bunny pic included with this post and if you are into Open Source you would have recognised him as Big Buck Bunny the star of the upcoming open movie Peach. That’s right there are only a few more weeks to the release of the movie and you can now take a look at the trailer before downloading your copy of Blender and having a go yourself - I can’t wait!

I spend the majority of my days working with OpenSource Unix derivatives and have to say I enjoy every minute of it! Not a day goes by where I do not learn something new or discover a new way to solve a problem all thanks to Unics (Everything has a beginning).

If you are passionate about GNU / Unix / Linux / OpenSource I apologise for the generalization “Unix” but wanted to focus on the OS and tools that make moving between one Unix variant and another as easy as pie.
Putting aside all the nuances that distinguish the many flavors of Unix, once you gain a core understanding of the Community, Tools and Kernels, you find that moving from one OS flavor to another is as simple as uname and man.

Take for instance my latest branch off the Unix tree - HP-UX. Having minimal exposure to HP-UX I wanted to get up to speed quick as possible, so I grabbed a copy of the HP-UX Study Guide and booked the CSA exam knowing that if I had a goal I would not just move onto the next flavor.

The book was a great resource and I only wished I had read it sooner as it covered many tools and concepts that are commonplace in SVR4 Unix derivatives such as Solaris. I felt like I was stepping back 10 years to when I first started to discover basic utilities such as cat, sed and awk but explained in so much detail that I found myself learning some new tricks!. Even if you are not interested in HP-UX this book is so well written and easy to condense that it is worth a place on your Unix bookshelf!.

As for HP-UX, well I felt right at home and only had to spend time learning the key differences such as Software Management and HP’s Partitioning Continuum technology (Virtualization to you and me) - very cool!.

Would I run HP-UX on my own servers? Probably not, it’s not OpenSource for starters (plus I don’t have the hardware), and to be honest I have been running FreeBSD for over 10 years now and no matter how many Unix/Linux derivatives I encounter not one has been able to surpass FreeBSD’s community, stability and manageability. Plus FreeBSD is Free in every sense!.

Speaking of FreeBSD you might have noticed the image in the upper left corner of this post - yes you guessed it; FreeBSD 7.0 has been released!.
I have been running a release candidate for a while now and have to say that the one thing you will notice is how fast it runs. Don’t take my word for that either, check out the Benchmarks and read through the new features explained in detail over at the BSD Devcenter. With performance like that why would you want to run any other OS?

Download your copy from here and never look back.

AuthStor came to life back in late 2007 after the Password Safe I had been using became corrupt. Not through any failure in Password Safe’s functionality but really to the way it was being used - Multi-User.

I had been seeking a decent Web Based Password Manager (Open Source of course) for ages but had not found anything that I liked. All the semi-decent software out there seemed to be Closed Source with license fees attached and to be honest I would feel more comfortable if I understood the inner workings, especially if this software is storing the key to my empire (mwuhahahaha).

So after a long history of Spreadsheets, Password Safe, Keychain and various other methods of storing passwords I finally decided to scratch the itch and roll my own password manager.

Now AuthStor is not my first attempt at a password manager, in fact I originally started to write an extension to MoinMoin that provided a method of storing passwords directly within the Wiki. This worked great for a while but was not the most secure method of managing passwords, especially as the secret key was embedded within the Python code!. I learned a lot from mmPasswords and hope to improve upon that greatly through the development of AuthStor.

So what is in store for AuthStor? (Get it? ) Well key features will include Multi-User capability, Full auditing, GnuPG encryption, REST like interface and that is just the start. Future plans include interfaces like SMS, VoIP and anything else I can think up.

Here is a sneak peak of the progress made so far:

The application itself is written in Perl and runs on top of the Catalyst Framework. Why Catalyst? Flexibility, Performance, Scalability, the list goes on… Catalyst’s plugin architecture provides multiple views (among many other things) so I can focus on the functionality and not worry about the presentation. Speaking of presentation, AuthStor makes extensive use of the Yahoo! User Interface Library to provide a rich interface with dynamic elements such as DataTable’s, Toolbars and Treeviews and all wrapped in a BSD License!!. Nice work folks!.

Expect a commit in the next few weeks…

I’m sure by now you would have read the great news that is Sun Microsystems acquisition of MySQL!

Sun have been forging ahead in the Open Source world with OpenSolaris, an Operating System that scales to Enterprise proportions and is an ideal host for the number one Open Source Database Server that is MySQL.

Whether this will have any impact on the MySQL Linux / FreeBSD / Windows offerings is something I am sure the community will be watching closely, but in my experience Java runs much faster on Solaris as it can take advantage of the MultiThreading libraries that help Solaris set those performance records.
Don’t forget, it was not that long ago that combined with Sun hardware MySQL was able to set it’s own world records!.

With that in mind it should only be a matter of time before we will begin to see the fruits of Sun / MySQL’s labour and I don’t know about you but I can’t wait!.

How this move will affect the future of other database servers such as PostgreSQL and Oracle I cannot begin to imagine.
Personally I never understood how Oracle devoted so much attention to Linux when the Oracle server (and App servers) have such a dependency on Java (which as I say always works better running under Solaris )?
As for PostgreSQL, Sun have always endorsed PostgreSQL on Solaris, will we see this focus switch to MySQL in the near future?.

One thing is for certain, MySQL combined with Solaris makes for one world class package that is going to be hard to beat!!.

I know it’s a little late, but as this is my first post of 2008 I wanted to start on a high!

After receiving my copy of the MySQL 5.1 Cluster DBA book at Christmas, I spent an hour or so each day with my head engrossed in MySQL Cluster technology, so much so that my goal of reading every Perl book I own (cover to cover) has been somewhat put on hold…

The book was much smaller than I expected, in both physical size and pages (266) which was not a bad thing as I could carry it around if needed, and each chapter could be read within 15-20 minutes, just about right for my attention span

I booked the exam a couple of weeks ago thinking I might need some incentive to fully digest MySQL Cluster details. I need not have worried, the book provides a great introduction to MySQL Cluster in easy-to-read chapters bringing you up to speed in no time, much more entertaining than the reference manual!

The exam itself consisted of 70 questions and on this occasion ended with a prompt asking to print to a file……argghhh……I suddenly got worried that the exam had crashed and that all my answers were lost?!, after all you normally get an instant result on screen and a printed record. I knew I should have used my regular testing centre Print -> File -> USB Stick -> another PC -> Adobe Distiller -> Print and I finally got the result - Passed - Phew!.

So the holidays are over and I have more time to focus on the fun stuff, like upgrading this site to the latest version of WordPress or adding GnuPG based encryption to AuthStor. If history is anything to go by the next few months are bound to see a spate of version bumps. I am personally looking forward to FreeBSD 7.0!

You never know, I might even find time to finish reading those Perl books before Perl 6 is complete

2007 is almost over and what a year this has been for Wave2!

My programming preferences have gone full circle, from beginning the year playing snakes and ladders (Python), I spent the summer drinking coffee (Java) and finally settled down surrounded by Camel’s, Lama’s and Parrots (Perl). Bizarre but never dull!

Steady progress has been made all round and as always the Open Source community has proven a valuable resource. Not a day went by where I did not discover something new or think of a new idea that could be built using Open Source Software. I only wish I had more time in the day!.

My Christmas present came early this year in the form of a MySQL 5.1 Cluster DBA Certification Study Guide. I have been putting off the final MySQL certification while waiting for the book to arrive, and now it is in my hands the only thing preventing me from taking the exam is the lack of free space over the Christmas period. Hehe

I also ordered a Nokia N810 (My Techie Present). My laptop is not small enough to carry around with me and I am hoping to capture more development time on this device. Either that or feed my mind.

Anyway I’m off to hit the WarHawk servers before getting ready to see the New Year in Amsterdam - home of the Blender Institute. With projects like AuthStor making great progress and ideas such as Unix Architect and BinaryStor slowly maturing, I cannot wait to see what 2008 holds for Wave2!

Before I head off I have to mention the Catalyst Advent Calendar. Every year they pack 24 days of tutorials and tips into the month of December. If you are looking to get the most of the Catalyst Framework you really must take a look at this resource. I was able to prototype AuthStor in under 2 weeks and enjoyed every minute!

Wave2 Wishes You a Very Merry Christmas and an Open Source New Year!!!

I read a lot about MySQL backups using LVM Snapshots on Linux, WAFL Snapshots on NetApp and more recently ZFS Snapshots. But did you know you can do the same under FreeBSD?

FreeBSD has had snapshot capability since around 2001 allowing administrators to take a frozen image of a filesystem at a given instant in time with minimal impact on the server / filesystem. So how does this help with MySQL Backups?

If like me you have loads of space on your database volume and often find yourself making frequent risky modifications to your databases, a snapshot can save you loads of time and headaches by creating a point in time marker that you can fallback on if needed.

Snapshots provide the following benefits:

1. Efficient - Only as blocks in the active filesystem are modified and written to new locations on disk does the snapshot begin to consume extra space.
2. Fast - It takes about 30 seconds to create a snapshot of an 8Gb filesystem. Of that time 25 seconds is spent in preparation; filesystem activity is only suspended for the final 5 seconds of that period.
3. Reliable - FreeBSD ensures that no disk write activity can take place during the setup of the snapshot (typically less than a second). The result is a consistent replica.

If you are running MySQL on FreeBSD, snapshots can certainly help improve your backup strategy. So how do you get started?
Well if you head over to the Wiki you’ll find a detailed guide to creating your first snapshot, but before you click that link there is another new FreeBSD feature that you may not be aware of - Security Event Auditing.

Protecting your data requires more than just regular backups and the security auditing feature in FreeBSD 6.2+ can help assist with the logging of activity on your MySQL server.

Based on Sun’s published Basic Security Module (BSM), the de facto industry standard for auditing, FreeBSD Security Event Auditing provides reliable, fine-grained, and configurable logging of a variety of security-relevant system events, including logins, configuration changes, and file and network access. These log records can be invaluable for live system monitoring, intrusion detection, and postmortem analysis.

At the moment the stable release of FreeBSD will require a Kernel compile to enable audit capability, but future releases should have audit enabled in the generic kernel. You never know by the time FreeBSD 7.0 is available I may have figured out a way to audit MySQL logins using auditd.

If your feeling brave you can download the latest monthly snapshot of the upcoming FreeBSD 7.0 release and put auditing to the test.

Right - I’m off to get a working NetUnit jar ready for the end of the week - I hope

I had one of those lightbulb moments the other day while pondering a problem I recently encountered relating to firewall changes.
The problem came to light the morning after some firewall rules had been modified, nothing new there I hear you say.
Of course at the time everything looked to be working, that is from the perspective of the few people that were actually working that late in the night. It was only when the morning came and the rest of the business started to come online that the problems were apparent. It didn’t take long to fix the problem but it got me thinking, how could we prevent this in future?

I have been spending some time looking at Unit testing recently and began to wonder if there were any testing tools or frameworks that could be used to prevent this kind of problem in future. Unit testing for software is pretty much standard practice nowadays and works well during the development phase, but once the application is in situ how can you test the operation from a networking perspective?. Web testing tools are great for validating web application functionality and I’m pretty sure there are loads of other test frameworks out there, but are they easy to use? I wanted a way to describe and validate the problem before and after the work was undertaken, and so NetUnit was born…

NetUnit aims to be simple to use, something I could give to a non-techie and have them run a Test Suite to validate connectivity or application functionality. NetUnit mimics Apache Ant in its operation and design, in fact I was going to write some Ant tasks at first but kept thinking of new ways to extend the tool in future.

The Test Suite used to describe the Tests to perform is a simple XML file. NetUnit parses this file and outputs the result of each test along the way - simple huh!.

Another benefit of this tool that immediately sprung to mind was the ability to take a Test Suite file and generate documentation for your applications external network interface or firewall policies. In fact the ideas don’t stop there and I have quite a list of features that could be implemented in future. Time permitting as always….