基于mysqldump编写自动全备增备的shell脚本

来源:转载


基于mysqldump编写自动全备增备的shell脚本

在线上MySQL数据库备份分为全备和增备,而xtrabackup备份已经支持了增量备份了,但是mysqldump就不支持增量备份,所以我们需要写一个shell脚本对于mysqldump来自动全备和增备。

一下脚本要求我们做一个全备的策略,然后如何做增量备份,自动完成每天执行增量备份,每个星期天执行全备。备份完删除二进制文件,减低磁盘压力。

[[email protected]~]#catmysqlback.sh
#!/bin/bash
##date=2017-11-7
##name=cml
##email=********@qq.com
##title=mysqldumpbackup

EMAIL="*******@qq.com"
mysqlbak_dir=/usr/local/mysql/mydata
mysqlbak_log=/usr/local/mysql/mydata/mysqlbak.log
DATE=`date+%Y%m%d`
TIME=`date+"%y-%m%H:%M:%S"`
DUMPBAK=$DATE.sql
GZDUMPBAK=$DATE.sql.gz
WEEK=`date+%w`
cd$mysqlbak_dir
USER=root
PASSWD=redhat
bin_log=`ls-trmysql-bin.0*|head-1`
bin_log_dir=/usr/local/mysql/mydata/bin_log

if[!-f$bin_log_dir];then
mkdir$bin_log_dir
fi

echo"--------------------------$TIME-----------------------------">>$mysqlbak_log
if[$WEEK==0];then##日期是星期天的时候执行
##week=0~6
cd$mysqlbak_dir
/usr/local/mysql/bin/mysqldump-u$USER-p$PASSWD--single-transaction-R--triggers-E--master-data--flush-logs--all-databases>$DUMPBAK2>&1
echo"mysqldumpbackupsuccess!!">>$mysqlbak_log
tar-czvf$GZDUMPBAK$DUMPBAK>>$mysqlbak_log2>&1
echo"$GZDUMPBAKbackupsuccess!!">>$mysqlbak_log
#if[-f$GZDUMPBAK];then
#echo"$GZDUMPBAKBACKUPSUCCESS!!"|mail-s"mysqldumpbackup"$EMAIL
#fi
rm-rf$DUMPBAK
rm-rf$bin_log
##上面步骤是全备过程
else
cd$mysqlbak_dir
/usr/local/mysql/bin/mysqladmin-u$USER-p$PASSWDflush-logs
cp-p$bin_log$bin_log_dir
echo"copy$bin_logto$bin_log_dir">>$mysqlbak_log
rm-rf$bin_log
echo"backupbin_logsuccess!!">>$mysqlbak_log
#echo"backupbin_log$bin_logSUCCESS!"|mail-s"backupbin_log"$EMAIL
##下面是增备过程
fi

##给脚本权限:

[[email protected]~]#cd/usr/local/src/script/
[[email protected]]#ls
mysqlback.sh
[[email protected]]#chmoda+xmysqlback.sh

##把它加入到crontab几乎任务里面:

[[email protected]]#crontab-e
10***/usr/local/src/script/mysqlback.sh

分享给朋友:
您可能感兴趣的文章:
随机阅读: