mysql 数据库备份脚本
2018-03-22
crontab -l
# 每天早上0点 数据库备份 0 0 * * * /bin/bash /data/backup/scripts/backup_mysql.sh >/dev/null 2>&1
/data/backup/scripts/backup_mysql.sh
#!/bin/bash ### AUTHOR: cenliang ### EMAIL: xuanniaoxi@sina.com ### DATE: 2018/03/29 ### DESC: MySQL Backup Scripts ### REV: 2.1 source /etc/profile source /root/.bash_profile ################################# # # # 全局配置 # # # ################################# # mysql实例名 InstName="my3306" # mysql配置文件 Conf=/app/mysql/etc/my.cnf # mysql用户名 User=xbackupopr # mysql密码 Password=t72e6_lWEdrN # 主库虚拟地址 VIP="172.31.132.60" # 备份日期，格式 20171030 BackupDate=`date +%Y%m%d` # 备份主机 BackupServer=`hostname` # 备份IP BackupIP=`ifconfig eth0 |grep "inet " |awk '{print $2}'` # Inno日志路径 InnoLog=/data/backup/log/$InstName/InnoBackup.log # 备份日志路径 BackupLog=/data/backup/log/$InstName/Backup.log # 全量备份路径 TargetFullDir=/data/backup/innobackup/$InstName/full # 基础备份路径 TargetBaseDir=`ls $TargetFullDir` # 增量备份路径 TargetIncrementDir=/data/backup/innobackup/$InstName/increment # 实例备份路径 TargetInstDir=/data/backup/innobackup/$InstName # 备份同步路径 TargetRsyncDir=/data/backup/mysql/$InstName # 备份同步日志路径 # RsyncLog=/data/rsync/log/$InstName/$BackupDate_$InstName.log # 备份服务器地址 RsyncSrv="backup" # 备份服务器的备份路径 RsyncDir=/data/rsync/thbl/mysql # 收件人 addr="cenliang@allinpay.com liulj3@allinpay.com chenzp3@allinpay.com wuyong@allinpay.com" #addr="cenliang@allinpay.com" # 邮件标题 subj="${BackupServer}(${BackupIP})_${InstName}_巡检报告" # 邮件附件 file=$BackupLog # 邮件正文 body=$BackupLog ################################# # # # 函数定义 # # # ################################# # 备份策略函数 function f_backup_strategy() { # 周六做全备，周日到周五做增备 WeekDaily=`date +%a` case "$WeekDaily" in "Mon") export BackupLevel=Inc ;; "Tue") export BackupLevel=Inc ;; "Wed") export BackupLevel=Inc ;; "Thu") export BackupLevel=Inc ;; "Fri") export BackupLevel=Inc ;; "Sat") export BackupLevel=Full ;; "Sun") export BackupLevel=Inc ;; "*") export BackupLevel=error esac echo "Today is : $WeekDaily Backup level=$BackupLevel" > $BackupLog } # 全量备份函数 function f_backup_full() { # 删除历史全量备份 cd $TargetFullDir rm -rf $TargetFullDir/* # 备份开始标识 echo >> $BackupLog echo "Starting MySQL full backup ........" >> $BackupLog # 备份开始时间 BackupStartTime=`date '+%Y-%m-%d %H:%M:%S'` BackupStartSec=`date +%s` echo "BackupStartTime: $BackupStartTime" >> $BackupLog # 全量备份 innobackupex \ --defaults-file=$Conf \ --user=$User \ --password=$Password \ $TargetFullDir > $InnoLog 2>&1 # 备份结束时间 BackupEndTime=`date '+%Y-%m-%d %H:%M:%S'` BackupEndSec=`date +%s` echo "BackupEndTime: $BackupEndTime" >> $BackupLog # 备份消耗时长 BackupTotalSec=$((BackupEndSec-BackupStartSec)) echo "BackupTotalSec: $BackupTotalSec s" >> $BackupLog echo "BackupTotalTime: $((BackupTotalSec/3600))h$((BackupTotalSec%3600/60))min$((BackupTotalSec%3600%60))s" >> $BackupLog # 备份日志 echo "...................................." >> $BackupLog tail -10 $InnoLog >> $BackupLog } # 增量备份函数 function f_backup_incremental() { # 删除历史增量备份 cd $TargetIncrementDir rm -rf $TargetIncrementDir/* # 备份开始标识 echo >> $BackupLog echo "Starting MySQL incremental backup ........" >> $BackupLog # 备份开始时间 BackupStartTime=`date '+%Y-%m-%d %H:%M:%S'` BackupStartSec=`date +%s` echo "BackupStartTime: $BackupStartTime" >> $BackupLog # 增量备份 innobackupex \ --defaults-file=$Conf \ --user=$User \ --password=$Password \ --incremental-basedir=$TargetFullDir/$TargetBaseDir \ --incremental $TargetIncrementDir > $InnoLog 2>&1 # 备份结束时间 BackupEndTime=`date '+%Y-%m-%d %H:%M:%S'` BackupEndSec=`date +%s` echo "BackupEndTime: $BackupEndTime" >> $BackupLog # 备份消耗时长 BackupTotalSec=$((BackupEndSec-BackupStartSec)) echo "BackupTotalSec: $BackupTotalSec s" >> $BackupLog echo "BackupTotalTime: $((BackupTotalSec/3600))h$((BackupTotalSec%3600/60))min$((BackupTotalSec%3600%60))s" >> $BackupLog # 备份日志 echo "...................................." >> $BackupLog tail -10 $InnoLog >> $BackupLog } # 备份打包压缩函数 function f_backup_tarball() { case "$BackupLevel" in "Full") export TarBallDir=full ;; "Inc") export TarBallDir=increment ;; "*") export TarBallDir=error esac # 打包压缩开始标识 echo >> $BackupLog echo "Starting MySQL tarball ........" >> $BackupLog # 打包压缩开始时间 TarBallStartTime=`date '+%Y-%m-%d %H:%M:%S'` TarBallStartSec=`date +%s` echo "TarBallStartTime: $TarBallStartTime" >> $BackupLog # 打包压缩 TarBall=""$InstName"_"$BackupDate"_"$BackupLevel".tar.gz" cd $TargetInstDir tar -zcvf $TarBall $TarBallDir mv $TarBall $TargetRsyncDir # 打包压缩结束时间 TarBallEndTime=`date '+%Y-%m-%d %H:%M:%S'` TarBallEndSec=`date +%s` echo "TarBallEndTime: $TarBallEndTime" >> $BackupLog # 打包压缩消耗时长 TarBallTotalSec=$((TarBallEndSec-TarBallStartSec)) echo "TarBallTotalSec: $TarBallTotalSec s" >> $BackupLog echo "TarBallTotalTime: $((TarBallTotalSec/3600))h$((TarBallTotalSec%3600/60))min$((TarBallTotalSec%3600%60))s" >> $BackupLog } # 备份同步函数 function f_backup_rsync() { # 备份同步开始标识 echo >> $BackupLog echo "Starting MySQL Rsync ........" >> $BackupLog # 备份同步开始时间 RsyncStartTime=`date '+%Y-%m-%d %H:%M:%S'` RsyncStartSec=`date +%s` echo "RsyncStartTime: $RsyncStartTime" >> $BackupLog # 备份同步 # scp $TargetRsyncDir/$TarBall $RsyncSrv:$RsyncDir rsync -vzrtopg $TargetRsyncDir $RsyncSrv:$RsyncDir if [ $? = 0 ];then echo "Rsync completely OK !" >> $BackupLog else echo "Rsync failed!" >> $BackupLog fi # 备份同步结束时间 RsyncEndTime=`date '+%Y-%m-%d %H:%M:%S'` RsyncEndSec=`date +%s` echo "RsyncEndTime: $RsyncEndTime" >> $BackupLog # 备份同步消耗时长 RsyncTotalSec=$((RsyncEndSec-RsyncStartSec)) echo "RsyncTotalSec: $RsyncTotalSec s" >> $BackupLog echo "RsyncTotalTime: $((RsyncTotalSec/3600))h$((RsyncTotalSec%3600/60))min$((RsyncTotalSec%3600%60))s" >> $BackupLog } ################################# # # # 函数调用 # # # ################################# # 检查本地是否为主库，备份在从库执行 VIPSTAT=`/sbin/ip a|grep $VIP ` if [ -n "$VIPSTAT" ] ; then echo "MySQL slave is not on this host and backup job is not runing on this host !!!" else #调用备份策略函数 f_backup_strategy case "$BackupLevel" in "Inc") # 调用增量备份函数 f_backup_incremental # 调用备份打包压缩函数 f_backup_tarball # 调用备份同步函数 f_backup_rsync ;; "Full") # 调用全量备份函数 f_backup_full # 调用备份打包压缩函数 f_backup_tarball # 调用备份同步函数 f_backup_rsync ;; "*") exit 99 ;; esac # 删除备份目录下，修改时间为30天以前的数据 find $TargetRsyncDir -mtime +30 -delete # 发送备份报告邮件 mail -s $subj $addr < $body fi
