Systems Administration

Automated backups of MySQL databases

Unless you have intelligent backup software that can do something smart to backup your databases, restoring a backup of a running MySQL server is like restarting your database after a hard system crash, it's a crap shoot. Since I don't have any fancy backup software that can help I decided to use mysqldump to create a snapshot of my database server and write it out to a compressed SQL file. Then my (dumb) backup software can continue to be used and I will be able to recover easily if my server dies.

Here's the quick and dirty script:

#!/bin/sh
#
# This script automates a call to mysqldump
# and sends the output to a file in a backup
# directory. The script is set up to keep
# seven days of history.
#
# Before you can run this script you must
# set up a MySQL user that can perform the
# backup. This user must have permission to
# SELECT and LOCK TABLES. The user should not
# be permitted to access MySQL in any way other
# than through the local socket. Here's how the
# user should be created:
#
# GRANT SELECT,LOCK TABLES ON *.* TO 'SomeUser'@'localhost' IDENTIFIED BY 'SomePassword'
# FLUSH PRIVILEGES;
#
# This script should be owned by root and only
# root should be able to read, write, and
# execute it. (i.e., chmod 700)
#

Upgrading MySQL from version 3.23 to 5.0.x

I recently had to upgrade a moldy old MySQL database server from version 3.23 to 5.0.x. Instead of stepping from 3.23 to 4.0, then from 4.0 to 4.1, and finally from 4.1 to 5.0.x I decided to use mysqldump.

I ran the following command on the old database server:
/path/to/mysqldump -u root -p -h oldserver.example.com --opt --all-databases > bigdump.sql

Then all I had to do was move the bigdump.sql file over to the new server and run the following command:
/path/to/mysql -u root -p -h newserver.example.com < bigdump.sql

Now all that is necessary is to flush the privileges so that users can access the databases. I logged into MySQL:
/path/to/mysql -u root -p -h newserver.example.com mysql

High Availability Web Services Using HAProxy

I was recently tasked with increasing the up time of my employer's main Web site. The site uses a content management system that lives on two Windows/IIS servers. (I know, the system was purchased before I was hired.) One server is for making changes to content (design-time server) and the other is the public web site (run-time server). The design-time server has a complete copy of the site which is replicated to the run-time server. Unfortunately the run-time server has a habit of refusing to serve pages at the most inopportune times, usually when I'm on vacation or somewhere without a computer.

Butt Kicking Chair

A couple of years ago I was sitting in one of those mind numbing meetings about stupid users or some such thing when I began to doodle and hit upon an idea. Wouldn't it be cool if all of our users sat in specially designed (or retrofitted) chairs that were capable of producing a shot to the sitter's posterior. The idea called for a chair, a boot, a lever, an actuator, a small computer with a network connection (wired or wireless), and some custom software. The computer would provide a network interface that would allow an administrator or help desk person to send a request to the chair and the person sitting in it would get a single kick in the pants. The idea for the interface later morphed into a web page and/or XML-RPC interface that listened to requests from authorized administrators which would trigger the butt kicking as well as various presets (single kick, small whooping, smack down, death by boot, etc).

Pages