Hot-backup of mysql with btrfs

hotbackup.sh

#!/bin/bash
#@****************************************************************************
#@ Author : Mathieu GOULIN (mathieu.goulin@gadz.org)
#@ Organization : Gadz.org (www.gadz.org)
#@ Licence : GNU/GPL
#@
#@ Description :
#@
#@ Prerequisites :
#@ Arguments :
#@
#@****************************************************************************
#@ History :
#@  - Mathieu GOULIN - 2015/12/26 : Initialisation du script
#@****************************************************************************

# Static configuration
cd `dirname $0`
script=`basename "$0" | cut -f1 -d"."`
log_file=`pwd`/$script.log

# Usage function
function usage () {
    # TODO - Write the good stuff here...
    echo "$0 [start|stop|restart]"
}
# Help function
function help () {
    usage
    echo
    grep -e "^#@" $script.sh | sed "s/^#@//"
}

# Log function
write_log () {
    log_state=$1
    shift;
    log_txt=$*
    log_date=`date +'%Y/%m/%d %H:%M:%S'`
    case ${log_state} in
        BEG)    chrtra="[START]"      ;;
        CFG)    chrtra="[CONF ERR]"   ;;
        ERR)    chrtra="[ERROR]"      ;;
        END)    chrtra="[END]"        ;;
        INF)    chrtra="[INFO]"       ;;
        *)      chrtra="[ - ]"        ;;
    esac
    echo "$log_date $chrtra : ${log_txt}" | tee -a ${log_file} 2>&1
}

test_rc () {
  if [ "x$1" != "x0" ]
  then
    write_log ERR "Erreur RC=$1"
    exit $1
  fi
}

. `dirname $0`/../config/config.sh

TH=`date +%Y%m%d%H%M%S`

if [ -f ${log_file} ]
then
    write_log ERR "An other backup process is running, if not please remove the file :  $log_file and restart hotback"
    exit 255
fi

if [ ! -S /appli/mysql/${instancename}/run/mysqld.sock ]
then
    write_log ERR "no such file : /appli/mysql/${instancename}/run/mysqld.sock"
    write_log ERR "Mysql not running, please start with exploit.sh before run hotback"
    mv $log_file ${log_file}_$TH
    exit 255
fi

pipe=`mktemp /appli/mysql/${instancename}/run/backuppipe.XXXXXX`
rm $pipe

if [[ ! -p $pipe ]]; then
    mkfifo $pipe
fi

backup_sql_start="FLUSH TABLES WITH READ LOCK;"
backup_sql_stop="UNLOCK TABLES;"
passwd=`cat /appli/mysql/${instancename}/config/.mysql-passwd`
# Create a persistant mysql connection to database reading command from pipe
write_log INF "Create a persistant mysql connection to database reading command from pipe"
( tail -f $pipe & echo $! >&3 ) 3>/appli/mysql/${instancename}/run/tail_backup.pid | mysql --defaults-file=/appli/mysql/${instancename}/config/my.cnf -uroot -p$passwd 2>&1 >>${log_file} &
mysql_pid=$!
tail_pid=$(< /appli/mysql/${instancename}/run/tail_backup.pid)
write_log INF "PID des fils a supprimer en fin de programme : $mysql_pid $tail_pid"
ps -axf | grep $mysql_pid | grep -v grep >> ${log_file}
ps -axf | grep $tail_pid | grep -v grep >> ${log_file}
trap "kill -9 $mysql_pid $tail_pid; rm -f $pipe /appli/mysql/${instancename}/run/tail_backup.pid" EXIT

write_log INF "switch to read only database"
echo $backup_sql_start >> $pipe
test_rc $?

write_log INF "take snapshot"
cd /appli/mysql/${instancename}/
btrfs subvolume snapshot mysql-datafile/ data/mysql-datafile-$TH.dump 2>&1 >>${log_file}
test_rc $?

write_log INF "make snapshot read only"
btrfs property set -ts data/mysql-datafile-$TH.dump ro true 2>&1 >>${log_file}
test_rc $?


write_log INF "reswitch to read-write"
echo $backup_sql_stop >> $pipe
test_rc $?

write_log INF "quit"
kill $mysql_pid $tail_pid;
rm -f $pipe
trap "" EXIT
test_rc $?

cd /appli/mysql/${instancename}/data/
if [ $((` ls -tr | wc -l `)) -gt 24 ]
then
  ls -1 | head -n $((`ls -tr | wc -l` - 24 ))  | xargs btrfs subvolume delete
fi

# Test if a btrfs export is older than 1 days, if present delete it and export current backup to file
if test "`find /appli/mysql/${instancename}/backup-daily -type f -mtime +0`"
then
    write_log INF "A btrfs export is older than 24h on backup-daily directory, drop it"
    find /appli/mysql/${instancename}/backup-daily -type f -mtime +0 | xargs rm -rf
    test_rc $?
fi

if [ `ls /appli/mysql/${instancename}/backup-daily | wc -l` -lt 1 ]
then
    write_log INF "no backup present in backup-daily => export current backup to file"
    btrfs send -f /appli/mysql/${instancename}/backup-daily/${instancename}-${TH}_datafile.dump /appli/mysql/${instancename}/data/mysql-datafile-$TH.dump 2>&1 >>${log_file}
    RC=$?
    if [ "x$RC" != "x0" ]
    then
        rm /appli/mysql/${instancename}/backup-daily/${instancename}-${TH}_datafile.dump
    fi
    test_rc $RC
fi

write_log END "fin du backup"
mv ${log_file} ${log_file}_${TH}
exit 0