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