0
0

[转]Xtrabackup全量备份/增量备份脚本

鸟窝 发表于 2018年01月09日 17:36 | Hits: 629
Tag: 运维

本文收集了几篇关于使用Xtrabackup/innobackupex进行mysql数据库全量和增量备份的脚本,学习之用。搜集的几篇都是转载,非本文原创。

Xtrabackup全量备份/增量备份脚本

这篇转载的文章源于http://powermichael.blog.51cto.com/12450987/1954307,但目前这篇原始文章已经无法访问了。

全量备份脚本

全量备份脚本

12345678910111213
#!/bin/bash#Description:xtrabackup complete#Author:created by michael#2017-08-07 v0.1#USER=rootPASSWD=123456BACKUP_DIR=/backup/mysql/completeDATE=$(date +"%F_%T")[[ -d $BACKUP_DIR ]] || mkdir $DATE_DIRinnobackupex --user=$USER --password=$PASSWD $BACKUP_DIR &> /tmp/mysql/"$DATE".txtegrep ".* Backup created in directory.*" /tmp/mysql/"$DATE".txt >> $BACKUP_DIR/complete.inform -rf /tmp/mysql/"$DATE".txt

启动crond以及开机自启动crond

12
systemctl start crondsystemctl enable crond

授予执行权限

1
chmod 755 /root/script/backup_complete.sh

每周六的凌晨4点整定时执行全量备份

1234
[root@michaelos complete]# crontab -ecrontab: installing new crontab[root@michaelos complete]# crontab -l0 4 * * 6 /root/script/backup_complete.sh

增量备份脚本

增量备份脚本

123456789101112131415
[root@michaelos script]# cat backup_increment.sh #!/bin/bash #Description: mysql backup incremention#Author:michael#2017-08-07 v0.1#USER=rootPASSWORD=123456BACKUP_DIR=/backup/mysql/incrementDATE=$(date +"%F_$T")BASE_DIR=$(tail -1 /backup/mysql/complete/complete.info | cut -d\' -f2)[[ -d $BACKUP_DIR ]] || mkdir $BACKUP_DIRinnobackupex --user=$USER --password=$PASSWORD --incremental $BACKUP_DIR --incremental-basedir=$BASE_DIR &> /tmp/mysql/"$DATE".txtegrep ".*Backup created in directory.*" /tmp/mysql/"$DATE".txt >> $BACKUP_DIR/backup.inform -rf /tmp/mysql/"$DATE".txt

授予执行权限

1
chmod 755 backup_increment.sh

每周二、四、日的凌晨2点执行增量备份

123
[root@michaelos script]# crontab -l0 4 * * 6 /root/script/backup_complete.sh0 2 * * 2,4,7 /root/script/backup_increment.sh

xtrabackup全量增量备份脚本

这篇文章来自segmentfaultlinda玲

全量备份脚本

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293
#!/bin/sh# add ling INNOBACKUPEX=innobackupexINNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEXTODAY=`date +%Y%m%d%H%M`YESTERDAY=`date -d"yesterday" +%Y%m%d%H%M`USEROPTIONS="--user=user --password=123456"TMPFILE="/logs/mysql/innobackup_$TODAY.$$.tmp"MYCNF=/etc/my.cnfMYSQL=/usr/local/mariadb/bin/mysqlMYSQLADMIN=/usr/local/mariadb/bin/mysqladminBACKUPDIR=/backup/mysql # 备份的主目录FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录KEEP=1 # 保留几个全库备份 # Grab start time############################################################################## Display error message and exit#############################################################################error(){    echo "$1" 1>&2    exit 1} # Check options before proceedingif [ ! -x $INNOBACKUPEXFULL ]; then  error "$INNOBACKUPEXFULL does not exist."fi if [ ! -d $BACKUPDIR ]; then  error "Backup destination folder: $BACKUPDIR does not exist."fi if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then error "HALTED: MySQL does not appear to be running."fi if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."fi # Some info outputecho "----------------------------"echoecho "$0: MySQL backup script"echo "started: `date`"echo # Create full and incr backup directories if they not exist.for i in $FULLBACKUPDIR $INCRBACKUPDIRdo        if [ ! -d $i ]; then                mkdir -pv $i        fidone # 压缩上传前一天的备份echo "压缩前一天的备份,scp到远程主机"cd $BACKUPDIRtar -zcvf $YESTERDAY.tar.gz ./full/ ./incr/scp -P 8022 $YESTERDAY.tar.gz root@192.168.10.46:/data/backup/mysql/if [ $? = 0 ]; then  rm -rf $BACKUPDIR/full $BACKUPDIR/incr  echo "Running new full backup."  innobackupex --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1else  echo "Error with scp."fi if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then echo "$INNOBACKUPEX failed:"; echo echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------"# cat $TMPFILE# rm -f $TMPFILE exit 1fi# 这里获取这次备份的目录 THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`echo "THISBACKUP=$THISBACKUP"#rm -f $TMPFILEecho "Databases backed up successfully to: $THISBACKUP"# Cleanupecho "delete tar files of 3 days ago"find $BACKUPDIR/ -mtime +3 -name "*.tar.gz"  -exec rm -rf {} \; echoecho "completed: `date`"exit 0

