Snapshoting virtual machine running mysql database

Backup in virtualized enviroment is mostly based on snapshoting entire virtual machine and saving this state to backup repository. This snapshot does not include memory of virtual machine. When you restore virtual machine from this snapshot filesystem is in unclean state, but journal filesystem can handle this. From mysql point of view server crash happended and database can be corrupted and unrecoverable, depending on what queries where running during snapshot. This unpredicted and unconsistent state you don’t want to have as a backup of your data.

Veeam provides article Hot Backup of MySQL on a Linux VM which describes how to use vmware tools pre-freeze and post-thaw scripts to suspend mysql database or create dump using mysqldump. But for some services downtime is not acceptable during backup and database can be very huge for dumping and dump itself need extra space to store inside vm.

More elegant solutions is to temporary put database into read only state during snapshot by issuing “FLUSH TABLES WITH READ LOCK”. On Debian we can use existing debian-sys-maint account. Following script will try to put database in readonly state on background and will be waiting for lockfile which should appear if “FLUSH TABLES WITH READ LOCK” suceed. Waiting for lock have 5 minutes timeout, after it snapshot continues even if lock was unsucessfull. If script is unable to connect to database, it will return exit code 1 and vmware will report error snapshoting virtual machine.

pre-freeeze-script

#!/bin/bash

# howto:
# put this script as /usr/sbin/pre-freeze-script
# and make symlink to it
# ln -s /usr/sbin/pre-freeze-script /usr/sbin/post-thaw-script

# config:
# when running on debian we can use existing debian-sys-maint account using defaults file
# otherwise, specify username and password below using use_credentials

#use_credentials="-uroot -p"
defaults_file="/etc/mysql/debian.cnf"
timeout=300
lock_file=/tmp/mysql_tables_read_lock
###

# are we running as post-thaw-script?
if grep -q post-thaw-script <<<$0; then
        mysql_pid=$(cat $lock_file)
        echo "$0 sending sigterm to $mysql_pid" | logger
        pkill -9 -P $mysql_pid
        rm -f $lock_file
        exit 0
fi

if [ -f $defaults_file ]; then
        opts="--defaults-file=$defaults_file"
fi

if [ -n $use_credentials ]; then
        opts="$opts $use_credentials"
fi

sleep_time=$((timeout+10))

rm -f $lock_file
echo "$0 executing FLUSH TABLES WITH READ LOCK" | logger
mysql $opts -e "FLUSH TABLES WITH READ LOCK; system touch $lock_file; system sleep $sleep_time; system echo lock released|logger; " &
mysql_pid=$!
echo "$0 child pid $mysql_pid" | logger

c=0
while [ ! -f $lock_file ]
do
        # check if mysql is running
        if ! ps -p $mysql_pid 1>/dev/null ; then
                echo "$0 mysql command has failed (bad credentials?)" | logger
                exit 1
        fi
        sleep 1
        c=$((c+1))
        if [ $c -gt $timeout ]; then
                echo "$0 timed out waiting for lock" | logger
                touch $lock_file
                kill $mysql_pid
        fi
done
echo $mysql_pid > $lock_file