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

增量备份脚本

#!/bin/bash## Usage():  ./backup_MySQL.sh ## Auth: John Liu 2015-07-08 modified: 2016-06-29backup_dir=/backupfull_backup_dir=$backup_dir/fullincre_backup_dir=$backup_dir/incr# Backup files retentionretention=3threads=10cnf_file=/app/mysql/my.cnfport=3306maillist="[e-mail]"remote_host="192.168.1.1"remote_dir=/app/dbbackup/db05remote_port=22bak_date=`date "+%Y%m%d"`## Week dayweekday=`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/loglogfile=$log_dir/xtrabackup_$bak_date.logcreate_dir(){if [ ! -d "$log_dir" ]; then  mkdir "$log_dir"fiecho -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_dirfi 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_dirfi}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  donedone}## 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_dateelif [ $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/$yestodayelse    /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/$yestodayfi}## Check the status of backup:status_check(){backup_state=FalseSTATUS=`tail -1 $logfile | awk -F ":" '{ print $4}' | awk '{print $2}'`if [ $STATUS = 'OK!' ] ; then  return 0else  return 77fi}## 将备份文件传输到远程服务器transfer_backupset(){compress_status=nocompress_file=noneif [ $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.bz2else    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.bz2fiecho "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 0else    return 99fi}## Execute the functions abovecreate_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"`        fielse        subject="${HOSTNAME}_${bak_date}_Innobackupex报错！！" && content=`tail -30 $logfile ; echo -e "\n\n See detail on $logfile"`fiecho $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-30maillist="[e-mail]"remote_port=2remote_host="192.168.1.3"remote_dir=/app/dbbackup/$1bak_date=`date "+%Y%m%d"`hist_dir=/backup/$1dbname=$1logfile=$hist_dir/pull_${bak_date}.log## Week dayweekday=`date "+%w"`transfer(){local_transfer_state=Falseconfirm_state=Falseif [ $weekday == 1 ]; thenencrypted_file=$remote_dir/full_backup_$bak_date.tar.bz2.encelseencrypted_file=$remote_dir/incre_backup_$bak_date.tar.bz2.encfi#while [[ 1==1 ]]; dofor ((COUNTER=0; COUNTER<60; ++COUNTER)); doecho "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" ]; thencd $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.txtif [ $? == 0 ]; thensubject="${dbname}_${bak_date}_异地备份完成" && content=`cat /dev/null`elif [ $local_transfer_state == "True" ]; thensubject="${dbname}_${bak_date}_异地备份scp完成，未删除远程confirm文件" && content=`cat $logfile`elsesubject="${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 $maillistbreakelsesleep 120fidone}transfer >> $logfile 2>&1