mysql_ufs_snapshot.sh

#!/bin/sh
# Copyright (c) 2007 TrueStep
# Name: mysql_ufs_snapshot.sh
# Author: Rory Arms - http://www.TrueStep.com/
# CDate: 2007-11-17
# Description: 
# 1. Unmounts and deletes old snapshot if found.
# 2. The mysql(1) monitor is used to flush & lock the database
# 3. Using MySQL's SYSTEM(), to generate new snapshot using mksnap_ffs(8)
# 4. Mount resulting snapshot read-only to make it available for backup
#
# Installation:
# 1. Make sure SNAP_MOUNT (see user knobs) directory exists.
# 2. Put this file in a local path, /usr/local/sbin probably.
# 3. Add a crontab(5) entry to execute this script as as often as desired.
# Example crontab entry for daily execution at 2:30:
# 30      2       *       *       *       root    /usr/local/sbin/mysql_ufs_snapshot.sh 1>/dev/null
#
# Tested with: FreeBSD 6.1, 6.2
# Deps: mksnap_ffs(8), mount(8), mdconfig(8), mysql(1), rm(1), awk(1)
# $Id: mysql_ufs_snapshot.sh,v 1.1.1.1 2007/11/26 21:35:12 rorya Exp $

# user knobs
SNAP_MOUNT="/mnt/mysql_snapshot"	# where to mount read-only snapshot
MYSQL_ROOT="/var/db/mysql" # mysql root directory, where the data lives
MYSQL_PASSWORD="" # mysql password for the root user


AUTHOR="TrueStep"
NAME="mysql_ufs_snapshot"
VERSION="2"
SNAP_NAME="mysql_snap" # only change if you want a different snapshot filename
PATH=/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin

print() {
	echo "-> $*"
}

print_error() {
	echo "Error: $*" > /dev/stderr
}

print_stderr() {
	echo "$*" > /dev/stderr
}

banner() {
	print "$NAME v$VERSION starting"
	echo
}

prechecks() {
	# Check FreeBSD version
	if [ ! $(uname -r | awk -F. '{ print $1 }') -ge 5 ]; then
		print_error "FreeBSD 5 or higher required"
		exit 255
	fi

	# Make sure MySQL is installed
	if [ ! -x $(which mysql) ]; then
		print_error "mysql(1) monitor not found"
		exit 255
	fi

	# Make sure this is the root user
	if [ ! $(id -u) -eq 0 ]; then
		print_error "You are not a superuser"
		exit 255
	fi

	# Make sure mount point exists
	if [ ! -e $SNAP_MOUNT ]; then
		print_error "mount point $SNAP_MOUNT does not exist, exiting"
		exit 255
	fi

	# Resolve partition mount and snapshot location from MYSQL_ROOT
	SNAP_FS="/$(echo $MYSQL_ROOT | awk -F/ '{ print $2 }')"
	SNAP_DIR="/$(echo $MYSQL_ROOT | awk -F/ '{ print $2 }')/.snap"
	SNAP_PATH=${SNAP_DIR}/${SNAP_NAME}

	# Make sure the .snap directory exists in the target filesystem
	if [ ! -d $SNAP_DIR ]; then
		print_error "$SNAP_DIR doesn't exist, are you sure this is a UFS2 partition?"
		exit 255
	fi
}

delete_oldsnap() {
	# Check to see if there is an existing snapshot from the last time 
	# this was used
	if [ -e $SNAP_PATH ]; then
		print "Old snapshot found"

		# unmount the snapshot
		umount $SNAP_MOUNT

		# Detach memory disk
		print "Detaching memory disk"
		mdconfig -d -u 4

		# Delete snapshot
		print "Deleting old snapshot"
		rm -f $SNAP_PATH
		# Check to make sure the memory disk detached successfully
		if [ ! $? = 0 ]; then
			print_error "memory disk did not detach, exiting"
			exit 255
		fi
		print "Old snapshot deleted, continuing.. "
		print	
	fi
}

create_snap() {
	# Check to see if there is a MySQL password
	if [ -z $MYSQL_PASSWORD ]; then
		MYSQL_OPTIONS=""
	else
		MYSQL_OPTIONS="-p$MYSQL_PASSWORD"
	fi
	# Flush mysql and use SYSTEM() to call mksnap_ffs(8) to create a 
	# UFS2 snapshot
	# This is done because the MySQL read lock is only held while the 
	# mysql(1) session is open.
	print "Launching mysql(1) monitor to lock tables and generate snapshot... "
	mysql $MYSQL_OPTIONS << SQL_MONITOR
FLUSH TABLES WITH READ LOCK;
SYSTEM mksnap_ffs /var '$SNAP_PATH';
UNLOCK TABLES;
EXIT
SQL_MONITOR
print "done"

	# Check mysql(1) status code
	if [ ! $? = 0 ]; then
		print_error "mysql(1) failed to create snapshot, exiting"
		exit 255
	fi
}

mount_snap() {
	# Attach snapshot file to a memory disk
	print "Attaching snapshot to memory disk"
	mdconfig -a -t vnode -o readonly -f $SNAP_PATH -u 4 
	# Check to make sure the snapshot attached
	if [ ! $? = 0 ]; then
		print_error "mdconfig(8) failed to attach snapshot $SNAP_PATH, exiting"
		exit 255
	fi

	# Mount memory disk
	mount -r /dev/md4 $SNAP_MOUNT
	# Check to make sure the mount succeeded
	if [ ! $? = 0 ]; then
		print_error "mount(8) failed to mount snapshot, exiting"
		exit 255
	fi
	print "Snapshot mounted at $SNAP_MOUNT"
}

print_usage() {
	print_stderr "usage: $NAME [-h]"
	print_stderr "Description: Creates UFS2 snapshot of MySQL data directory"
	print_stderr "Then mounts the snapshot for further backup operations"
	print_stderr "Dependencies:"
	print_stderr "Deps: mksnap_ffs(8), mount(8), mdconfig(8), mysql(1), rm(1), awk(1)"
}

print_version() {
	print_stderr "$NAME version $VERSION"
}

# main

# print usage if -h is specified
if [ ! -z $1 ]; then
	case $1 in
		-h|--help)
			print_usage
		;;
		-v|-V|--version)
			print_version
		;;
	esac
	exit 1
fi

banner
prechecks
delete_oldsnap
create_snap
mount_snap