MySQL Recovery script

The original text turns from:

http://blog.csdn.net/dbanote/article/details/13295727

Application scenario: ************************************************************************************************1. Backup directory is/ The Backup/full and/backup/incre architectures, which save the full standby, save an incremental backup of 2. If fully prepared for/backup/full/2015-04-08_15-14-33, the directory name will be fully prepared 2015-04-08_ 15-14-33 as the directory name for the incremental backup under/backup/incre/. This design is designed to be as long as the full-time, the full-file after the incremental backup is also less than the period. 3. When you restore, the script automatically finds the latest fully-prepared and up-to-date, fully-named incremental backup directory, and applies the incremental backup in sequence to full standby. Finalize the Restore. 4. This version increases the recovery of all incremental backups, which are all incremental by default. ************************************************************************** Script *************************************************************************************** #!/bin/sh#[email protected] #xtrabackup 2.3.3# How to use: #./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, restore to the end of the previous file of the specified file name. # That is, the increment file name you enter should be the next file for the delta file name that you want to cut #note: Make sure that the MySQL service stops and the data and log directories are emptied, such as # rm-rf/data/mysql/innodb_data/*# rm-rf/data/, before recovery begins mysql/data/*# 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=/data/backup # Your backup home directory fullbackup_dir= $BACKUP _dir/full # Directory for full library backup incrbackup_dir= $BACKUP _dir/incre # Incremental backup directory memory=2048m # memory limit used when restoring errorlog= ' grep-i ' ^log-error ' $MY _cnf | Cut-d =-F 2 ' mysqld_safe=/data/mysql/bin/mysqld_safemysql_port=33107############################################# ################################ #显示错误 ########################################################################## # # #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 detects if [!-X $INNOBACKUPEX _path ]; Then error "$INNOBACKUPEX _path in the specified path does not exist, please confirm whether 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 "How to use: $ $ Use to restore the absolute path of the directory" fi if [!-D $]; Then error "Specified backup directory: $" does not exist. " fiportnum00= ' Netstat-lnt|grep ${mysql_port}|wc-l ' if [$PORTNUM xx = 1];thenecho-e ' \e[31m Note:----------------------- -------------------. \e[m "#红色echo-E" \e[31m MySQL is running, please turn off MySQL. \e[m "#红色echo-E" \e[31m Note:------------------------------------------. \e[m "#红色exit 0fi ################ Determine whether to restore the incremental backup portion or all ############### #echo "*************************************************************************" Echo-e "\e[31m the increment file name that you entered should be the next file for the delta file name cutoff. \e[m" #红色echo "**********************"Ipname= ' read-p" input cutoff incremental backup name [default all]: "Ipnameechoecho" input cutoff incremental backup named : $ipname "input_value=$1intpu_res= ' echo ${input_value%/*} ' # Some info outputecho"----------------------------" Echoecho "$0:mysql Restore Script" start_restore_time= ' date +%f ' '%T '%w ' echo ' database restore started in: $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 '" echoelse## ############# #判断还原部分增量备份还是所有增量备份 ############### #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 ' #查找最后一个增量备份文件echo ' restore will start at the full standby $full to the end of the 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, the incremental backup is applied to the full-standby directory, in the order of incremental backups, from old to newest for i in ' Find $PARENT _dir/$FULL-mindepth 1-maxdepth 1-type d-printf "%P \ n "| Sort-n ';d o# judge the latest fully prepared 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 LSN value of $fullbackup: ${fetch_full_lastlsn} ' echo ' incremental backup $i LSN value: ${fetch_ INCRE_LASTLSN} "If [" ${fetch_incre_lastlsn} "-eq" ${fetch_full_lastlsn} "];thenecho" ******************************* "Echo" LSNEqual, no need for prepare! " echo "*****************************************" Echobreakelseecho "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 ##### #判断LSNdoneelseerror "Unknown backup type" fielsefull= ' ls-t $FULLBACKUP _dir |head-1 ' fullbackup= $FULLBACKUP _di r/$FULLecho "Prepare: Full backup ..." echo "*****************************" $INNOBACKUPEX _path--defaults-file= $MY _ CNF--apply-log--redo-only--use-memory= $MEMORY $FULLBACKUP > $TMP _log 2>&1check_innobackupex_failipt= ' Stat-c=%z $PARENT _dir/$FULL/$ipname |cut-d =-F 2 ' echo ' restores the specified incremental catalog file $ipname era 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 taken by the for loop is the input filename, and if yes stops the loop if ["$i" = "$ipname"]; Thenbreak;elsef01= ' stat-c=%z $PARENT _dir/$FULL/$i |cut-d =-F 2 ' if ["$f"-le "$ipt"]; thenif [$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 start from the full standby $full to the end of the incremental $ipname. " echo# Judging the latest fully prepared 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 the 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 ' fully backs up the current LSN:${FETCH_FULL_LASTLSN} "Elseerror" Unknown backup type "Fielse echo" to find the 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 "#红色breakfifidonefi ######### ###### #判断还原部分增量备份还是所有增量备份 ############### #fiecho echo "Prepare: full backup and rollback of those uncommitted transactions ..." $INNOBACKUPEX the _pATH--defaults-file= $MY _cnf--apply-log--use-memory= $MEMORY $FULLBACKUP > $TMP _log 2>&1check_innobackupex_ Failecho "*****************************" echo "Database restore ... Please wait a moment "echo" ***************************** "$INNOBACKUPEX _path--defaults-file= $MY _cnf--copy-back $FULLBACKUP > $TMP _log 2>&1check_innobackupex_fail rm-f $TMP _logecho "1. Congratulations, restore success!." echo "*****************************" #修改目录权限echo "Modify the permissions of the MySQL directory." mysqlcnf= "/data/mysql/my.cnf" mysqldatadir= ' Grep-i "^basedir" $mysqlcnf |cut-d =-F 2 "Echo ' Chown-r mysql:mysql ' ${mysq Ldatadir} ' echo ' 2. Permissions modified successfully! " echo "*****************************" #自动启动mysqlINIT_NUM =1if [!-x $MYSQLD _safe]; Then echo "MySQL installation when startup files are not installed to $mysqld_safe or no Execute permissions" Exit 1 #0是执行成功, 1 is unsuccessful Elseecho "start the native MYSQL port for: $MYSQL _port Service" $MYSQLD _ SAFE--defaults-file= $MY _cnf >/dev/null &while [$INIT _num-le] do portnum= ' Netstat-lnt|grep $ {mysql_port}|wc-l ' echo ' MYSQL startup ....        Please wait a moment ... "sleep if [$PORTNUM = 1];THENECHO-E "\e[32m MySQL * * * * * * * * * * * * * \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 0fiend_restore_time= ' date +%f '%T '%w ' echo" database restore completed: $END _restore_time "Exit 0****** Execution Result: **************** #./restore.sh/backup/incre/ Enter a cutoff incremental backup name [default all]:2015-04-10_14-10-49 input cutoff incremental backup name: 2015-04-10_14-10-49----------------------------./restore.sh: MySQL Restore script database restore started at: 2015-04-10 14:24:51 5Prepare: Full backup ... ***************************** restore the specified Delta catalog file 2015-04-10 The epoch time for the _14-10-49 is: 1428646269 The restore will start from the full standby 2015-04-10_14-05-40 to the end of the incremental 2015-04-10_14-10-49. LSN of the lsn:344693290 incremental backup for a full backup: 413297865 Prepare: Incremental backup set 2015-04-10_14-08-19........***************************** full backup of current LSThe n:413297865 restore will start with the full standby 2015-04-10_14-05-40 to the end of the incremental 2015-04-10_14-10-49. lsn:481905295 Prepare for a full backup lsn:413297865 incremental backup: Incremental backup set 2015-04-10_14-09-07........***************************** full backup the current lsn:481905295 restore will start with the full standby 2015-04-10_14-05-40 To the incremental 2015-04-10_14-10-49 end. lsn:550515490 of the lsn:481905295 incremental backup for a full backup Prepare: Incremental backup set 2015-04-10_14-10-49........********* Full backup of the current lsn:550515490 find incremental backup file complete. --------------------------------------------fully back up the final lsn:550515490-------------------------------------------- Prepare: A full backup and rollback of those uncommitted transactions ... ***************************** database Restore in ..... Just a moment, please *****************************1. Congratulations, restore success!. Modify the permissions of the MySQL directory. 2. Permissions modified successfully!***************************** start the native MySQL port for: 3306 service MySQL Startup .... Please wait a moment ... mysql startup .... Please wait a moment ... mysql startup ....   Please wait a moment ... mysql * * * * * * * * * *

MySQL Recovery script