Xtrabackup Automatic Recovery script

 #!/bin/sh###################[email protected]################### #xtrabackup2 .1.8#  Use method:#  ./restore.sh /incremental Backup Parent directory or fully-prepared parent directory name #eg1:./restore.sh/data/backup/full/#eg2:./restore.sh/data/backup/incre/# NOTICE: Incremental Backup restore If you specify a file, the end of the previous file to the specified file name is restored. #即输入的增量文件名应为需要截止的增量文件名的下一个文件    #NOTE: Make sure that the MySQL service is stopped and the data and log directories are emptied, such as # rm -rf/data/mysql/innodb_data/*# rm -rf/data/mysql/data/*#, before recovery begins  rm -rf/data/mysql/mysql_logs/innodb_log/*  echo "***************************************** "echo -e  ' \e[32m  tip: MySQL port is modified according to the actual environment.  \e[m '   #红色echo ' * * "    innobackupex= innobackupexinnobackupex_path=/usr/local/xtrabackup/bin/$INNOBACKUPEXTMP _log= "/var/log/restore.$$.log" MY_CNF=/ etc/my.cnfbackup_dir=/databackup #  your backup home directory fullbackup_dir= $BACKUP _dir/full #  Catalog incrbackup_dir= $BACKUP _dir/incre #  increments for full-Library backupsBackup directory memory=1024m #  memory limit used when restoring errorlog= ' grep-i  ' ^log-error '   $MY _cnf |cut -d  = -f 2 ' mysqld_safe=/usr/local/mysql/bin/mysqld_safemysql_port=3306      #############################################################################    #显示错误   #############################################################################  error ()  {     echo  "$"  1>&2     exit 1  }       ############################################################# ################   #  Check Innobackupex error Output   ####################################### ######################################  check_innobackupex_fail ()  {      if [ -z  "' tail -2  $TMP _log | grep ' completed ok! ' ' " ] ; then     echo  "$INNOBACKUPEX Command execution failed:";echo     echo  "----------$ Innobackupex Error Output  ----------"     cat  $TMP _log       #保留一份备份的详细日志     logfiledate=restore. ' Date +%y%m%d%h%m ' .txt      cat  $TMP _log>/backup/$logfiledate       rm -f  $TMP _ log     exit 1   fi }           #  option Detection if [ ! -x$innobackupex_path ]; then    error "$INNOBACKUPEX _path does not exist in the specified path, verify that the link is installed or verified correctly." fi       if [ ! -d  $BACKUP _dir ]; then    error  "Backup directory $backup_dir does not exist." Please create a new backup home directory $backup_dir " fi        if [ $# != 1 ] ; then   error&nbsP; " Use the method: $0  the absolute path of the restored directory "fi       if [ ! -d $1  ]; then   error  "Specified backup directory: $ not present." fi       portnum00= ' Netstat -lnt|grep ${mysql_port}|wc-l '  if  [  $PORTNUM 00 = 1  ]; then echo -e  "\e[31mnote:------------- -----------------------------. \e[m   #红色echo  -e  \e[31m mysql is running, turn off mysql. \e[m   #红色echo  -e  "\e[31mnote:------------------------------------------. \e[m"   #红色exit  0  fi   ############### #判断还原增量备份部分还是所有 ############### #echo "********************************* " echo -e " \e[31m  the increment file name should be the next file for the delta file name cutoff. \e[m "   #红色echo "*************************************************************************"  ipname= "  read -p  "Input cutoff incremental backup name [default all]:" IPNAME ECHO  &Nbsp;echo  "Input cutoff incremental backup name: $ipname"      input_value=$1 intpu_res= ' echo ${ input_value%/*} '        # Some info output echo  '-- --------------------------"echo echo " $0: mysql Restore Script "start_restore_time= ' date+%f '   '%T '   '%w '  echo  "database restore started at: $START _restore_time"  echo             #PARENT_DIR = ' Dirname ${intpu_res} '  PARENT_DIR=${intpu_res}    if [  $PARENT _dir =  $FULLBACKUP _dir ]; thenfull= ' ls-t  $FULLBACKUP _dir | Head -1 ' fullbackup=${intpu_res}/$FULLecho   "restore full backup: ' basename  $FULLBACKUP '" echo    else ############### #判断还原部分增量备份还是所有增量备份 ############### #if  ["$ipname"  =  " ];then  if [  $PARENT _dir =  $INCRBACKUP _dir ]; thenfull= ' ls-t  $FULLBACKUP _dir  |head -1 ' Fullbackup= $FULLBACKUP _dir/$FULLif  [ ! -d$FULLBACKUP ]; thenerror  "fully prepared: $FULLBACKUP does not exist." Fiincr= ' ls -t  $INCRBACKUP _dir/$FULL/ |sort -nr | head -1  '  # Find the last incremental backup file echo  "The restore will start with a fully prepared $full and end with an incremental $incr." echoecho  "Prepare: Full backup ..." echo "*****************************" $INNOBACKUPEX _path--defaults-file=$ my_cnf --apply-log --redo-only --use-memory= $MEMORY $fullbackup >  $TMP _log 2 >&1check_innobackupex_fail    # prepare incremental backup set, which will be applied to the fully-prepared directory, Follow the incremental backup order, from old to newest for i in ' find  $PARENT _dir/$FULL  -mindepth 1 -maxdepth 1  -type d -printf "%p\n"  | sort -n  ';d o   #判断最新全备的lsn #check_full_ File= ' find$fullbackup/ -mindepth 1 -maxdepth 1 -type d -printf  "%P\n"  | sort-nr | head  -1 '   check_full_lastlsn= $FULLBACKUP/xtrabaCkup_checkpoints  fetch_full_lastlsn= ' grep-i  "^LAST_LSN"  ${check_full_lastlsn} |cut  -d = -f 2 '   ##### #判断增量备份中第一个增量备份的LSN #check_incre_file= ' find$parent_dir/$FULL  -mindepth 1 -maxdepth 1 -type d -printf  "%p\n"  |sort -n  |  head -1 '     check_incre_lastlsn= $PARENT _dir/$FULL/$i/xtrabackup_ Checkpoints    fetch_incre_lastlsn= ' grep-i  "^last_lsn"  ${CHECK_INCRE_LASTLSN}  |cut -d = -f 2 ' echo  ' full backup $fullbackup LSN value:${fetch_full_lastlsn}  "echo   "Incremental backup $i LSN value:${fetch_incre_lastlsn} "  if ["${fetch_incre_lastlsn}"  -eq  "${fetch _FULL_LASTLSN} " ];thenecho" ***************************************** "echo " LSN is equal and does not require prepare! " echo "*****************************************" echobreak   elseecho  "Prepare: Incremental backup set $i .... "Echo" ***************************** "$INNOBACKUPEX _path--defaults-file= $MY _cnf --apply-log --redo-only --use-memory= $MEMORY $ fullbackup --incremental-dir= $PARENT _dir/$FULL/$i  >  $TMP _log 2>&1check_ innobackupex_fail if [$INCR  =  $i  ]; thenbreakfi   fi###### Determine lsndone elseerror  "Unknown backup type"   fi  elsefull= ' ls-t  $FULLBACKUP _dir | Head -1 ' fullbackup= $FULLBACKUP _dir/$FULLecho   "Prepare: Full backup ..." echo "************************* "$INNOBACKUPEX _path--defaults-file= $MY _cnf --apply-log --redo-only --use-memory= $MEMORY $ fullbackup >  $TMP _log 2>&1check_innobackupex_fail     ipt= ' stat-c=%z   $PARENT _dir/$FULL/$ipname  |cut -d= -f 2 ' echo  ' to restore the specified delta directory file $ Ipname's epoch Time is: $ipt "For i in ' find  $PARENT _dir/$FULL  -mindepth 1 -maxdepth 1  -type d -printf "%p\n"  | sort -n  ';d o# determines whether the file name that is taken by the for loop is the input filename, and if so, stops the loop if [$i  =   $ipname  ]; Thenbreak;elsef01= ' stat-c=%z   $PARENT _dir/$FULL/$i  |cut -d = -f2 ' if ["$f 01"  -le  "$ipt"  ]; then  if [$PARENT _dir =  $INCRBACKUP _dir ];  thenif [ ! -d$FULLBACKUP ]; thenerror  "fully prepared: $FULLBACKUP does not exist." Fi#incr= ' ls-t  $INCRBACKUP _dir/$FULL/ |sort -nr | head -1 ' echo  ' restore will be fully prepared from $ Full start, end to incremental $ipname. " echo   #判断最新全备的lsn #check_full_file= ' find$fullbackup/ -mindepth 1 -maxdepth 1  -type d -printf  "%p\n"  | sort-nr | head  -1 '    check_full_lastlsn= $FULLBACKUP/xtrabackup_checkpoints  fetch_full_lastlsn= ' grep-i  "^LAST_LSN"  ${check_full_lastlsn} |cut -d = -f 2 '   ##### #判断增量备份中第一个增量备份的LSNcheck_ Incre_file= ' Find$parent_dir/$FULL  -mindepth 1 -maxdepth 1 -type d -printf  "%p\n"  |sort  -n |  head -1 '     check_incre_lastlsn= $PARENT _dir/$FULL/$i/xtrabackup_ Checkpoints    fetch_incre_lastlsn= ' grep-i  "^last_lsn"  ${CHECK_INCRE_LASTLSN}  |cut -d = -f 2 ' echo  ' full backup of lsn:${fetch_full_lastlsn}  "echo " Incremental backup lsn:$ {fetch_incre_lastlsn}  "if [" ${fetch_incre_lastlsn} " -eq " ${fetch_full_lastlsn} " ]; Thenecho "*****************************************" echo -e  "\E[31M LSN does not require prepare or no data changes during backup interval  \e[m "  #红色echo" ***************************************** "   fi  echoecho   "Prepare: Incremental backup set $i ..." echo "*****************************" $INNOBACKUPEX _path--defaults-file= $MY _cnf  --apply-log --redo-only --use-memory= $MEMORY $fullbackup --incremental-dir= $PARENT _dir/$ full/$i  >  $TMP_log 2>&1check_innobackupex_fail ##### #判断LSNcheck_full_lastlsn = $FULLBACKUP/xtrabackup_ Checkpoints  fetch_full_lastlsn= ' grep-i  "^LAST_LSN"  ${check_full_lastlsn} |cut - D = -f 2 ' echo  ' full backup of current LSN:${FETCH_FULL_LASTLSN} '   elseerror  ' unknown backup type ' fielseecho  "Find incremental backup file complete." check_full_lastlsn= $FULLBACKUP/xtrabackup_checkpoints  fetch_full_lastlsn= ' grep-i  "^LAST_LSN"  ${check_full_lastlsn} |cut -d = -f 2 ' echo -e  ' \e[31m ----------- ---------------------------------\e[m "  #红色echo  -e " \e[31m  full backup final LSN:${FETCH_FULL_LASTLSN}  \e[m "  #红色echo  -e " \e[31m --------------------------------------------\e[m " # Red breakfifidone  fi############### #判断还原部分增量备份还是所有增量备份 ################   fi      echo echo  "Prepare: Full backup and rollback of those uncommitted transactions ..."   $INNOBACKUPEX _path.  --defaults-file= $MY _cnf--apply-log --use-memory= $MEMORY   $FULLBACKUP  >  $TMP _log 2> &1 check_innobackupex_fail     echo  "*****************************"  echo  "Database restore  .. Please wait a moment "echo" ***************************** "    $INNOBACKUPEX _path --defaults-file= $MY _ cnf--copy-back  $FULLBACKUP  >  $TMP _log 2>&1 check_innobackupex_fail      rm -f  $TMP _log echo  "1. Congratulations, restore success!." echo "*****************************"       #修改目录权限echo   "Modify the permissions of the MySQL directory." mysqlcnf= "/etc/my.cnf"  mysqldatadir= ' grep -i  "^datadir" $mysqlcnf  |cut -d =  -f 2 '   ' echo  ' Chown -r mysql:mysql '  ${mysqldatadir} '  echo  ' 2. Permissions modified successfully! " echo "*****************************"       #自动启动mysql    INIT_NUM=1  if [ ! -x  $MYSQLD _SAFE ];  then   echo  "MySQL Installation when startup file is not installed to $mysqld_safe or no execute permission"    exit 1    #0是执行成功, 1 is performed unsuccessfully elseecho  "start native MYSQL port for: $MYSQL _port Service" $MYSQLD _safe--defaults-file= $MY _cnf   > /dev/null&while  [  $INIT _num -le 10 ]        do         portnum= ' netstat -lnt| Grep${mysql_port}|wc -l '          echo  ' MYSQL startup .... Please wait a moment ... "         sleep 10          if [  $PORTNUM  = 1  ];          thenecho -e  "\e[32m mysql                                        **** startup Success ****\e[m "          exit 0         fi           init_num=$ (($INIT _num +1))        doneecho -e   "\e[31m mysql boot failed or the boot time is too long, please check the error log   ' echo  ' cat  '  ${errorlog} '  \e[m ' echo ' * * * * * "exit 0 fi          End_restore_time= ' date +%f '   '%T '   '%w '  echo  ' database restore completed: $END _restore_time '  exit  0

Reference:

http://blog.itpub.net/29114615/viewspace-1408347

http://blog.csdn.net/yangzhawen/article/details/44857097

Http://www.2cto.com/database/201504/388993.html

This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1758032

Xtrabackup Automatic Recovery script