超好用的Xtrabackup的备份与还原脚本

作者： 魏延是反贼. 分类： 数据库, 系统·安全·调优

如果你用Xtrabackup，那你就知道面对上百G的数据恢复起来有多么效率了~那么，一个合理的备份与恢复脚本就成了DBA的必需品！

最近发现一个写的非常优秀的备份与还原脚本，拿来给大家分享。

使用方法：

1.备份： 先设置好脚本中定义的备份位置之后，运行./xtrabackup-backup.sh即可

只要你运行了这个脚本，那么它就会进行备份，第一次默认进行全库备份，如果有全库备份，且全库备份时间不超过1天(默认)，那么就根据全库备份的最后post点进行增量备份。如果运行的时候发现上一个全库备份已经超过规定的1天(默认)，那么进行全新的全库备份，这个时间在脚本中定义。

2.还原：./xtrabackup-restore.sh /备份目录名

因为xtrabackup备份之后起的备份目录名的格式是“2013-11-22-11-11”这样的时间目录，所以还原的时候，脚本后跟最后一次增量/全库备份的那个目录即可。脚本会自动将全库备份以及全库备份之后的增量备份依次还原。

Xtrabackup备份脚本xtrabackup-backup.sh：

#!/bin/sh
# 
# 这个脚本可以使用innobackupex命令创建一个完整的全库备份
# 关于innobackup的帮助信息可以参考：
# http://www.percona.com/doc/percona-xtrabackup/innobackupex/innobackupex_script.html
#
# 当你第一次执行它的时候它会先创建一个全库备份
# 之后当你运行它的时候，它会根据脚本中的设定来基于之前的全库备份进行增量备份
# 每次全库备份的时间在脚本中定义
#
# 脚本的版权&说明：
# (C)2010 Owen Carter @ Mirabeau BV
# This script is provided as-is; no liability can be accepted for use.
# You are free to modify and reproduce so long as this attribution is preserved.
#

INNOBACKUPEX=innobackupex-1.5.1
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
USEROPTIONS="--user=root --password=XXXXXX"
TMPFILE="/tmp/innobackupex-runner.$$.tmp"
MYCNF=/etc/mysql/my.cnf
MYSQL=/usr/bin/mysql
MYSQLADMIN=/usr/bin/mysqladmin
BACKUPDIR=/backup # 备份的主目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录
FULLBACKUPLIFE=86400 # 全库备份的间隔周期，时间：秒
KEEP=1 # 保留几个全库备份

# Grab start time
STARTED_AT=`date +%s`

#############################################################################
# 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.
mkdir -p $FULLBACKUPDIR
mkdir -p $INCRBACKUPDIR

# Find latest full backup
LATEST_FULL=`find $FULLBACKUPDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

# Get latest backup last modification time
LATEST_FULL_CREATED_AT=`stat -c %Y $FULLBACKUPDIR/$LATEST_FULL`

# Run an incremental backup if latest full is still valid. Otherwise, run a new full one.
if [ "$LATEST_FULL" -a `expr $LATEST_FULL_CREATED_AT + $FULLBACKUPLIFE + 5` -ge $STARTED_AT ] ; then
  # Create incremental backups dir if not exists.
  TMPINCRDIR=$INCRBACKUPDIR/$LATEST_FULL
  mkdir -p $TMPINCRDIR

  # Find latest incremental backup.
  LATEST_INCR=`find $TMPINCRDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`

  # 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."
  $INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS --incremental $TMPINCRDIR --incremental-basedir $INCRBASEDIR > $TMPFILE 2>&1
else
  echo "Running new full backup."
  $INNOBACKUPEXFULL --defaults-file=$MYCNF $USEROPTIONS $FULLBACKUPDIR > $TMPFILE 2>&1
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`
rm -f $TMPFILE

echo "Databases backed up successfully to: $THISBACKUP"
echo

# Cleanup
echo "Cleanup. Keeping only $KEEP full backups and its incrementals."
AGE=$(($FULLBACKUPLIFE * $KEEP / 60))
find $FULLBACKUPDIR -maxdepth 1 -type d -mmin +$AGE -execdir echo "removing: "$FULLBACKUPDIR/{} \; -execdir rm -rf $FULLBACKUPDIR/{} \; -execdir echo "removing: "$INCRBACKUPDIR/{} \; -execdir rm -rf $INCRBACKUPDIR/{} \;

