mysql备份脚本

这个脚本调用的是innobackupex备份mysql，可以自己设置全备的保留策略，保留策略内的全备和增量备份都放在同一个目录TAGXXXX下，分别为full_XXX和inc_XXX目录，自定义retention变量，超过retention的备份将新建一个TAGXXXX目录，此脚本相比网上的流传的那个更方便，通过简单的修改还能实现基于秒基本的增量复制~~

#!/bin/bash
#set -x

# 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
}

# Get the key value of input arguments format like '--args=value'.
function get_key_value()
{
    echo "$1" | sed 's/^--[a-zA-Z_-]*=//'
}
 
# Parse the input arguments and get the value of the input argument. 
function 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
      exit 0;;
    *)
      echo "Unknown option '$1'"
      exit 1;;
    esac
    shift
  done
}
 
#main

if [ $# -eq 0 ];then
      usage
      exit 0;
fi

# Call the parse_options function to parse the input arguments and initialisze env.
parse_options "$@"

#week day
TIMESTAMP=`date +%Y%m%d%H%M%S`
TIMEDAY=`date +%Y%m%d`
TIMEFULL=`date +%s`

#directory structer
BACK_HOME=${backupDir}/backup
LOGDIR=${BACK_HOME}/log
TAGDIR=${BACK_HOME}/TAG${TIMEDAY}
CMD=/usr/local/percona-xtrabackup-2.3.2-Linux-x86_64/bin/innobackupex
LOGFILE=${LOGDIR}/backup_${TIMESTAMP}.log
CURRENTFILE=${BACK_HOME}/currentfile
RETENTION=3


if [ ! -d "$BACK_HOME" ]; then
    mkdir -p $BACK_HOME
fi
 
if [ ! -f "$defaultFile" ]; then
    defaultFile=/etc/my.cnf
fi

if [ ! -d "${LOGDIR}" ]; then
    mkdir -p ${LOGDIR}
fi

if [ ! -f "$CURRENTFILE" ]; then
    touch $CURRENTFILE
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()
{

if [ ! -d "${TAGDIR}" ]; then
    mkdir -p ${TAGDIR}
fi

#create tagdir
echo $TAGDIR,$TIMEFULL > ${BACK_HOME}/currentfile
CHECKPOINTFILE=`cat ${BACK_HOME}/currentfile|awk -F, '{print $1}'`/checkpoint

if [ ! -f "${CHECKPOINTFILE}" ]; then
    touch ${CHECKPOINTFILE}
fi

echo "$CMD --defaults-file=$defaultFile --user=$mysqlUser --password=$mysqlPassword  \
      --no-timestamp $TAGDIR/full_${TIMESTAMP} \
     " >> ${LOGFILE}
     
$CMD --defaults-file=$defaultFile --user=$mysqlUser --password=$mysqlPassword  \
--no-timestamp $TAGDIR/full_${TIMESTAMP} 2>> ${LOGFILE}

grep last_lsn $TAGDIR/full_${TIMESTAMP}/xtrabackup_checkpoints|cut -b 12- > ${CHECKPOINTFILE}
echo "Current last lsn is : `grep last_lsn $TAGDIR/full_${TIMESTAMP}/xtrabackup_checkpoints|cut -b 12-`
" >>${LOGFILE}
   
}
 
function back_inc()
{
CURRENTDIR=`cat ${BACK_HOME}/currentfile |awk -F, '{print $1}'`
CHECKPOINTFILE=$CURRENTDIR/checkpoint
echo "$CMD --defaults-file=$defaultFile --user=$mysqlUser --password=$mysqlPassword \
      --no-timestamp --incremental  --incremental-lsn=`cat ${CHECKPOINTFILE}` ${CURRENTDIR}/inc_${TIMESTAMP} 
      "  >> ${LOGFILE}
 
$CMD --defaults-file=$defaultFile --user=$mysqlUser --password=$mysqlPassword \
  --no-timestamp --incremental --incremental-lsn=`cat ${CHECKPOINTFILE}` ${CURRENTDIR}/inc_${TIMESTAMP} 2>> ${LOGFILE}

grep last_lsn ${CURRENTDIR}/inc_${TIMESTAMP}/xtrabackup_checkpoints|cut -b 12- >${CHECKPOINTFILE}    
  echo "Current last lsn is : `grep last_lsn ${CURRENTDIR}/inc_${TIMESTAMP}/xtrabackup_checkpoints|cut -b 12-`
  " >>${LOGFILE}
}

FULLDAY=`cat ${CURRENTFILE}|awk -F, '{print $2}'`

if  [ ! -z "${FULLDAY}" ]
then
DAYMIS=`expr  $TIMEFULL - $FULLDAY`
DAYALL=`expr $DAYMIS / 86400`
fi

if  [ -z "${FULLDAY}" ] 
then   
back_full
elif [ "${DAYALL}" -ge "$RETENTION" ]
then
back_full
else
back_inc
fi

例如：

[root@mysql1 test2]# ./1.sh --backup-dir=/data --defaults-file=/usr/local/mysql/my.cnf --host=127.0.0.1 --port=3306 --user=root --password=root 

备份完成后在/data/backup目录下为备份目录

[root@mysql1 backup]# ls
currentfile  log  TAG20151109  TAG20151113  TAG20151120

每个TAG打头的目录里面都有一个完整的全备和N个增量备份，当执行的时间超过保留的策略的时候，会新建一个TAGXXXX目录存放新的全备和增量备份，log里面存放的是所有的备份日志。

[root@mysql1 backup]# ls -l
total 20
-rw-r--r-- 1 root root   36 Nov 20 00:07 currentfile
drwxr-xr-x 2 root root 4096 Nov 20 00:07 log
drwxr-xr-x 5 root root 4096 Nov 13 00:00 TAG20151109
drwxr-xr-x 4 root root 4096 Nov 13 00:01 TAG20151113
drwxr-xr-x 5 root root 4096 Nov 20 00:07 TAG20151120