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 ""