echo
echo "completed: `date`"
exit 0

好了，有了备份脚本，对应的就应该有还原脚本：

Xtrabackup还原脚本xtrabackup-restore.sh：

#!/bin/sh
# 
# 这个脚本用于还原DB，对应Xtrabackup的备份脚本辅助使用
# 使用方法：
# ./xtrabackup-restore.sh /your-full(incr)backup-dir
#
# 脚本的版权&说明：
# (C)2010 Owen Carter @ Mirabeau BV
# This script is provided as-is; no liability can be accepted for use.
# You are free to modify and reproduce so long as this attribution is preserved.
#
 
INNOBACKUPEX=innobackupex-1.5.1
INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX
TMPFILE="/tmp/innobackupex-restore.$$.tmp"
MYCNF=/etc/mysql/my.cnf
BACKUPDIR=/backup # 你的备份主目录
FULLBACKUPDIR=$BACKUPDIR/full # 全库备份的目录
INCRBACKUPDIR=$BACKUPDIR/incr # 增量备份的目录
MEMORY=1024M # 还原的时候使用的内存限制数
 
#############################################################################
# Display error message and exit
#############################################################################
error()
{
	echo "$1" 1>&2
	exit 1
}
 
#############################################################################
# Check for errors in innobackupex output
#############################################################################
check_innobackupex_error()
{
	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
}
 
# 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 [ $# != 1 ] ; then
  error "Usage: $0 /absolute/path/to/backup/to/restore"
fi
 
if [ ! -d $1 ]; then
  error "Backup to restore: $1 does not exist."
fi
 
# Some info output
echo "----------------------------"
echo
echo "$0: MySQL backup script"
echo "started: `date`"
echo
 
PARENT_DIR=`dirname $1`
 
if [ $PARENT_DIR = $FULLBACKUPDIR ]; then
  FULLBACKUP=$1
  
  echo "Restore `basename $FULLBACKUP`"
  echo
else
  if [ `dirname $PARENT_DIR` = $INCRBACKUPDIR ]; then
    INCR=`basename $1`
    FULL=`basename $PARENT_DIR`
    FULLBACKUP=$FULLBACKUPDIR/$FULL
    
    if [ ! -d $FULLBACKUP ]; then
      error "Full backup: $FULLBACKUP does not exist."
    fi
    
    echo "Restore $FULL up to incremental $INCR"
    echo
    
    echo "Replay committed transactions on full backup"
    $INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --redo-only --use-memory=$MEMORY $FULLBACKUP > $TMPFILE 2>&1
    check_innobackupex_error
    
    # Apply incrementals to base backup
    for i in `find $PARENT_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -n`; do
      echo "Applying $i to full ..."
      $INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --redo-only --use-memory=$MEMORY $FULLBACKUP --incremental-dir=$PARENT_DIR/$i > $TMPFILE 2>&1
      check_innobackupex_error
      
      if [ $INCR = $i ]; then
        break # break. we are restoring up to this incremental.
      fi
    done
  else
    error "unknown backup type"
  fi
fi
 
echo "Preparing ..."
$INNOBACKUPEXFULL --defaults-file=$MYCNF --apply-log --use-memory=$MEMORY $FULLBACKUP > $TMPFILE 2>&1
check_innobackupex_error
 
echo
echo "Restoring ..."
$INNOBACKUPEXFULL --defaults-file=$MYCNF --copy-back $FULLBACKUP > $TMPFILE 2>&1
check_innobackupex_error
 
rm -f $TMPFILE
echo "Backup restored successfully. You are able to start mysql now."
echo "Verify files ownership in mysql data dir."
echo "Run 'chown -R mysql:mysql /path/to/data/dir' if necessary."
echo
echo "completed: `date`"
exit 0

标签：, ,

返回正文

(1)条评论

  • a

    |

    vb

    回复

您的留言是我最大的支持！

专业的友情链接：卫岩的51cto博客、马哥教育、一起吧，GO！、别样的天空、马哥的51cto博客、LustLost