Xtrabackup automatic restore script and xtrabackup script

Xtrabackup automatic restore script and xtrabackup script

Xtrabackup Automatic Restoration ************************************* **************************************** * ******************* application scenarios: **************************************** **************************************** * **************** 1. the backup directory is/backup/full and/backup/incret. The former stores full backup, and the latter stores Incremental backup. for example, if the full backup is/backup/full/2015-04-08_15-14-33, the full backup directory name 2015-04-08_15-14-33 is used as the Directory Name of the Incremental backup under/backup/inre. The original intention of this design is that as long as the full backup does not expire, the Incremental backup after the full backup file does not expire. 3. during restoration, the script will automatically find the latest Incremental Backup directory named full backup and the latest full backup directory, and apply logs to the full backup in sequence, and finally restore the directory. **************************************** **************************************** *********************** **************************************** *********************************#! /Bin/sh # usage :#. /restore. sh/Incremental backup parent directory # ocpyang@126.com # NOTE: Make sure mysql service is stopped and data and log directories are cleared before recovery starts, for example, # rm-rf/usr/local/mysql/innodb_data/* # rm-rf/usr/local/mysql/data/* # rm-rf/usr/local/mysql/ mysql_logs/innodb_log/* INNOBACKUPEX = innobackupexINNOBACKUPEX_PATH =/usr/local/xtrabackup/bin/$ INNOBACKUPEXTMP_LOG = "/var/log/restore. $. log "MY_CNF =/usr/local/mysql/my. cnfBACKUP_DIR =/backup # Your backup home directory FULLBACKUP_DIR = $ BA CKUP_DIR/full # full-Database Backup Directory INCRBACKUP_DIR = $ BACKUP_DIR/incre# Incremental Backup Directory MEMORY = 4096 M # MEMORY limit used during restoration ERRORLOG = 'grep-I "^ log-error "$ MY_CNF | cut-d =-f 2 'mysqld _ SAFE =/usr/local/mysql/bin/mysqld_safeMYSQL_PORT = 3306 ######### ######################################## ############################# display errors ######### ######################################## ########################### error () {echo "$1" 1> & 2 exit 1 }### ######################################## ################################### Check the error output of innobackupex # ######################################## #################################### check_innobackupex_fail () {if [-z "'Tail-2 $ TMP_LOG | grep' completed OK! ''"]; Then echo "$ INNOBACKUPEX command execution failed :"; echo "---------- $ error output of INNOBACKUPEX ----------" cat $ TMP_LOG # Keep the detailed log of a backup logfiledate = restore. 'date required policyymm1_d1_h1_m'.txt cat $ TMP_LOG>/backup/$ logfiledate rm-f $ TMP_LOG exit 1 fi} # Check if [! -X $ INNOBACKUPEX_PATH]; then error "$ INNOBACKUPEX_PATH does not exist in the specified path. Check whether the link is installed or correct." fi if [! -D $ BACKUP_DIR]; then error "Backup directory $ BACKUP_DIR does not exist." fi if [$ #! = 1]; then error "Usage: $0 use the absolute path of the restored directory" fi if [! -D $1]; then error "the specified Backup Directory: $1 does not exist. "fiPORTNUM00 = 'netstat-lnt | grep $ {MYSQL_PORT} | wc-l' if [$ PORTNUM00 = 1]; thenecho-e' \ e [31 m NOTE: success :------------------------------------------. \ e [m' # Red echo-e '\ e [31 m mysql is running. Please disable mysql. \ e [m' # Red echo-e '\ e [31 m NOTE :------------------------------------------. \ e [m' # Red exit 0fiinput_value = $ 1intpu_res = 'echo $ {input_value %/*} '# Some info outputecho "------------- --------------- "Echoecho" $0: MySQL restoration script "START_RESTORE_TIME = 'date + % F' % t' % w'echo" database restoration started: $ START_RESTORE_TIME "echo # PARENT_DIR = 'dirname $ {intpu_res} 'parent _ DIR =$ {intpu_res} if [$ PARENT_DIR = $ FULLBACKUP_DIR]; thenFULLBACKUP =$ {intpu_res} echo "Restore full backup: 'basename $ FULLBACKUP '" echoelseif [$ PARENT_DIR = $ INCRBACKUP_DIR]; thenFULL = 'LS-t $ FULLBACKUP_DIR | head-1 'FULLBACKUP = $ FULLBACKUP_DIR/$ FULLif [! -D $ FULLBACKUP]; thenerror "Full backup: $ FULLBACKUP does not exist. "fiINCR = 'LS-t $ INCRBACKUP_DIR/$ FULL/| head-1 'echo" restoration starts from FULL backup $ FULL and ends with incremental $ INCR. "echoecho" Prepare: complete backup set ........... "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, applies Incremental backup to the full backup directory. Incremental backup is performed from the old to the latest for I in 'Find $ PARENT_DIR/$ FULL-mindepth 1-maxdepth 1-type d-printf "% P \ n" | sort-n '; do # identify the latest full-Backup 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 placement = 'grep-I "^ last_lsn" $ {check_full_lastlsn} | cut-d =-f 2' ##### judging Incremental Backup LSNcheck_incre_file = 'Find $ PARENT_DIR/$ FULL-mindepth for the first Incremental Backup 1-maxdepth 1-type d-printf "% P \ n" | sort-n | head-1 'check_incre_lastlsn = $ PARENT_DIR/$ FULL/$ I/xtrabackup_checkpoints Limit = 'grep -I "^ last_lsn" $ {check_incre_lastlsn} | cut-d =-f 2 'echo "fully backed up LSN: $ {fetch_full_lastlsn} "echo" Incremental Backup LSN :$ {fetch_incre_lastlsn} "if [" $ {fetch_incre_lastlsn} "-eq" $ {fetch_full_lastlsn} "]; thenecho "************************************** * ** "echo" LSN No prepare required! "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 ###### identify LSNdoneelseerror "unknown Backup Type" fifiecho "prepare: The full backup set rollback not mentioned Transaction ........... "$ INNOBACKUPEX_PATH -- defaults-file = $ MY_CNF -- apply-log -- use-memory = $ MEMORY $ FULLBACKUP> $ TMP_LOG 2> & 1check_innobackupex_failecho "********* * ******************* "echo" database restoration... please wait for "echo" **************************** "$ INNOBACKUPEX_PATH -- defaults- file = $ MY_CNF -- copy-back $ FULLBACKUP> $ TMP_LOG 2> & 1check_innobackupex_fail rm-f $ TMP_LOGecho "1. congratulations! The restoration is successful !. "Echo" **************************** "# modify the directory permission echo" Modify mysql directory permission. "mysqlcnf ="/usr/local/mysql/my. cnf "mysqldatadir = 'grep-I" ^ basedir "$ mysqlcnf | cut-d =-f 2 ''echo 'chown-R mysql: mysql '$ {mysqldatadir}' echo "2. permission modified! "Echo" **************************** "# automatically start mysqlINIT_NUM = 1if [! -X $ MYSQLD_SAFE]; then echo "when mysql is installed, the startup file is not installed to $ MYSQLD_SAFE or has no execution permission." exit 1 #0 indicates that the execution is successful, 1 is the execution fails elseecho "Start the local mysql port is: $ MYSQL_PORT service "$ MYSQLD_SAFE -- defaults-file = $ MY_CNF>/dev/null & while [$ INIT_NUM-le 6] do PORTNUM = 'netstat-lnt | grep $ {MYSQL_PORT} | wc-l 'echo "Starting mysql .... please wait... "sleep 5 if [$ PORTNUM = 1]; then echo" mysql ***** started successfully ***** "exit 0 fi INIT_NUM =$ ($ INIT_NUM + 1 )) done echo-e "mysql startup failed or The startup time is too long, check the error log 'echo 'cat' $ {ERRORLOG} '"echo "*********************** * ***************** "exit 0fiEND_RESTORE_TIME = 'date + % f'' % t' % w'echo" database Restoration completed on: $ END_RESTORE_TIME "exit 0 *********************************** **************************************** * ********************* execution result: **************************************** **************************************** * *************** the execution result is as follows: #. /restore. sh/backu P/incret /----------------------------. /restore. sh: MySQL restoration script database restoration starts at: 15:17:14 3 the restoration will start from full backup 2015-04-08_15-14-33 and end with incremental. prepare: complete backup set ........... * **************************** full backup LSN: 62974601 Incremental Backup LSN: 124278446 Prepare: Incremental Backup set ........ * **************************** full backup LSN: 124278446 Incremental Backup LSN: 185584722 Prepare: Incremental Backup set ........ **************** * ************ Prepare: Full backup set rollback of those uncommitted transactions ........... * ***************************** restoring the database... please wait ****************************** 1. congratulations! The restoration is successful !.. 2. permission modified! * **************************** Start the service with the local mysql port 3306. starting mysql .... please wait... 3. starting mysql .... please wait... 3. starting mysql .... please wait... mysql is successfully started ****