xtrabackup备份与恢复的脚本

xtrabackup备份与恢复的脚本

01 02 * * * cd /home/xtrabackup_mysql && ./bk_mysql.sh fullbackup >> log/run.log 2>&1 &

#!/bin/bash

echo "==============new backup================="
echo "===============arg:[$@]=================="
date

export LD_LIBRARY_PATH=/home/xtrabackup_mysql/bin
export PATH=/home/xtrabackup_mysql/bin:$PATH

dir=`pwd`

mysqldatadir=/home/mysql/data/
declare -A dbconf
declare -A authconf
declare -A partial_bk_timeconf
declare -A full_bk_timeconf
declare -A slave_argconf 

dbconf=(["host74"]="a1"
        ["host75"]="a1"
        ["host170"]="mallcenter mallcenter_stats gamecenter"
    )

authconf=(["host74"]="--defaults-file=/etc/mysql/my.cnf --host=192.168.1.74 --port=3306 --user=root --password=*****"
          ["host75"]="--defaults-file=/etc/mysql/my.cnf --host=192.168.1.75 --port=3306 --user=root --password=*****"
          ["host170"]="--defaults-file=/etc/mysql/my.cnf --host=localhost --port=3306 --user=root --password=*****"
    )

slave_argconf=(["host74"]=""
               ["host75"]="--slave-info --safe-slave-backup"
               ["host170"]="--slave-info --safe-slave-backup"
    )

full_bk_timeconf=(["host74"]=4320
                  ["host75"]=4320
                  ["host170"]=2880
    )

partial_bk_timeconf=(["host74"]=300 
                     ["host75"]=300 
                     ["host170"]=300 
    )


db=${dbconf["`hostname`"]}
auth=${authconf["`hostname`"]}
slave_arg=${slave_argconf["`hostname`"]}
partial_bk_time=${partial_bk_timeconf["`hostname`"]}
full_bk_time=${full_bk_timeconf["`hostname`"]}

time=`date '+%Y%m%d%H%M'`
outfile="${dir}/log/run.log.${time}"
redirectlog=" >> ${outfile} 2>&1"
#cd $dir

checkResultIsOkDie(){
	resstr=`tail -1 $outfile | grep "completed OK"`
	if [ "$resstr" != "" ];then
        echo "checkResultIsOkDie success!"
		return ;
	else
		errmsg="checkResultIsOkDie failed. resstr:"`tail -1 $outfile`
        echo "mysql_backup_error:$errmsg"
        #sendSms "mysql_backup_error:$errmsg"
		exit
	fi
}

fullbackup(){
#对数据库当前的数据生成一份全量的备份到fullbackup/base
    #删除三天以前的全量备份数据
    find   ${dir}/fullbackup/ -name "base_backtime_*" -type d -mmin +${full_bk_time} | xargs rm -rf
    mv ${dir}/fullbackup/base ${dir}/fullbackup/base_backtime_${time}

    innobackupex ${auth} ${slave_arg} --no-timestamp --database="${db}" ${dir}/fullbackup/base  >> $outfile 2>&1
	checkResultIsOkDie

	#同步到远程机器上面
	#rsync -avzP --bwlimit=50000 ${dir}/fullbackup/base root@192.168.1.170:${dir}/fullbackup/
	#ssh root@192.168.1.170 "innobackupex ${auth} -redo-only --apply-log --use-memory=4G ${dir}/fullbackup/base" 

    #全量备份完成后，做一次apply，以备后续随时将增量数据 -redo-only --apply-log进来
    #innobackupex ${auth} -redo-only --apply-log --use-memory=4G ${dir}/fullbackup/base   >> $outfile 2>&1
	#checkResultIsOkDie
}

full_recovery(){
#将fullbackup/参数1 里面的全量备份数据
    if [ ! -d "$1" ]; then
        echo "full backup dir does not existes!:$1"
        exit
    fi
	rm ${dir}/fullbackup/base_recovery -rf
	cp ${dir}/$1 ${dir}/fullbackup/base_recovery -rpf

    innobackupex ${auth} --apply-log --use-memory=4G ${dir}/fullbackup/base_recovery   >> $outfile 2>&1
	checkResultIsOkDie

    /etc/init.d/mysql stop
    
	innobackupex ${auth} --database="${db}" --copy-back ${dir}/fullbackup/base_recovery  >> $outfile 2>&1
	checkResultIsOkDie

	chown -R mysql:mysql ${mysqldatadir}
    /etc/init.d/mysql start
}

