Xtrabackup automatic restore script v2 and xtrabackup script v2

Xtrabackup automatic restore script v2 and xtrabackup script v2
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.


2. If the full backup is/backup/full/2015-04-08_15-14-33, the full backup directory name will be 2015-04-08_15-14-33
The directory name of the Incremental backup under/backup/incret. 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 automatically finds the latest Incremental Backup directory named "Full backup" and "Full backup", and backs up the Incremental Backup Directory.
Apply logs to full backup in sequence, and restore the logs.


4. This version adds whether to restore all incremental backups. By default, all incremental backups are used.


**************************************** **************************************** ****************
Script
**************************************** **************************************** ****************


#! /Bin/sh
#
# Usage:
#./Restore. sh/Incremental backup parent directory
# Ocpyang@126.com


# NOTE: Make sure that the mysql service is stopped and the data and log directories are cleared before the recovery starts, as shown in figure
# Rm-rf/usr/local/mysql/innodb_data /*
# Rm-rf/usr/local/mysql/data /*
# Rm-rf/usr/local/mysql/mysql_logs/innodb_log /*






INNOBACKUPEX = innobackupex
INNOBACKUPEX_PATH =/usr/local/xtrabackup/bin/$ INNOBACKUPEX
TMP_LOG = "/var/log/restore. $. log"
MY_CNF =/usr/local/mysql/my. cnf
BACKUP_DIR =/backup # Your backup home directory
FULLBACKUP_DIR = $ BACKUP_DIR/full # full Database Backup Directory
INCRBACKUP_DIR = $ BACKUP_DIR/incre# directory of Incremental Backup
MEMORY = 4096 M # MEMORY limit used for restoration
ERRORLOG = 'grep-I "^ log-error" $ MY_CNF | cut-d =-f 2'
MYSQLD_SAFE =/usr/local/mysql/bin/mysqld_safe
MYSQL_PORT = 3306




######################################## #####################################


# Display Error


######################################## #####################################


