基于Innobackupex的MySQL备份脚本

Innobackupex是Xtrabackup的一部分，其实质也是调用xtrabackup。主要的不同是Xtrabackup除了支持innodb引擎外还支持xtradb引擎。本文主要封装了Innobackupex到shell脚本进行定期备份，供大家参考。1、脚本描述
a、支持增量备份以及全备
b、需要传递到备份脚本（如备份路径，连接相关参数等）
c、基于周日，周三的全量备份，其他增量备份
d、可根据需要调整脚本，比如压缩备份的文件夹以及rsync等2、脚本内容################################################################################
# File : innobk.sh #
# Author : Leshami #
# Blog : http://www.linuxidc.com #
# Date : 20141113 #
# Description : #
# The script will call innobackupex to #
# take a full or increment backup for mysql db. #
# Currently it will take follow principal to backup: #
# Sun,Wend take full backup. #
# Mon,Tue,Thur,Fri,Sat take incremental backup. #
# #
# Usage Example: #
# innobk.sh --help|-？ #
# innobk.sh --backup-dir=/dbbak --defaults-file=/inst3606/my3606.cnf #
# --host=127.0.0.1 --port=3606 --user=xxx --password=xxx #
# #
################################################################################
# Change History: #
# -------------------------------------------------- #
# Init Development Leshami 2014-11-13 #
#################################################################################！/bin/bash
#set -x# Get the key value of input arguments format like "--args=value".
function get_key_value（）
{
echo "$1" | sed "s/^--[a-zA-Z_-]*=//"
}# Usage will be helpful when you need to input the valid arguments.
function usage（）
{
cat <<EOF
Usage: $0 [configure-options]
-？, --help Show this help message.
--backup-dir=<> Set backup directory
--defaults-file=[] Set mysql configuration file directory
--host=<> Set mysql host
--port=<> Set mysql port
--user=<> Set mysql user name
--password=<> Set mysql user password
EOF
}# Parse the input arguments and get the value of the input argument.
if [ $# -eq 0 ];then
usage
# print_default
exit 0;
fifunction parse_options（）
{
while test $# -gt 0
do
case "$1" in
--backup-dir=*）
backupDir=`get_key_value "$1"`;;
--defaults-file=*）
defaultFile=`get_key_value "$1"`;;
--host=*）
Host=`get_key_value "$1"`;;
--port=*）
mysqlPort=`get_key_value "$1"`;;
--user=*）
mysqlUser=`get_key_value "$1"`;;
--password=*）
mysqlPassword=`get_key_value "$1"`;;
-？ | --help ）
usage
# print_default
exit 0;;
*）
echo "Unknown option "$1""
exit 1;;
esac
shift
done
}# Call the parse_options function to parse the input arguments and initialisze env.
parse_options "$@"
physicalBackupDir=${backupDir}/physical
logDir=${backupDir}/log
checkPointDir=${backupDir}/checkpoint
cmdInno=/usr/bin/innobackupex
sock=/tmp/mysql.sockday=`date +%w`
lastday=`date -d "1 days ago" +%Y%m%d`
dt=`date +%Y%m%d`
ts=`date +%Y%m%d%H%M%S`
logFile=${backupDir}/log/innobak_${ts}.logif [ "${day}" -eq 0 ] || [ "${day}" -eq 3 ];then
if [ ！ -d "$physicalBackupDir/$dt" ];then
echo "mkdir -p $physicalBackupDir/$dt"
mkdir -p $physicalBackupDir/$dt
fi
fiif [ -z "$defaultFile" ]; then
defaultFile=/etc/my.cnf
fiif [ ！ -d "${logDir}" ]; then
mkdir -p ${logDir}
fiif [ ！ -d "${checkPointDir}" ]; then
mkdir -p ${checkPointDir}
fi echo "Start innobackup at `date`." >>${logFile}
echo "Current defaults file is : ${defaultFile}" >>${logFile}
echo "Current host is : ${Host}" >>${logFile}
echo "Current port is : ${mysqlPort}" >>${logFile}
echo "Current mysql user is : ${mysqlUser}" >>${logFile}
echo "Current password is : ${mysqlPassword}"
echo "Current log directory is : ${logDir}" >>${logFile}
echo "Current log file is : ${logFile}" >>${logFile}# Define backup function for full and incremental backup type.
function back_full（）
{
echo "$cmdInno --user=$mysqlUser --password=$mysqlPassword --no-timestamp
--defaults-file=$defaultFile $physicalBackupDir/$dt/base_${dt}
--socket=$sock 2> ${logDir}/bak_$ts.log" >>${logFile} $cmdInno --user=$mysqlUser --password=$mysqlPassword --no-timestamp
--defaults-file=$defaultFile $physicalBackupDir/$dt/base_$dt --socket=$sock 2> ${logDir}/bak_${ts}.log
grep last_lsn $physicalBackupDir/$dt/base_$dt/xtrabackup_checkpoints|cut -b 12- >$checkPointDir/ckp_${dt}
}function back_inc（）
{
echo " $cmdInno --user=$mysqlUser --password=$mysqlPassword --socket=$sock --no-timestamp
--defaults-file=$defaultFile --incremental $basedir/inc_$dt
--incremental-lsn=`cat $checkPointDir/ckp_$lastday` 2>${logDir}/bak_${ts}.log " >>${logFile} $cmdInno --user=$mysqlUser --password=$mysqlPassword --port=${mysqlPort} --socket=$sock --no-timestamp
--defaults-file=$defaultFile --incremental $basedir/inc_$dt
--incremental-lsn=`cat $checkPointDir/ckp_$lastday` 2>${logDir}/bak_${ts}.log
grep last_lsn $basedir/inc_$dt/xtrabackup_checkpoints|cut -b 12- >$checkPointDir/ckp_$dt
}case $day in
0）
# Sunday Full backup
back_full
;;
1）
# Monday Relatively Sunday"s incremental backup
basedir=$physicalBackupDir/`date -d "1 days ago" +%Y%m%d`
back_inc
;;
2）
# Tuesday Compared with Monday"s incremental backup
basedir=$physicalBackupDir/`date -d "2 days ago" +%Y%m%d`
back_inc
;;
3）
# Wednesday Full backup
back_full
;;
4）
# Thursday Relatively Wednesday"s incremental backup
basedir=$physicalBackupDir/`date -d "1 days ago" +%Y%m%d`
back_inc
;;
5）
# Friday Compared with Thursday"s incremental backup
basedir=$physicalBackupDir/`date -d "2 days ago" +%Y%m%d`
back_inc
;;
6）
# Saturday Compared with Friday"s incremental backup
basedir=$physicalBackupDir/`date -d "3 days ago" +%Y%m%d`
back_inc
;;
esac# Check backup log ,remove history logfile and bacupset.
retention=5find ${physicalBackupDir} -type f -mtime +$retention -exec rm {} ;
find ${logDir} -type f -mtime +$retention -exec rm {} ;# Send mail for backup result.
echo "" >>${logFile}
echo "Below is detail log for current innobackup.">>${logFile}
cat ${logDir}/bak_${ts}.log >>${logFile}
mailadd="jack@12306.cn,ww@12306.cn"if [ -e "${logFile}" ]; then
status=`grep -i "innobackupex: completed OK！" ${logFile}`
if [ -n "${status}" ]; then
cat ${logFile} |mutt -s "Successful backup for MySQL hotbackup on `hostname`." $mailadd
else
cat ${logFile} |mutt -s "Failed backup for MySQl hotbackup on `hostname`." $mailadd
fi
else
echo "The hotbackup logfile was not found on `hostname`." |
mutt -s "Failed backup for MySQl hotbackup on `hostname`." $mailadd
fi

exit3、调用示例SHELL> more call_innobk.sh
#！/bin/bash
/db_scripts/innobk.sh --backup-dir=/data/backup --host=127.0.0.1 --port=3306 --user=innobk --password=InnoBKSHELL> crontab -l
0 3 * * * /db_scripts/call_innobk.shSHELL> cd /data/backup
SHELL> ls
checkpoint log physical
SHELL> cd physical/
SHELL> ls
20141228 20141231 20150104 20150107
SHELL> cd 20150107
SHELL> ls -hltr
total 16K
drwxr-xr-x 9 root root 4.0K Jan 7 03:05 base_20150107 #全备
drwxr-xr-x 9 root root 4.0K Jan 8 03:04 inc_20150108 #增备
drwxr-xr-x 9 root root 4.0K Jan 9 03:03 inc_20150109 #增备
drwxr-xr-x 9 root root 4.0K Jan 10 03:03 inc_20150110 #增备MySQL管理之使用XtraBackup进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htmMySQL开源备份工具Xtrabackup备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htmMySQL Xtrabackup备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htm用XtraBackup实现MySQL的主从复制快速部署【主不锁表】 http://www.linuxidc.com/Linux/2012-10/71919p2.htm安装和使用 Percona 推出的 Xtrabackup 备份 MySQL http://www.linuxidc.com/Linux/2011-10/44451.htmXtraBackup 的详细介绍：请点这里
XtraBackup 的下载地址：请点这里本文永久更新链接地址