Solo Technology

When I first started using WordPress as my blog software, I was hosted on a windows 2003 machine, running windows versions of MySQL and PHP with the built in IIS (aka WIMP).

Before I get started, let me insert a thought here: I’m a complete freak about database backups. Gotta have ‘em. Get edgy and weird when they don’t work. I’m stupidly cavalier about normal file system backups, but boy, I gotta have recent database ones. go figure…

Now this was my first “real” experience with a MySQL database that I considered “production” — I’ve messed with the product a lot over the years, but never for anything that was deemed important enough to actually backup. While looking around for options (and there are many), I found that the MySQL folks themselves have a nice little Admin tool that runs under windows called MySQL Administrator. Key feature, for me, was that it was easy to use and handled backups. Handled them well, actually. Ok, free doesn’t hurt either. You just define a backup set and indicate that you’d like it to be a repeatable thing — the program would write an entry right into Windows’ event scheduler. There’s some small tweaks to be done (define a connection name in MySQL Admin first!) but it works great.

I then moved to Linux. So now a LAMP (Linux, Apache, MySQL, PHP) world. But hey, I can still use the MySQL Administrator program, I’ll just run it on the linux machine. And, at first, that seemed like it would work well. However, I blew a couple hours one morning this past weekend trying to coerce the dang thing to do a backup. It continually hollered something that gave me the impression it would only work if the server was in ANSI mode (something about ANSI quotes). Well, ok then… i figured that out, got a backup done and realized that broke the blog. Doh. Off goes the ANSI switch and I ponder my next step.

The next step was to install the windows version of MySQL admin on my windows workstation. Indeed, I finally realized there was absolutely no reason that the backups had to happen on the same machine as the server! Talk about a blinding flash of the obvious, but cut me some slack — I do backups, after all. So, this desktop machine now has a windows job scheduled to backup the database on my linux server. Works well and heck, even helps save the data should I lose a drive on the server (I can rationalize well too).

After the fact, I think I noticed something about ansi quotes as an option when defining the connection to the database. Heh, I bet that was my issue when trying to use the linux version of Admin.

2 Responses to “MySQL and more than one way to skin a cat”

1. Peter on February 9th, 2006 5:52 am
   

   I’m surprised you’re still using MySQL Administrator. mysqldump as a crontab works quite nicely. You can even inject some shell logic to get nicely named files and pipe the output through bzip to get tight little bundles.

2. chris on February 9th, 2006 6:01 pm
   

   Yeah, ya know I actually pondered that option. But I fell “in like” with the idea of the backups being on a different machine, so I think I’ll run it as is for a bit.

Leave a Reply