增量备份脚本

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293
#!/bin/sh# add ling INNOBACKUPEX=innobackupexINNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEXTODAY=`date +%Y%m%d%H%M`USEROPTIONS="--user=user --password=123456"TMPFILE="/logs/mysql/incr_$TODAY.$$.tmp"MYCNF=/etc/my.cnfMYSQL=/usr/local/mariadb/bin/mysqlMYSQLADMIN=/usr/local/mariadb/bin/mysqladminBACKUPDIR=/backup/mysql # 备份的主目录FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录############################################################################## Display error message and exit#############################################################################error(){    echo "$1" 1>&2    exit 1} # Check options before proceedingif [ ! -x $INNOBACKUPEXFULL ]; then  error "$INNOBACKUPEXFULL does not exist."fi if [ ! -d $BACKUPDIR ]; then  error "Backup destination folder: $BACKUPDIR does not exist."fi if [ -z "`$MYSQLADMIN $USEROPTIONS status | grep 'Uptime'`" ] ; then error "HALTED: MySQL does not appear to be running."fi if ! `echo 'exit' | $MYSQL -s $USEROPTIONS` ; then error "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)."fi # Some info outputecho "----------------------------"echoecho "$0: MySQL backup script"echo "started: `date`"echo # Create full and incr backup directories if they not exist.for i in $FULLBACKUPDIR $INCRBACKUPDIRdo        if [ ! -d $i ]; then                mkdir -pv $i        fidone # Find latest full backupLATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"`echo "LATEST_FULL=$LATEST_FULL" # Run an incremental backup if latest full is still valid.# Create incremental backups dir if not exists.TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULLmkdir -p $TMPINCRDIRBACKTYPE="incr"# Find latest incremental backup.LATEST_INCR=`find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`echo "LATEST_INCR=$LATEST_INCR"  # If this is the first incremental, use the full as base. Otherwise, use the latest incremental as base.if [ ! $LATEST_INCR ] ; then  INCRBASEDIR=$FULLBACKUPDIR/$LATEST_FULLelse  INCRBASEDIR=$LATEST_INCRfiecho "Running new incremental backup using $INCRBASEDIR as base."innobackupex --defaults-file=$MYCNF $USEROPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1 if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then echo "$INNOBACKUPEX failed:"; echo echo "---------- ERROR OUTPUT from $INNOBACKUPEX ----------" exit 1fi# 这里获取这次备份的目录 THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`echo "THISBACKUP=$THISBACKUP"echoecho "Databases backed up successfully to: $THISBACKUP"echoecho "incremental completed: `date`"exit 0

分别基于innobackupex备份工具的mysql全量备份和增量备份shell脚本

这是另一篇mysql全量和增量备份的脚本, 来自21运维

之前有过一个基于innobackupex备份mysql文章,不是很简单实用。这里是一个更为简单的脚本,可以实现分别基于innobackupex备份工具的mysql全量备份和增量备份。
注意:
1,线上用的时候请注意将自己的用户名和密码都设置对,这里password密码我暂时用xxxxxx代替。当然,你也可以建立专用于备份用的数据库用户并设置权限。
2,根据情况设置一个任务计划。

mysql全量备份脚本如下

123456789101112131415
#!/bin/bash#author:21yunweilogfile="/home/mysqlbak/mysql_full_bak_time.log"backuplogfile="/home/mysqlbak/mysqlbackup.log"mysql_bakdir="/home/mysqlbak/"user=rootpassword=xxxxxx [ ! -d $mysql_bakdir  ] &&  mkdir -p $mysql_bakdir[ ! -f  $logfile ] && touch  $logfile innobackupex --defaults-file=/etc/my.cnf --user=${user} --password=${password} --compress –-parallel=4 –-throttle=400 --stream=tar ${mysql_bakdir} 2>&1 >> $backuplogfilereset=$?finishtime=`date +%Y%m%d_%H%M%S`[ ${reset} -eq 0 ] && echo  "Mysql fullbakup  finished, time: $finishtime">>  $logfile || echo "Mysql fullbakup failed,please check time:$finishtime" >>  $logfile

mysql增量备份脚本如下

123456789101112131415
#!/bin/bash#author:21yunweitime=`date -d '-3 day' +%Y-%m-%d`logfile="/home/mysqlbak/mysql_full_bak_time.log"mysql_bakdir="/home/mysqlbak/"user=rootpassword=xxxxxxlastfullbakdir=`ls /home/mysqlbak/ |grep ${time}` [ ! -d $mysql_bakdir  ] &&  mkdir -p $mysql_bakdir[ ! -d  $logfile ] && touch  $logfile innobackupex --defaults-file=/etc/my.cnf --user=${user} --password=${password} --incremental-basedir=${mysql_bakdir}/${lastfullbakdir}  --incremental ${mysql_bakdir}finishtime=`date +%Y%m%d_%H%M%S`[ $? -eq 0 ] && echo  "Mysql fullbakup  finished, time: $finishtime">>   $logfile || echo "Mysql fullbakup failed,please check time:$finishtime">>  $logfile

原文链接: http://colobu.com/2018/01/09/xtrabackup-full-increament-backup/

0     0

评价列表(0)