Start a MySQL instance on a snapshot

OK, so we have snapshotted past instances of the data filesystem. That's useful to wholly restore a damaged file. What if, however, one only needs a small bit of that data, or wants to simply see what's changed since the snapshot on a table, or wants to run a mysqldump?

This code creates a ZFS cloned filesystem from the snapshot using zfs clone. A ZFS clone is a writable filesystem created from a snapshot. ZFS's copy-on-write semantics mean that data is only taken up by that data which was changed from within the clone.

It then creates a MySQL configuration in /tmp, including a my.cnf and other stuff, and starts up a MySQL instance using those and the cloned filesystem.

#!/bin/sh

SNAPGROUP=$1
SNAPNUM=$2
if [ -z "$SNAPNUM" ]; then
	SNAPNUM=`date +%H`
fi

echo "Starting MySQL snapshot for $SNAPGROUP-$SNAPNUM"

BASEDIR=/var/local/mysql/default
BINDIR=$BASEDIR/bin
DATADIR=/mysql/data/4.1.12/$SNAPGROUP-$SNAPNUM/var
LOGDIR=/tmp/mysql-logs-$SNAPGROUP-$SNAPNUM
if [ $SNAPGROUP = "hour" ] ; then
	PORT=34$SNAPNUM
else
	PORT=35$SNAPNUM
fi
SOCKET=/tmp/mysqld-backup-$SNAPGROUP-$SNAPNUM.sock
PIDFILE=/tmp/mysqld-backup-$SNAPGROUP-$SNAPNUM.pid

# Create clone FS to run MySQL
zfs clone mysql/data/4.1.12@$SNAPGROUP.$SNAPNUM mysql/data/4.1.12/$SNAPGROUP-$SNAPNUM

# Make directory for logs
if [ -d $LOGDIR ] ; then
	rm -rf $LOGDIR
fi
mkdir $LOGDIR
chmod 777 $LOGDIR

# Generate my.cnf with correct info
/usr/ccs/bin/m4 -DBASEDIR=$BASEDIR -DBINDIR=$BINDIR \
	-DDATADIR=$DATADIR -DLOGDIR=$LOGDIR \
	-DSOCKET=$SOCKET -DPORT=$PORT -DPIDFILE=$PIDFILE \
	$BASEDIR/var/mybackup.cnf \
		> /tmp/mybackup-$SNAPGROUP-$SNAPNUM.cnf
cd $LOGDIR
nohup $BINDIR/mysqld_safe \
	--defaults-file=/tmp/mybackup-$SNAPGROUP-$SNAPNUM.cnf 
		>>/dev/null 2>&1 &

It uses a skeleton my.cnf that is filled in by m4:

[mysqld]
basedir=BASEDIR
datadir=DATADIR
ft_min_word_len=2
ft_stopword_file=DATADIR/fulltextstopwords.cnf
default-character-set=utf8
default-collation=utf8_general_ci
max_connections=10000
query_cache_size=32000000
log-bin=LOGDIR/web-mysql1-bin
server-id=3
read-only
#log=LOGDIR/web-mysql1-query.log
log-slow-queries=LOGDIR/web-mysql1-slow.log
log-error=LOGDIR/web-mysql1.usc.edu.err
port=PORT
socket=SOCKET
pid-file=PIDFILE

[myisamchk]
ft_min_word_len=2
ft_stopword_file=DATADIR/fulltextstopwords.cnf