Over the last couple of days, I’ve turned my Inner Geek into DBA geek in a manner of speaking.

As a Web developer, I’ve of course been messing with databases for years in different capacities, but over the last couple of days I concentrated on two database-related tasks in an effort to better myself/my Web skills.

And it’s fun!

The items:

• Replication (in this case, with MySQL)
• Perl DBI

Without any further ado…

Replication (in this case, with MySQL)

Yes, I have long understood the whole concept of replication. I’ve worked with replicated, clustered, fault-tolerant DBs for years.

It’s just that I never really had a hand in actually setting up/maintaining any DB other than a single, stand-alone instance until now.

Yesterday, in a (for the most part) completely painless process, I configured replication from the MySQL instance on my main Linux box (master) to a MySQL instance on my main Windows box (I use SQL Server for my Windows apps, for the most part).

I was inspired when I read – on his blog – that Jeremy Zawodny’s MySQL book has been released.

Zawondy is a MySQL guru who works for Yahoo!, and he has much knowledge about the subject (and a good sense of humor – he’s worth reading for non-tech matters, as well).

One chapter of the book was available on the O’Reilly site (the publishers), the chapter on replication. (Note: Link to download page; chapter is a PDF file.)

So I downloaded it and thought, what the hell, let’s set up replication here!

And – as mentioned above – it was rather trivial to set up. For all I bitch and moan about MySQL (legitimately, I think), it still has a lot going for it. And I use it more every day.

One thing JZ’s tutorial left out was the issue of how to set up replication with Windows – the examples were all Linux-centric (which is fine, as MySQL is primarily a *NIX product).

However, since I rarely use the Windows MySQL, I had to dig around to find things. Such as the base configuration file.

On Linux: /ect/my.cnf

On Windows: c:/winnt/my.ini (may differ with different installations; but the file is “my.ini”)

Once I found that file, I made the changes, restarted servers and … damn… it just worked.

I’m sure I did some things stupidly, but that’s part of the learning curve – when something breaks, you figure out why and that’s how you get better.

But – so far – nothing’s broken. Excellent.

Perl DBI

I’ve just never gotten around to messing with this (I bought a book about it over two years ago…), simply because I’ve primarily done Web programming, and the scripting languages – ASP, ColdFusion, JSP, PHP – have been more than adequate for most tasks.

Now that I’m working more on learning back-end tools – CRON, parsing, data loads, shell scripts and so on – it seemed to make sense to (begin to) learn the Perl DBI so I could run simple Perl scripts to do a whole bunch of stuff that would be more convoluted with Web scripting languages.

So I wrote my first Perl script that uses the DBI – a CRON job that runs each night and updates a given database/table in my MySQL database server.

It’s a trivial example, but something that mimics a file I had written in PHP and does the same in less lines.

That’s good!