今天我们继续来讲Mysql方面的知识。

一、安装xtrabackup工具
安装方法见:xtrabackup安装
二、配置备份脚本

1.拷贝mysql_backup_shell到mysql业务服务器目录,比如:/data/backup_shell/mysql_backup_shell
2.进入拷贝后mysql_backup_shell所在目录, 然后执行chmod 777 mysql_backup.sh

3.修改backup.conf
需要修改的项:
mysql_conf_file=/usr/local/meb/my.cnf.3309 #mysql cnf文件路径
user=root #mysql连接用户名
port=3309 #端口号
host=127.0.0.1 #mysql ip
password= #密码,可以为空
socket=/data0/mysql/3309/mysql.sock #mysql socket
backup_dir=/root/workspace/mysqlbackup/ #全备份后文件存放目录
full_backup_month_day=29 #全备份在每月哪天进行, 29代表本月29号进行全备份
is_tar_full=1 #取值为0或1,为1表示会压缩全备份目录,并且删除全备份目录,而保留全备份目录压缩后tar.gz文件。 取0不压缩,保留全备份目录。
PS: 除上面这些项,其他项一般不需要修改。

4.利用crontab配置定时任务(每天23:59开始备份)

#crontab -e
59 23 * * * YourPath/mysql_backup_shell/mysql_backup.sh > /tmp/monitor.log 2>&1

YourPath指解压缩后目录存放的路径,比如/data/backup_shell

5.命令行运行脚本

第一种运行方式
> ./mysql_backup.sh
根据backup.conf文件里面full_backup_month_day值, 和当天日期比较, 如果相同执行全备份;如果不相同执行增量备份。

第二种运行方式
mysql_backup.sh提供一个参数-t,方便测试和出错后手动启动备份使用。
./mysql_backup.sh -t full #会进行全备份
./mysql_backup.sh -t inc #会进行增量份

说明:
1.每个月执行一次全备份,具体哪天由backup.conf文件里面full_backup_month_day项控制。
2.每天23:59执行增量备份
3.每次全备份前会删除上个月全备份和增量备份的文件, 换句话:只会持续保持一个月的数据内容。
4.增量备份基于上次的增量备份进行, 除全备以后第一次增量备份, 其余都基于上次增量备份目录进行。

三、脚本

# xtrabackup backup shell
# Author : zouqingyun
# Date   : 2016-03-23
source /etc/profile

work_dir=`dirname $0`
cd $work_dir

#获取配置文件中配置项的值
conf_file=$work_dir/backup.conf

user=`sed '/^user=/!d;s/.*=//' $conf_file`
password=`sed '/^password=/!d;s/.*=//' $conf_file`
mysql_conf_file=`sed '/^mysql_conf_file=/!d;s/.*=//' $conf_file`
port=`sed '/^port=/!d;s/.*=//' $conf_file`
# mysql host
host=`sed '/^host=/!d;s/.*=//' $conf_file`
# mysql socket
mysql_socket=`sed '/^socket=/!d;s/.*=//' $conf_file`
backup_dir=`sed '/^backup_dir=/!d;s/.*=//' $conf_file`

full_backup_month_day=`sed '/^full_backup_month_day=/!d;s/.*=//' $conf_file`
full_backup_prefix=`sed '/^full_backup_prefix=/!d;s/.*=//' $conf_file`
increment_prefix=`sed '/^increment_prefix=/!d;s/.*=//' $conf_file`
mysql_conf_file=`sed '/^mysql_conf_file=/!d;s/.*=//' $conf_file`
error_log_name=`sed '/^error_log=/!d;s/.*=//' $conf_file`
index_file_name=`sed '/^index_file=/!d;s/.*=//' $conf_file`
is_tar_full=`sed '/^is_tar_full=/!d;s/.*=//' $conf_file`

#获取当前时间
backup_date=`date +%F`
backup_time=`date +%H-%M-%S`
backup_month_day=`date +%d`

#获取日志和错误文件所在目录
log_dir=$work_dir/log
var_dir=$work_dir/var

#日志和错误文件路径
error_log=$log_dir/$error_log_name
index_file=$var_dir/$index_file_name

mkdir -p $backup_dir
mkdir -p $log_dir
mkdir -p $var_dir

