I am currently studying for the MySQL Cluster exam, primarily because I find it a pretty interesting (for nerds) technology, but also because it is a high availability, high performance database system ideal not just for the ISP’s and telcos that are using it now, but also for web based businesses and well anyone that needs a reliable and high performance database system.

I am fortunate enough that I am able to get some real world experience with a MySQL Cluster at work through a client, but given that the system is a business critical application that must be up 24×7 at all times (and has been since 2006), I won’t be tinkering around with that.

As you might have guessed from the name, a MySQL Cluster is in fact a collection of servers tied together into one big database. It is a highly available system as 1 or more servers will each hold a copy of the same data, allowing the cluster to continue to work, even with the failure of one more servers within the cluster (known as nodes). It is also ultra fast as data and indexes are held primarily in RAM, allowing for the processing of queries in jig time across the cluster.

Ideally, even for a test cluster, you will need 3-4 servers (at least 2 data nodes, a management node and a MySQL server). This is a lot of hardware to just muck around with a new technology. Even for me…. Clearly I wasn’t going to get this at work, so I had to look elsewhere to supply my needs.

There were basically 3 options:
1) Resurrect some old machines into a cluster at home. Lots of pain with networking, old hardware etc.
2) Virtualize a decent box into 3 or 4 virtual machines using Vmware or other virtualisation technology
3) Get hosting for the cluster!

I was looking at option 2 for a while, but in the end I felt it was going to be too much work, and crucially, I felt the need to hook my cluster into a web application to get a real feel as to how it was going to work.

So that left purchasing some hosted servers for my cluster. I would need my own boxes as setting up a cluster requires root access to the servers. Shared hosting won’t do.

After realizing $300 a month, even at today’s exchange rate, was a bit too much for enhancing my training, I decided that perhaps a combination of options 2 & 3 might be the best approach: purchasing a Virtual Private Servers (VPS) solution from a hosting provider. A VPS would allow me to have complete control over the server, but without having to buy a full dedicated server.

As price was an important factor, I shopped around a bit and finally settled on Tektonik, where I now have 3 Centos 5 VPS servers running my cluster: 2 data nodes and a management node also serving as the MySQL server. The plan I have chosen is quite a low spec one, with just 256MB of RAM (RAM being the key factor for cluster data nodes), but the cluster database will be quite small so I don’t anticipate needing any more than that for the time being. VPS resources can be increased immediately via the control panel if I do need more RAM anyway.

The total cost of each VPS server is USD $15 per month, but there was a special offer at the time knocking 10% off the price, so 3 * $13.50 = $40.50, at today’s rate, a bit under 30 euro.

So for 30 euro I have a working MySQL Cluster, albeit a pretty small one, but it lets me cut my teeth into working with it as it serves a real-world application (a Facebook application I developed), and now that the cluster is set up, additional servers can be easily added to it.

More on how I set up the cluster in a future posting.

I’m forever doing web searches for little snippets I know I should have kept a note of somewhere, and I’ve decided to add them to the blog from now on as if I find them useful, someone else might too.

The MySQL replace function is one such thing that I use every so often to save myself time:

REPLACE(str,from_str,to_str)

It is great for replacing text strings in a table, saving you time. For example, I have a table called “security_pages” where a directory has been renamed and all entries in the page field (there are about 120 of them) need to be edited from “/olddirectory/page.php” to “/newdirectory/page.php”.

This would be tedious by hand, but the following code will do it quickly (Query took 0.0007 sec):

UPDATE security_pages
SET page =  REPLACE ( page, "/olddirectory/", "/newdirectory/" )
WHERE 1=1

Just replace the variables accordingly. If, like me, you have learnt that playing it fast and loose means the occasional “doh!”, you may want to grab a backup of the table before running a command like this.

ADVERT: If you are scratching your head, I am a certified MySQL professional and available for hire. Email talk@cuplaweb.com.