Moving to MySQL

I’m currently involved in moving an application from SQL Server (2000) to MySQL.  The reasoning isn’t terribly relevant, but primarily it’s an open source v. closed source issue.  This (aging) J2EE app required a proprietary bridge between the application server (Java on Linux) and the database (SQL Server on Windows 2000 Server).  That odd adapter has caused us all stress over the years.  Being proprietary, we didn’t know exactly how it worked nor whether it would be around tomorrow.

The cost was also a consideration.  Updating the server to the current version would likely cost us thousands of dollars just for the RDBMS itself.  We’d likely move to the latest OS and upgrade the hardware, too.  It got very pricey, very quickly.

We came to the conclusion that a better solution would be to migrate the whole mess over to MySQL on a Linux server.  I am intimately familiar with MySQL and knew that for very little dough we could provide a fast data store with replication for fail-over, and we’d eliminate that vaguely disconcerting feeling that relying on closed source proprietary software creates.

A More DIY Model of Disaster Recovery

However, one of the nice things that MSSQL does provide is a backup mechanism from within that database manager itself.  MySQL does not.  I needed to evaluate my options given the situation and choose the most reliable, simplest-to-implement backup solution that wouldn’t cost additional money nor require disruptive, time-consuming reconfiguration of existing systems.

It appears to me that there are three common methods for backing up a simple MySQL database:

1. mysqldump
   Shut down the database, dump out a text file with all of the SQL statements required to recreate the database, and restart.  Then, back up the file like any other file.
2. LVM snapshots
   Lock the tables, create a logical volume snapshot of the data partition, and unlock the tables.  Then back up the snapshot and remove it when completed.
3. Replication
   Create a slave database server that replicates the target server, and simply stop the slave, back up the data, and restart the slave.

Mysqldump has the advantage of being very simple to setup and script.  Also, the backup is a text file, and so a ding to the file isn’t likely to destroy it completely.  With a binary backup, a data ding could render the whole thing useless.  Still, it requires a fair amount downtime, which is bad, and the backup files can be very large, which just makes backups and restores take that much longer.

LVM snapshots sound great.  There’s very little downtime (just enough to create the snapshot), and the backup is the actual datafile.  It’s likely to be smaller and restore takes essentially no time since the data file can just be dropped back into a running server and used.  Still, snapshots only work when the filesystem is built on logical volumes (another good topic to discuss, I think).  We were looking at some older systems that used standard physical partitions, and I was not enthusiastic about shutting them down, backing everything up, and reinstalling on LVM just to accomplish this.

So, I settled on replication.  MySQL provides a very slick ability to create a master-slave replication arrangement wherein one server maintains an identical copy of the master database (god willing).  There’s just a bit of setup, but with this arrangement, there’s no downtime for the master, and the slave can be backed up in any convenient way without concern for the time involved.  Also, should something befall the master, it is quite likely that the application server can just be pointed to the slave and carry on as though nothing happened.

As it happened, we had a machine in our colocation cabinet just sucking power, doing nothing.  I set up a replication slave on it in just a few minutes.  Then, I wrote a quick script to shut it down, run the backup, and restart the slave.  I added that to the cron table, and we had our backups licked.

Now, obviously, I’m not talking about a very complex setup here.  This is an application that is critical to the business, but not what I consider heavily used.  Should it ever graduate to the level of something like a load-balanced cluster, I’ll have to revisit this topic.  In the meantime, we’re securely covered with a backup process that doesn’t require downtime and provides the side-benefit of a spinning fail-over server as an even faster disaster recovery path.

A Condescending Lecture on Backups

Backup, backup, backup… it’s so often overlooked until it’s too late.  Do yourself a favor, spend a little money and time and back up more than you think you need to.  When the Bad Thing happens, a good backup strategy can mean the difference between a routine mechanical replacement and restore (everyone’s happy!) and an uncertain scrounging of data or total loss (ain’t nobody happy).  Make it a priority.

And like most things, the best way to start is to just start.  Now.  Don’t worry if it’s not perfect.  Just take a disk image if that’s all you can do.  Then, consider what would happen if various nasty scenarios occurred.  Then think about how you might recover.  What if the machine’s stolen (it happens)?  What if the machine bursts into flames (that’s happened to me)?  What if an earthquake crushes the colo (probably not, but what if)?

Classify your data by criticality and back it up accordingly.  I recently nearly lost my company books when a power surge took out my active drive and the routine backup drive.  I was lazy and calculated the probability that both of those drives would fail at the same time and decided without really thinking about it that I’d get around to good backups… later… sometime… Luckily for me, I’d had at least the forethought to take an image of my drives and store them offline.  I’d forgotten that I’d done that and spent the better part of a day engaged in self-loathing.  Now, I regularly take some time to consider how I might improve my backups– net-based, portable disks swapped offsite, RAIDed online storage, or maybe all of the above.  It’s done wonders for my comfort factor to know that if my backups turn out to be insufficient, it’s likely that I’ll be more worried about the zombies than my business records.

Oh, happy Halloween.