MySQL增量备份脚本和异地备份脚本【Shell】

最新推荐文章于 2023-11-23 11:31:28 发布

oEleven123456789

最新推荐文章于 2023-11-23 11:31:28 发布

阅读量994

点赞数

分类专栏： MySQL 文章标签： shell 备份异地备份

本文链接：https://blog.csdn.net/oEleven123456789/article/details/51853410

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

增量备份脚本

#!/bin/bash
## Usage():  ./backup_MySQL.sh 
## Auth: John Liu 2015-07-08 modified: 2016-06-29

backup_dir=/backup
full_backup_dir=$backup_dir/full
incre_backup_dir=$backup_dir/incr
# Backup files retention
retention=3
threads=10
cnf_file=/app/mysql/my.cnf
port=3306
maillist="liuj@abc.com"
remote_host="192.168.1.1"
remote_dir=/app/dbbackup/db05
remote_port=22

bak_date=`date "+%Y%m%d"`
## Week day
weekday=`date "+%w"`
#weekday=$1
#interval=`expr $weekday - 1`
#monday=`date -d "-$interval day" "+%Y%m%d"`
yestoday=`date -d "-1 day" "+%Y%m%d"`

log_dir=$backup_dir/log
logfile=$log_dir/xtrabackup_$bak_date.log


create_dir()
{
if [ ! -d "$log_dir" ]; then
  mkdir "$log_dir"
fi

echo -e "Backup begin at ------ `date` ------\n\n"
if [ ! -d "$full_backup_dir" ]; then 
  echo "Full backup directory $full_backup_dir does not exist! Create it now!"
  mkdir -p "$full_backup_dir" && chown mysql:mysql $full_backup_dir
fi 

if [ ! -d "$incre_backup_dir" ]; then
  echo "Incremental backup directory $incre_backup_dir does not exist! Create it now!"
  mkdir -p "$incre_backup_dir" && chown mysql:mysql $incre_backup_dir
fi
}

rm_backupset()
{
# 分批次删除上一次的备份文件，以减少对磁盘的冲击
for i in ${full_backup_dir} ${incre_backup_dir}; do
  echo $i
  for j in `ls $i`; do
    for k in `ls $i/$j`; do
          if [ -f $i/$j/$k ]; then
          #如果是文件类型，则先清空，以尽量减少对磁盘的冲击
                > $i/$j/$k
          fi
      rm -rf $i/$j/$k
    done
        if [ -f $i/$j ]; then
                > $i/$j
        fi
    rm -rf $i/$j
  done
done
}

## Start to backup:
start_backup()
{
if [ $weekday == 1 ]; then
    rm_backupset; 
    /usr/bin/innobackupex --user=root --port=$port --defaults-file=$cnf_file --galera-info --no-timestamp --parallel=$threads $full_backup_dir/$bak_date
elif [ $weekday == 2 ]; then
    /usr/bin/innobackupex --user=root --port=$port --defaults-file=$cnf_file --galera-info --no-timestamp --parallel=$threads --incremental $incre_backup_dir/$bak_date --incremental-basedir=$full_backup_dir/$yestoday
else
    /usr/bin/innobackupex --user=root --port=$port --defaults-file=$cnf_file --galera-info --no-timestamp --parallel=$threads --incremental $incre_backup_dir/$bak_date --incremental-basedir=$incre_backup_dir/$yestoday
fi
}


## Check the status of backup:
status_check()
{
backup_state=False
STATUS=`tail -1 $logfile | awk -F ":" '{ print $4}' | awk '{print $2}'`
if [ $STATUS = 'OK!' ] ; then
  return 0
else
  return 77
fi
}

## 将备份文件传输到远程服务器
transfer_backupset()
{
compress_status=no
compress_file=none
if [ $weekday == 1 ]; then
    tar -cf $backup_dir/full_backup_$bak_date.tar  $full_backup_dir/$bak_date && pbzip2 -p$threads $backup_dir/full_backup_$bak_date.tar && compress_status=yes && compress_file=$backup_dir/full_backup_$bak_date.tar.bz2
else
    tar -cf $backup_dir/incre_backup_$bak_date.tar  $incre_backup_dir/$bak_date && pbzip2 -p$threads $backup_dir/incre_backup_$bak_date.tar && compress_status=yes && compress_file=$backup_dir/incre_backup_$bak_date.tar.bz2
fi

echo "compress_status: $compress_status compress_file: $compress_file"
if [ $compress_status == "yes" ] && [ $compress_file != "none" ]; then

<span style="white-space:pre">		</span># 考虑到远程传输过程中的安全问题，所以对备份文件进行加密
		#解密：openssl des -d -k "<span style="font-family: Arial, Helvetica, sans-serif;">password</span>.." -in full_backup_20160630.tar.bz2.enc > full_backup_20160630.tar.bz2.deencrypt
		/usr/bin/openssl des -salt -k "<span style="font-family: Arial, Helvetica, sans-serif;">password</span><span style="font-family: Arial, Helvetica, sans-serif;">.." -in $compress_file -out $compress_file.enc && > $compress_file && rm -rf $compress_file && echo "加密完成" && \</span>
        echo "开始scp.." && /usr/bin/sshpass -f $backup_dir/pwd.conf /usr/bin/scp -P $remote_port $compress_file.enc dbbackup@$remote_host:$remote_dir && \
        echo "scp完成。开始创建确认文件.." && /usr/bin/sshpass -f $backup_dir/pwd.conf /usr/bin/ssh -p $remote_port dbbackup@$remote_host "touch ${remote_dir}/transfer_state_${bak_date}.txt" && echo "已创建确认文件。" && \
        > $compress_file.enc && rm -rf $compress_file.enc && echo "已删除本地的压缩加密文件。" && \
        return 0
else
    return 99
fi
}