#调用xtrabckup进行全量备份
function full_backup() {
  backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}

  echo "backup_folder: $backup_folder"
  mkdir -p $backup_dir/$backup_folder

  echo "xtrabackup --defaults-file=$mysql_conf_file --user=$user --password=$password --port=$port --socket=$mysql_socket --backup --no-lock --target-dir=$backup_dir/$backup_folder > $log_dir/${backup_folder}.log"
  xtrabackup \
  --defaults-file=$mysql_conf_file \
   --user=$user \
   --password=$password \
   --port=$port \
    --socket=$mysql_socket \
    --backup \
    --no-lock \
    --target-dir=$backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1
  return $?
}

#调用xtrabckup进行增量备份
function increment_backup() {
  backup_folder=${increment_prefix}_${backup_date}_${backup_time}
  echo "index_file : $index_file"
  incr_base_folder=`sed -n '$p' $index_file | \
                   awk -F '[, {}]*' '{print $3}' | \
                   awk -F ':' '{print $2}'`
  echo "backup_folder: $backup_folder"
  echo "incr_base_folder: $incr_base_folder"

  mkdir -p $backup_dir/$backup_folder
  echo "xtrabackup --defaults-file=$mysql_conf_file --user=$user --password=$password --port=$port --socket=$mysql_socket --backup --no-lock --target-dir=$backup_dir/$backup_folder --incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log"

  xtrabackup \
    --defaults-file=$mysql_conf_file \
    --user=$user \
    --password=$password \
    --port=$port \
    --socket=$mysql_socket \
    --backup \
    --no-lock \
    --target-dir=$backup_dir/$backup_folder \
    --incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log 2>&1
  return $?
}
#根据索引文件记录的恢复记录, 删除备份目录
function delete_before_backup() {
  if [ ! -n "`cat $index_file`" ]; then
    return
  fi

  cat $index_file | awk -F '[, {}]*' '{print $3}' | \
    awk -v backup_dir=$backup_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s\n", backup_dir, $2)}}' | \
    /bin/bash
  
  cat $index_file | awk -F '[, {}]*' '{print $3}' | \
    awk -v log_dir=$log_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s.log\n", log_dir, $2)}}' | \
    /bin/bash
}

#备份索引文件 
function backup_index_file() {
  cp $index_file ${index_file}_$(date -d "1 day ago" +%F)
}

function send_index_file_to_remote() {
  echo 'send index file ok'
}

#写入索引日志
function append_index_to_file() {
  echo "{week_day:$backup_month_day, \
         dir:${1}_${backup_date}_${backup_time}, \
         type:${1}, \
         date:${backup_date}}" >> $index_file
}

#写入解压日志
function append_tar_index_to_file() {
  echo "{week_day:$backup_month_day, \
         dir:${1}_${backup_date}_${backup_time}.tar.gz, \
         type:${1}, \
         date:${backup_date}}" >> $index_file
}

#记录错误日志
function logging_backup_err() {
  echo "{week_day:$backup_month_day, \
         dir:${1}_${backup_date}_${backup_time}, \
         type:${1}, \
         date:${backup_date}}" >> $error_log
}

#清除索引文件
function purge_index_from_file() {
  > $index_file
}

#清除错误日志
function purge_err_log() {
  > $error_log
}

#压缩全备份目录
function tar_backup_file() {
  tar_file=${1}_${backup_date}_${backup_time}

  tar -C $backup_dir -zcvf $backup_dir/${tar_file}.tar.gz ${tar_file} 
  echo "tar $1 ok"
}

function send_backup_to_remote() {
    
  echo "send $1 remote ok"
}
 #获取恢复的类型,返回类型:0:full, 1:inc
# 0:full, 1:incr
function get_backup_type() {
  full_backup_month_day=`sed '/^full_backup_month_day=/!d;s/.*=//' $conf_file`
  backup_type=0
  if [ "${full_backup_month_day}_x" == "${backup_month_day}_x" ]; then
    backup_type=0
  else
    backup_type=1
  fi
  if [ ! -n "`cat $index_file`" ]; then
    backup_type=0
  fi
  return $backup_type
}