partialback(){
#定时将增量数据从数据库中备份出来到partial目录

    find ${dir}/partialbackup/ -name "partial_*" -type d -mmin +${partial_bk_time} | xargs rm -rf

    innobackupex ${auth} ${slave_arg} --no-timestamp --database="${db}" --incremental --incremental-basedir=${dir}/fullbackup/base  ${dir}/partialbackup/partial_${time}  >> $outfile 2>&1
	checkResultIsOkDie

	#同步到远程机器上面
	#rm ${dir}/partialbackup/partial_rsync 
	#cp ${dir}/partialbackup/partial ${dir}/partialbackup/partial_rsync -rpf 
	#rsync -avzP --bwlimit=50000 ${dir}/partialbackup/partial_rsync root@192.168.1.170:${dir}/partialbackup/
	#ssh root@192.168.1.170 "innobackupex ${auth} --slave-info -redo-only --safe-slave-backup --apply-log --use-memory=4G ${dir}/fullbackup/base --incremental-dir=${dir}/partialbackup/partial"

}

partial_recovery(){
#将fullbackup/base里面的全量备份数据，或者说增量备份合并在一起的全量数据导到数据库里面,需要重启数据库的
    if [ ! -d "${dir}/$1" ]; then
        echo "partial backup full dir does not existes!:$1"
        exit 
    fi
    if [ ! -d "${dir}/$2" ]; then
        echo "partial backup partial dir does not existes!:$2"
        exit
    fi
    #以下拷贝到临时目录
	rm ${dir}/fullbackup/base_recovery -rf
	cp ${dir}/$1 ${dir}/fullbackup/base_recovery -rpf

	rm ${dir}/partialbackup/partial_recovery
	cp ${dir}/$2 ${dir}/partialbackup/partial_recovery -rpf

    innobackupex ${auth} -redo-only --apply-log --use-memory=4G ${dir}/fullbackup/base_recovery   >> $outfile 2>&1
	checkResultIsOkDie

    #最后一个增量备份不需要redo-only了
    innobackupex ${auth} --slave-info --safe-slave-backup --apply-log --use-memory=4G ${dir}/fullbackup/base_recovery --incremental-dir=${dir}/partialbackup/partial_recovery  >> $outfile 2>&1
	checkResultIsOkDie

    #拷贝文件到mysql-data目录，准备恢复
    /etc/init.d/mysql stop
    
	innobackupex ${auth} --database="${db}" --copy-back ${dir}/fullbackup/base_recovery  >> $outfile 2>&1
	checkResultIsOkDie

	chown -R mysql:mysql ${mysqldatadir}
    /etc/init.d/mysql start
}

#sendSms(){
#    echo "sendSms , msg:$1 ";
#    errmsg="db_backup_failed"
#    wget -O - "http://ws.montnets.com:9002/MWGate/wmgw.asmx/MongateCsSpSendSmsNew?userId=J00275&password=187523&pszMobis=15110248624&pszMsg=[`hostname`]Hi guys,'$errmsg'&iMobiCount=1&pszSubPort=1065712038002984"
#}


bash ./checkdiskisfull.sh

echo "begin $1 of databases" ;
case "${1:-''}" in
  'fullbackup')
    fullbackup ;
	;;

  'full_recovery')
    echo usage:bk_mysql2.sh full_recovery base_20130707
    full_recovery $2;
	;;

  'partialback')
    partialback ;
	;;

  'partial_recovery')
    echo usage:bk_mysql2.sh partial_recovery base_20130707 partial_2013030709
    partial_recovery $2 $3;
	;;

  *)
	echo "Usage: $SELF fullbackup | full_recovery fulldir | partialback | partial_recovery fulldir partialdir"
	exit 1
	;;
esac


date
echo "==============backup finished================="
echo ""