Error ()
{
Echo "$1" 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 "---------- $ error output of INNOBACKUPEX ----------"
Cat $ TMP_LOG
# Keep a Detailed backup log
Logfiledate = restore. 'date when policyymm1_d1_hsf-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. Check whether the link is installed or correct ."
Fi





If [! -D $ BACKUP_DIR]; then
Error "the backup directory $ BACKUP_DIR does not exist. Create a backup home directory $ BACKUP_DIR"
Fi





If [$ #! = 1]; then
Error "Usage: $0 indicates the absolute path of the restored directory"
Fi





If [! -D $1]; then
Error "the specified Backup Directory: $1 does not exist ."
Fi






PORTNUM00 = 'netstat-lnt | grep $ {MYSQL_PORT} | wc-l'
If [$ PORTNUM00 = 1];
Then
Echo-e "\ e [31 m NOTE: --------------------------------------------. \ 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 0
Fi


############### Determine whether the restored Incremental backup is full ################
Ipname =''
Read-p "input end Incremental Backup name [all by default]:" ipname
Echo
Echo "input end 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 restoration started at: $ START_RESTORE_TIME"
Echo










# PARENT_DIR = 'dirname $ {intpu_res }'
PARENT_DIR =$ {intpu_res}


If [$ PARENT_DIR = $ FULLBACKUP_DIR]; then
FULL = 'LS-t $ FULLBACKUP_DIR | head-1'
FULLBACKUP =$ {intpu_res}/$ FULL
Echo "Restore full backup: 'basename $ FULLBACKUP '"
Echo


Else

############### Determine whether the restored Incremental backup is full ################
If ["$ ipname" = '']; then
If [$ PARENT_DIR = $ INCRBACKUP_DIR]; then
FULL = 'LS-t $ FULLBACKUP_DIR | head-1'
FULLBACKUP = $ FULLBACKUP_DIR/$ FULL
If [! -D $ FULLBACKUP]; then
Error "Full backup: $ FULLBACKUP does not exist ."
Fi
INCR = 'LS-t $ INCRBACKUP_DIR/$ FULL/| sort-nr | head-1' # Find the last Incremental Backup File
Echo "restoration starts from FULL backup $ FULL and ends with incremental $ INCR ."
Echo
Echo "Prepare: Full backup ..........."
Echo "*****************************"
$ INNOBACKUPEX_PATH -- defaults-file = $ MY_CNF -- apply-log -- redo-only -- use-memory = $ MEMORY $ FULLBACKUP> $ TMP_LOG 2> & 1
Check_innobackupex_fail



# Prepare Incremental Backup set: applies Incremental backup to the full backup directory. Incremental backup is performed from the old backup time to the latest backup time.
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

Fetch_full_lastlsn = 'grep-I "^ last_lsn" $ {check_full_lastlsn} | cut-d =-f 2'


###### Determine the lsn of the first Incremental Backup in Incremental Backup
# 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: $ {fetch_full_lastlsn }"
Echo "Incremental Backup $ I's LSN value: $ {fetch_incre_lastlsn }"

If ["$ {fetch_incre_lastlsn}"-eq "$ {fetch_full_lastlsn}"]; then
Echo "************************************** ***"
Echo "LSN equal, prepare not required! "
Echo "************************************** ***"
Echo
Break



Else
Echo "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> & 1
Check_innobackupex_fail
 
If [$ INCR = $ I]; then
Break
Fi

Fi
###### Determine the LSN
Done

Else
Error "unknown Backup Type"
Fi


Else
FULL = 'LS-t $ FULLBACKUP_DIR | head-1'
FULLBACKUP = $ FULLBACKUP_DIR/$ FULL
Echo "Prepare: Full backup ..........."
Echo "*****************************"
$ INNOBACKUPEX_PATH -- defaults-file = $ MY_CNF -- apply-log -- redo-only -- use-memory = $ MEMORY $ FULLBACKUP> $ TMP_LOG 2> & 1
Check_innobackupex_fail





Ipt = 'stat-c = % Z $ PARENT_DIR/$ FULL/$ ipname | cut-d =-f 2'
Echo "the epoch time for restoring the specified incremental directory file $ ipname is: $ ipt"
For I in 'Find $ PARENT_DIR/$ FULL-mindepth 1-maxdepth 1-type d-printf "% P \ n" | sort-n ';
Do
F01 = 'stat-c = % Z $ PARENT_DIR/$ FULL/$ I | cut-d =-f 2'
If ["$ f01"-le "$ ipt"]; then


If [$ PARENT_DIR = $ INCRBACKUP_DIR]; then
If [! -D $ FULLBACKUP]; then
Error "Full backup: $ FULLBACKUP does not exist ."
Fi
# INCR = 'LS-t $ INCRBACKUP_DIR/$ FULL/| sort-nr | head-1'
Echo "restoration starts from FULL backup $ FULL and ends with incremental $ ipname ."
Echo


# 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

Fetch_full_lastlsn = 'grep-I "^ last_lsn" $ {check_full_lastlsn} | cut-d =-f 2'


###### Determine the lsn of the first Incremental Backup in Incremental Backup
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 "fully backed up LSN :$ {fetch_full_lastlsn }"
Echo "Incremental Backup LSN :$ {fetch_incre_lastlsn }"
If ["$ {fetch_incre_lastlsn}"-eq "$ {fetch_full_lastlsn}"]; then
Echo "************************************** ***"
Echo "LSN does not require prepare! "
Echo "************************************** ***"
Echo
Break
Else
Echo "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> & 1
Check_innobackupex_fail

Fi

###### Determine the LSN
Check_full_lastlsn = $ FULLBACKUP/xtrabackup_checkpoints

Fetch_full_lastlsn = 'grep-I "^ last_lsn" $ {check_full_lastlsn} | cut-d =-f 2'
Echo "fully back up the current LSN: $ {fetch_full_lastlsn }"


Else
Error "unknown Backup Type"
Fi
Else
Echo "finding the Incremental backup file is complete ."
Check_full_lastlsn = $ FULLBACKUP/xtrabackup_checkpoints
Fetch_full_lastlsn = 'grep-I "^ last_lsn" $ {check_full_lastlsn} | cut-d =-f 2'
Echo-e "\ e [31 m ---------------------------------------------- \ e [m" # Red
Echo-e "\ e [31 m complete backup of the final LSN: $ {fetch_full_lastlsn} \ e [m" # Red
Echo-e "\ e [31 m ---------------------------------------------- \ e [m" # Red
Break
Fi
Done


Fi
################ Determine whether the Incremental backup is restored or not ################



Fi




Echo
Echo "prepare: complete backup and rollback of uncommitted transactions ..........."
$ INNOBACKUPEX_PATH -- defaults-file = $ MY_CNF -- apply-log -- use-memory = $ MEMORY $ FULLBACKUP> $ TMP_LOG 2> & 1
Check_innobackupex_fail




Echo "*****************************"
Echo "restoring database... please wait"
Echo "*****************************"


$ INNOBACKUPEX_PATH -- defaults-file = $ MY_CNF -- copy-back $ FULLBACKUP> $ TMP_LOG 2> & 1
Check_innobackupex_fail



Rm-f $ TMP_LOG
Echo "1. Congratulations, restored successfully !. "
Echo "*****************************"




# Modify directory permissions
Echo "Modify mysql directory permissions ."
Mysqlcnf = "/usr/local/mysql/my. cnf"
Mysqldatadir = 'grep-I "^ basedir" $ mysqlcnf | cut-d =-f 2'
'Echo 'chown-R mysql: mysql' $ {mysqldatadir }'
Echo "2. Permission modification successful! "
Echo "*****************************"




# Automatically start mysql


INIT_NUM = 1
If [! -X $ MYSQLD_SAFE]; then
Echo "the mysql Startup file is not installed in $ MYSQLD_SAFE or has no execution permission"
Exit 1 #0 indicates that the execution is successful, and 1 indicates that the execution is unsuccessful.
Else
Echo "Start the mysql port of the Local Machine: $ MYSQL_PORT service"
$ MYSQLD_SAFE -- defaults-file = $ MY_CNF>/dev/null &
While [$ INIT_NUM-le 8]
Do
PORTNUM = 'netstat-lnt | grep $ {MYSQL_PORT} | wc-l'
Echo "mysql is starting... please wait ..."
Sleep 5
If [$ PORTNUM = 1];
Then
Echo-e "\ e [32 m mysql ***** started successfully ***** \ e [m"
Exit 0
Fi
INIT_NUM = $ ($ INIT_NUM + 1 ))
Done
Echo-e "\ e [31 m mysql startup failed or startup time is too long. Check the error log 'echo 'cat' $ {ERRORLOG} '\ e [m"
Echo "************************************** ***"
Exit 0
Fi








END_RESTORE_TIME = 'date + % f'' % t'' % W'
Echo "database restoration completed at: $ END_RESTORE_TIME"
Exit 0








**************************************** **************************************** ****************
Execution result:
**************************************** **************************************** ****************
 
#./Restore. sh/backup/incre/
Enter the end Incremental Backup name [all by default]: 2015-04-10_14-10-49


Enter the end Incremental Backup name: 2015-04-10_14-10-49
----------------------------


./Restore. sh: MySQL restore script
Database restoration started at: 14:24:51 5


Prepare: Full backup ...........
*****************************
The Epoch time for restoring the specified incremental directory file 2015-04-10_14-10-49 is: 1428646269
The restoration starts from full backup 2015-04-10_14-05-40 and ends with incremental 2015-04-10_14-10-49.


Full backup LSN: 344693290
Incremental Backup LSN: 413297865
Prepare: Incremental Backup set 2015-04-10_14-08-19 ........
*****************************
Full backup of the current LSN: 413297865
The restoration starts from full backup 2015-04-10_14-05-40 and ends with incremental 2015-04-10_14-10-49.


Full backup LSN: 413297865
Incremental Backup LSN: 481905295
Prepare: Incremental Backup set 2015-04-10_14-09-07 ........
*****************************
Full backup of the current LSN: 481905295
The restoration starts from full backup 2015-04-10_14-05-40 and ends with incremental 2015-04-10_14-10-49.


Full backup LSN: 481905295
Incremental Backup LSN: 550515490
Prepare: Incremental Backup set 2015-04-10_14-10-49 ........
*****************************
Full backup of the current LSN: 550515490
Incremental backup file found.
--------------------------------------------
Complete backup of the final LSN: 550515490
--------------------------------------------


Prepare: complete backup and rollback of uncommitted transactions ...........
*****************************
Restoring the database... please wait.
*****************************
1. Congratulations, the restoration is successful !.
*****************************
Modify the permission of the mysql directory.
2. the permission is successfully modified!
*****************************
Start the service with the mysql port 3306 on the local machine
Starting mysql... please wait...
Starting mysql... please wait...
Starting mysql... please wait...
Mysql is successfully started ****