## Execute the functions above
create_dir  > $logfile;
start_backup >> $logfile 2>&1;
if [ $? == 0 ]; then 
    status_check >> $logfile 2>&1;
        if [ $? == 0 ]; then 
            transfer_backupset >> $logfile 2>&1;
                if [ $? == 0 ]; then 
                    subject="${HOSTNAME}_${bak_date}_备份完成，传输完成" && content=`cat /dev/null`
                elif [ $? == 99 ]; then 
                    subject="${HOSTNAME}_${bak_date}_备份完成，传输失败！！" && content=`tail -30 $logfile ; echo -e "\n\n See detail on $logfile"`
                else
                        subject="${HOSTNAME}_${bak_date}_备份状态未知，状态值 ： $?" && content=`tail -30 $logfile ; echo -e "\n\n See detail on $logfile"`
                fi
        elif [ $? == 77 ]; then 
            subject="${HOSTNAME}_${bak_date}_备份失败！！" && content=`tail -30 $logfile ; echo -e "\n\n See detail on $logfile"`
        else 
            subject="${HOSTNAME}_${bak_date}_备份检查报错！！" && content=`tail -30 $logfile ; echo -e "\n\n See detail on $logfile"`
        fi
else
        subject="${HOSTNAME}_${bak_date}_Innobackupex报错！！" && content=`tail -30 $logfile ; echo -e "\n\n See detail on $logfile"`
fi

echo $subject,$content
( echo "Subject: $subject"; echo "Content-Type: text/html;charset=utf-8"; echo "Content-Disposition: inline"; echo $content; ) | /usr/sbin/sendmail $maillist

异地备份脚本

#!/bin/bash
## Usage():  ./backup_MySQL.sh 
## Auth: John Liu 2016-06-30


maillist="liuj@abc.com"
remote_port=2
remote_host="192.168.1.3"
remote_dir=/app/dbbackup/$1
bak_date=`date "+%Y%m%d"`
hist_dir=/backup/$1
dbname=$1
logfile=$hist_dir/pull_${bak_date}.log

## Week day
weekday=`date "+%w"`

transfer()
{
local_transfer_state=False
confirm_state=False

if [ $weekday == 1 ]; then
	encrypted_file=$remote_dir/full_backup_$bak_date.tar.bz2.enc
else
	encrypted_file=$remote_dir/incre_backup_$bak_date.tar.bz2.enc
fi

#while [[ 1==1 ]]; do
for ((COUNTER=0; COUNTER<60; ++COUNTER)); do	
	echo "encrypted_file: $encrypted_file"
	/usr/bin/sshpass -f /backup/pwd.conf /usr/bin/scp -P $remote_port dbbackup@$remote_host:$remote_dir/transfer_state_$bak_date.txt $hist_dir && echo "scp确认文件完成" && confirm_state=True 
	if [ $confirm_state == "True" ]; then
		cd $hist_dir && find * -mtime 60 -exec rm -rf {} \; && echo "删除超过60天的文件完成。"
		/usr/bin/sshpass -f /backup/pwd.conf /usr/bin/scp -P $remote_port dbbackup@$remote_host:$encrypted_file $hist_dir && echo "scp备份文件完成" && local_transfer_state=True && \
		/usr/bin/sshpass -f /backup/pwd.conf /usr/bin/ssh -p $remote_port dbbackup@$remote_host "rm -rf $remote_dir/transfer_state_$bak_date.txt" && rm -rf $hist_dir/transfer_state_$bak_date.txt
		if [ $? == 0 ]; then
			subject="${dbname}_${bak_date}_异地备份完成" && content=`cat /dev/null`
		elif [ $local_transfer_state == "True" ]; then
			subject="${dbname}_${bak_date}_异地备份scp完成，未删除远程confirm文件" && content=`cat $logfile`
		else
			subject="${dbname}_${bak_date}_异地备份失败，请检查！" && content=`cat $logfile`
		fi
		( echo "Subject: $subject"; echo "Content-Type: text/html;charset=utf-8"; echo "Content-Disposition: inline"; echo $content; ) | /usr/sbin/sendmail $maillist
		break
	else
		sleep 120
	fi
done
}

transfer >> $logfile 2>&1