mysql_ufs_snapshot

mysql_ufs_snapshot

Bourne shell script for FreeBSD servers that use MySQL and the UFS2 filesystem.

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 external backup.

It's meant to be used with cron(8) as a way to do make daily MySQL snapshots.
Also worth noting, other than the mysql monitor, it only depends on the
FreeBSD base system.

It has only been tested on FreeBSD. If you make changes to get it
working on other 4.4BSD systems that support UFS2, feel free to send any
enhancements.

Sample outputs:

# mysql_ufs_snapshot.sh -h
usage: mysql_ufs_snapshot [-h]
Description: Creates UFS2 snapshot of MySQL data directory
Then mounts the snapshot for further backup operations
Dependencies:
Deps: mksnap_ffs(8), mount(8), mdconfig(8), mysql(1), rm(1), awk(1)

# mysql_ufs_snapshot.sh
-> mysql_ufs_snapshot v2 starting

-> Old snapshot found
-> Detaching memory disk
-> Deleting old snapshot
-> Old snapshot deleted, continuing..
->
-> Launching mysql(1) monitor to lock tables and generate snapshot...
-> done
-> Attaching snapshot to memory disk
-> Snapshot mounted at /mnt/mysql_snapshot
#

#!/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