6
F6fde213d3
其他fancool • 于 2018-01-01发布 • 79次阅读 • (原文: colobu.com)

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

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

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

全量备份脚本

全量备份脚本

# !/bin/bash
# Description:xtrabackup complete
# Author:created by michael
# 2017-08-07 v0.1
# 
USER=root
PASSWD=123456

BACKUP_DIR=/backup/mysql/complete
DATE=$(date +"%F_%T")
[[-d $BACKUP_DIR]] || mkdir $DATE_DIR

innobackupex --user=$USER --password=$PASSWD $BACKUP_DIR &> /tmp/mysql/"$DATE".txt
egrep ".* Backup created in directory.*" /tmp/mysql/"$DATE".txt >> $BACKUP_DIR/complete.info
rm -rf /tmp/mysql/"$DATE".txt

启动crond以及开机自启动crond

systemctl start crond
systemctl enable crond

授予执行权限

chmod 755 /root/script/backup_complete.sh

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

[root@michaelos complete]# crontab -e

crontab: installing new crontab
[root@michaelos complete]# crontab -l

0 4 * * 6 /root/script/backup_complete.sh

增量备份脚本

增量备份脚本

[root@michaelos script]# cat backup_increment.sh 

# !/bin/bash 
# Description: mysql backup incremention
# Author:michael
# 2017-08-07 v0.1
# 
USER=root
PASSWORD=123456

BACKUP_DIR=/backup/mysql/increment
DATE=$(date +"%F_$T")
BASE_DIR=$(tail -1 /backup/mysql/complete/complete.info | cut -d\' -f2)

[[-d $BACKUP_DIR]] || mkdir $BACKUP_DIR
innobackupex --user=$USER --password=$PASSWORD --incremental $BACKUP_DIR --incremental-basedir=$BASE_DIR &> /tmp/mysql/"$DATE".txt
egrep ".*Backup created in directory.*" /tmp/mysql/"$DATE".txt >> $BACKUP_DIR/backup.info
rm -rf /tmp/mysql/"$DATE".txt

授予执行权限

chmod 755 backup_increment.sh

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

[root@michaelos script]# crontab -l

0 4 * * 6 /root/script/backup_complete.sh

0 2 * * 2,4,7 /root/script/backup_increment.sh

xtrabackup全量增量备份脚本

这篇文章来自 segmentfaultlinda玲

全量备份脚本

# !/bin/sh
#  add ling

INNOBACKUPEX=innobackupex
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX

TODAY=`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.cnf
MYSQL=/usr/local/mariadb/bin/mysql
MYSQLADMIN=/usr/local/mariadb/bin/mysqladmin
BACKUPDIR=/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 proceeding

if [! -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 output

echo "----------------------------"

echo

echo "$0: MySQL backup script"

echo "started: `date`"

echo

#  Create full and incr backup directories if they not exist.

for i in $FULLBACKUPDIR $INCRBACKUPDIR

do
        if [! -d $i]; then

                mkdir -pv $i

        fi

done

#  压缩上传前一天的备份

echo "压缩前一天的备份,scp到远程主机"

cd $BACKUPDIR

tar -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>&1

else
  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 1

fi

#  这里获取这次备份的目录 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`

echo "THISBACKUP=$THISBACKUP"

# rm -f $TMPFILE

echo "Databases backed up successfully to: $THISBACKUP"

#  Cleanup

echo "delete tar files of 3 days ago"

find $BACKUPDIR/ -mtime +3 -name "*.tar.gz" -exec rm -rf {} \;

echo

echo "completed: `date`"

exit 0

增量备份脚本

# !/bin/sh
#  add ling

INNOBACKUPEX=innobackupex
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX

TODAY=`date +%Y%m%d%H%M`
USEROPTIONS="--user=user --password=123456"

TMPFILE="/logs/mysql/incr_$TODAY.$$.tmp"

MYCNF=/etc/my.cnf
MYSQL=/usr/local/mariadb/bin/mysql
MYSQLADMIN=/usr/local/mariadb/bin/mysqladmin
BACKUPDIR=/backup/mysql # 备份的主目录

FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录

INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录

############################################################################# 
#  Display error message and exit
############################################################################# 

error()
{
    echo "$1" 1>&2

    exit 1

}

#  Check options before proceeding

if [! -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 output

echo "----------------------------"

echo

echo "$0: MySQL backup script"

echo "started: `date`"

echo

#  Create full and incr backup directories if they not exist.

for i in $FULLBACKUPDIR $INCRBACKUPDIR

do
        if [! -d $i]; then

                mkdir -pv $i

        fi

done

#  Find latest full backup
LATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P
"`

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_FULL

mkdir -p $TMPINCRDIR

BACKTYPE="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_FULL

else
  INCRBASEDIR=$LATEST_INCR

fi

echo "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 1

fi

#  这里获取这次备份的目录 
THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPFILE`

echo "THISBACKUP=$THISBACKUP"

echo

echo "Databases backed up successfully to: $THISBACKUP"

echo

echo "incremental completed: `date`"

exit 0

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

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

之前有过一个基于innobackupex备份mysql文章,不是很简单实用。这里是一个更为简单的脚本,可以实现分别基于innobackupex备份工具的mysql全量备份和增量备份。

注意:

1,线上用的时候请注意将自己的用户名和密码都设置对,这里password密码我暂时用xxxxxx代替。当然,你也可以建立专用于备份用的数据库用户并设置权限。

2,根据情况设置一个任务计划。

mysql全量备份脚本如下

# !/bin/bash
# author:21yunwei
logfile="/home/mysqlbak/mysql_full_bak_time.log"

backuplogfile="/home/mysqlbak/mysqlbackup.log"

mysql_bakdir="/home/mysqlbak/"

user=root
password=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 >> $backuplogfile

reset=$?
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增量备份脚本如下

# !/bin/bash
# author:21yunwei
time=`date -d '-3 day' +%Y-%m-%d`
logfile="/home/mysqlbak/mysql_full_bak_time.log"

mysql_bakdir="/home/mysqlbak/"

user=root
password=xxxxxx
lastfullbakdir=`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
暂无回复。
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册
Top