innobackupex自动备份脚本（增量备份，自动压缩）

最新推荐文章于 2023-09-14 15:52:55 发布

原创最新推荐文章于 2023-09-14 15:52:55 发布 · 547 阅读

0 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

57 篇文章

订阅专栏

这是一个关于如何设置innobackupex自动备份脚本的说明，脚本策略为每天执行一次，进行全备与增量备份的交替。每隔七天进行一次全备份，其余六天做增量备份。备份完成后，计划将对已完成的备份文件进行压缩和管理。

#说明：脚本执行策略为每天执行一次，执行前需要先建立config文件，并在config文件
#中添加
#backup_full=
#backup_pre_name=

#mysql 目录下创建 full incr 目录
#即可，注意路径。
#备份策略，每七天一个循环，第一天为全备份，第二天至第六天为增量备份。
#后续会增加已备份文件压缩转移定期删除部分

增量为备份所有库，全量为所有库

#!/bin/bash

backup_full_dir="/backup/mysql/full/"
backup_incr_dir="/backup/mysql/incr/"
backup_path="/backup/mysql/"
backup_log_dir="/backup/log/"
backup_config_dir="/backup/config/"
backup_gztar="/backup/tar.gzdb/"

backup_config="config"
db_conf="/etc/my.cnf"
#root="root"
root="root"
#pwd="Qwer1234!@#$"
pwd="Qwer1234!@#$"
db_backup_day=7

starttime=`date +'%Y-%m-%d %H:%M:%S'`
time="$(date +"mysql-%Y-%m-%d")"

source /backup/config/config

old_backup_file=${backup_full}  #获取全量备份文件名称
_day=`echo ${old_backup_file} | awk 'BEGIN{FS="-"}{print $2"-"$3"-"$4}'`
_old_day=$(date +%s -d "$_day")
_new_day=$(date +%s )

let num=$(((${_new_day}-${_old_day})/86400))  #计算当前时间与全量备份时间差值

Ym_week=$(date +'%Y-%m')/$(date +'%V')  #年-月/本年第几周

backup_full=${time}
tar_gz_full_dir=`date -d "${db_backup_day} days ago" +"mysql-%Y-%m-%d"`
tar_gz_incr_dir=`date -d "1 days ago" +"mysql-%Y-%m-%d"`

if [ -z ${old_backup_file} ] || [ $num -ge ${db_backup_day} ]; then
     #全备
     echo '全备开始'

     if [ -d ${backup_full_dir}${tar_gz_full_dir} ]; then

          if [ ! -d ${backup_gztar}${Ym_week} ]; then
                mkdir -p ${backup_gztar}${Ym_week}
          fi

          su - root -c "tar -czPf ${backup_gztar}${Ym_week}/${tar_gz_full_dir}.tar.gz -C ${backup_full_dir} ."


         if [ $? -eq 0 ]; then
            su - root -c  "rm -rf ${backup_full_dir}${tar_gz_full_dir}"
            echo "`date "+%Y-%m-%d %H:%M:%S"` 全备压缩目录成功  ${backup_gztar}${Ym_week}/${tar_gz_full_dir}.tar.gz  删除目录 ${backup_full_dir}${tar_gz_full_dir}"  >>${backup_log_dir}"/tar.log" 2>&1

         else
            echo "`date "+%Y-%m-%d %H:%M:%S"` 全备压缩目录失败  ${backup_gztar}${Ym_week}/${tar_gz_full_dir}" >>${backup_log_dir}"/tar.log" 2>&1
         fi
     fi

    innobackupex --defaults-file=${db_conf} --user=${root} --password=${pwd} --no-timestamp --no-lock ${backup_full_dir}${backup_full}/ >> ${backup_log_dir}/${backup_full}."log" 2>&1

      endtime=`date +'%Y-%m-%d %H:%M:%S'`
      start_seconds=$(date --date="$starttime" +%s);
      end_seconds=$(date --date="$endtime" +%s);

      if [ $? -eq 0 ]; then
          echo "`date "+%Y-%m-%d %H:%M:%S"` 本次运行时间：$((end_seconds-start_seconds))[S] ${backup_full} 全备已完成 !!!" >> ${backup_log_dir}"/backup_db.log"

      else
          echo "`date "+%Y-%m-%d %H:%M:%S"` 本次运行时间：$((end_seconds-start_seconds))[S] ${backup_full} 全备失败 !!!" >> ${backup_log_dir}"/backup_db.log"

      fi

    echo "backup_full=${backup_full}" > ${backup_config_dir}${backup_config}
    echo "backup_pre_name=full/${backup_full}" >> ${backup_config_dir}${backup_config}
    echo '全备结束'

else

  echo '增量备份开始'

    innobackupex --defaults-file=${db_conf} --user=${root} --password=${pwd} --no-timestamp --no-lock --incremental ${backup_incr_dir}${backup_full}/ --incremental-basedir=${backup_path}${backup_pre_name}/  >> ${backup_log_dir}/${backup_full}."log" 2>&1

    if [ $? -eq 0 ]; then
          echo "`date "+%Y-%m-%d %H:%M:%S"` 本次运行时间：$((end_seconds-start_seconds))[S] ${backup_full} 增备已完成 !!!" >> ${backup_log_dir}"/backup_db.log"

      else
          echo "`date "+%Y-%m-%d %H:%M:%S"` 本次运行时间：$((end_seconds-start_seconds))[S] ${backup_full} 增备失败 !!!" >> ${backup_log_dir}"/backup_db.log"

    fi


    if [ -d ${backup_incr_dir}${tar_gz_incr_dir} ]; then

          if [ ! -d ${backup_gztar}${Ym_week} ]; then
                mkdir -p ${backup_gztar}${Ym_week}
          fi

          su - root -c "tar -czPf ${backup_gztar}${Ym_week}/${tar_gz_incr_dir}_incr.tar.gz -C ${backup_incr_dir} ."

         if [ $? -eq 0 ]; then
            su - root -c  "rm -rf ${backup_incr_dir}${tar_gz_incr_dir}"
            echo "`date "+%Y-%m-%d %H:%M:%S"` 增备压缩目录成功  ${backup_gztar}${Ym_week}/${backup_incr_dir}" >>${backup_log_dir}"/tar.log" 2>&1
         else
            echo "`date "+%Y-%m-%d %H:%M:%S"` 增备压缩目录失败  ${backup_gztar}${Ym_week}/${backup_incr_dir}" >>${backup_log_dir}"/tar.log" 2>&1
         fi
     fi

   echo "backup_full=${backup_full}" > ${backup_config_dir}${backup_config}
   echo "backup_pre_name=incr/${backup_full}" >> ${backup_config_dir}${backup_config}
   echo '增量备份结束'
fi