#检测配置文件各项数据合法性
function test_conf_file() {
  if [ ! -n "$user" ]; then echo 'fail: configure file user not set'; exit 2; fi
  #if [ ! -n "$password" ]; then echo 'fail: configure file password not set'; exit 2; fi
  #if [ ! -n "$host" ]; then echo 'fail: configure file host not set'; exit 2; fi
  if [ ! -n "$mysql_socket" ]; then echo 'fail: configure file mysql_socket not set'; exit 2; fi
  #if [ ! -n "$port" ]; then echo 'fail: configure file port not set'; exit 2; fi
  if [ ! -n "$backup_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi
  if [ ! -n "$full_backup_month_day" ]; then echo 'fail: configure file full_backup_month_day not set'; exit 2; fi
  if [ ! -n "$full_backup_prefix" ]; then echo 'fail: configure file full_backup_prefix not set'; exit 2; fi
  if [ ! -n "$increment_prefix" ]; then echo 'fail: configure file increment_prefix not set'; exit 2; fi
  if [ ! -n "$mysql_conf_file" ]; then echo 'fail: configure file mysql_conf_file not set'; exit 2; fi
  if [ ! -n "$error_log" ]; then echo 'fail: configure file error_log not set'; exit 2; fi
  if [ ! -n "$index_file" ]; then echo 'fail: configure file index_file not set'; exit 2; fi
  if [ ! -n "$is_tar_full" ]; then echo 'fail: configure file is_tar_full not set'; exit 2; fi
}

#运行主函数
function run() {
  test_conf_file
  backup_type=-1
  #解析命令行参数
  while getopts "t:" arg
  do
    case $arg in
        t)
            if [ "$OPTARG" = "$full_backup_prefix" ]; then
                backup_type=0
            elif [ "$OPTARG" = "$increment_prefix" ]; then
                backup_type=1
            fi
            ;;
        ?)
            echo "unkonw argument"
        exit 1
        ;;
        esac
  done
  if [ "$backup_type" -eq -1 ]; then
    get_backup_type
    backup_type=$?    
  fi
  echo "backup_type: $backup_type"

  case $backup_type in
    0)#全量备份
      echo '----------------------full backup start----------------'
      echo 'delete_before_backup start'
      delete_before_backup
      echo 'call xtrabackup to full backup start'
      full_backup 

      backup_ok=$?
      if [ 0 -eq "$backup_ok" ]; then

        if [ 1 -eq "$is_tar_full" ]; then
          tar_backup_file $full_backup_prefix
        fi

        #send_backup_to_remote $full_backup_prefix
        echo 'backup_index_file start'
        backup_index_file

        # send_index_file_to_remote
        echo 'purge_index_from_file start'
        purge_index_from_file
        
        echo 'append_index_to_file start'
        if [ 1 -eq "$is_tar_full" ]; then
          append_tar_index_to_file $full_backup_prefix
        fi

        append_index_to_file $full_backup_prefix

        echo 'call xtrabackup to increment backup start'
        increment_backup
        backup_ok=$?
        if [ 0 -eq "$backup_ok" ]; then
          # tar_backup_file $increment_prefix
          # send_backup_to_remote $increment_prefix
          echo 'append_index_to_file start'
          append_index_to_file $increment_prefix
          rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}
        else
          rm -rf ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}
          logging_backup_err $increment_prefix
        fi
      else
        rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}
        logging_backup_err $full_backup_prefix
      fi
      ;;
    1)#增量备份
      echo '----------------------incr backup start----------------'
      
      echo 'call xtrabackup to increment backup start'
      increment_backup
      backup_ok=$?
      if [ 0 -eq "$backup_ok" ]; then
        # tar_backup_file $increment_prefix

        # send_backup_to_remote $increment_prefix
        echo 'append_index_to_file start'
        append_index_to_file $increment_prefix
      else
        rm -rf ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}

        logging_backup_err $increment_prefix
      fi
      ;;
  esac
}

run $@

本文出自 “散人” 博客,请务必保留此出处http://zouqingyun.blog.51cto.com/782246/1842390

因为水平有限,难免有疏忽或者不准确的地方,希望大家能够直接指出来,我会及时改正。一切为了知识的分享。

后续会有更多的精彩的内容分享给大家。