Xtrabackup Auto-Restore v2

Xtrabackup Auto-Restore v2

************************************************************************************************

Application Scenarios:

************************************************************************************************

1. The backup directory is/backup/full and/backup/incre schema, the former is fully prepared, the latter saves the incremental backup

2. If the full/backup/full/2015-04-08_15-14-33 will be fully prepared directory name 2015-04-08_15-14-33

The directory name as an incremental backup under/backup/incre/. This design is designed to be as long as the full-time, then

The incremental backup after this fully-prepared file is not yet available.

3. When restoring, the script automatically finds the latest fully-prepared and up-to-date, fully-named incremental backup directory, and adds an incremental backup

Apply the log to full standby in order of precedence and finalize the restore.

4. This version increases the recovery of all incremental backups and, by default, all incremental backups.

************************************************************************************************

Script

************************************************************************************************

#!/bin/sh

#

# How to use:

#./restore.sh/Incremental Backup parent directory

#[email protected]

#NOTE: Make sure that the MySQL service is stopped and the data and log directories are emptied before recovery starts, such as

# 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-Library backup directory

incrbackup_dir= $BACKUP _dir/incre # Incremental backup directory

memory=4096m # Number of memory limits to use when restoring

Errorlog= ' Grep-i "^log-error" $MY _cnf |cut-d =-F 2 '

Mysqld_safe=/usr/local/mysql/bin/mysqld_safe

mysql_port=3306

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

#显示错误

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

Error ()

{

echo "$" 1>&2

Exit 1

}

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

# Check for 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 "How to: Use the absolute path to restore a directory"

Fi

if [!-D $]; Then

Error "Specified backup directory: $" does not exist. "

Fi

portnum00= ' Netstat-lnt|grep ${mysql_port}|wc-l '

if [$PORTNUM 00 = 1];

Then

Echo-e "\e[31m Note:------------------------------------------. \e[m" #红色

Echo-e "\e[31m MySQL is in the running state, please turn off MySQL. \e[m "#红色

Echo-e "\e[31m Note:------------------------------------------. \e[m" #红色

Exit 0

Fi

############### #判断还原增量备份部分还是所有 ################

Ipname= "

Read-p "Input cutoff incremental backup name [default all]:" Ipname

Echo

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]; Then

Full= ' ls-t $FULLBACKUP _dir |head-1 '

fullbackup=${intpu_res}/$FULL

echo "Restore full backup: ' BaseName $FULLBACKUP '"

Echo

Else

############### #判断还原增量备份部分还是所有 ################

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 "All ready: $FULLBACKUP not present."

Fi

Incr= ' ls-t $INCRBACKUP _dir/$FULL/|sort-nr | Head-1 ' #查找最后一个增量备份文件

echo "Restore will start at full standby $full to the end of 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, applying incremental backups to the full standby directory, in the order of incremental backups, 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

#判断最新全备的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}"];then

echo "*****************************************"

echo "LSN equal, no prepare! 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

##### #判断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 "restore the specified delta directory file $ipname in the epoch Time: $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 ["$f"-le "$ipt"]; Then

if [$PARENT _dir = $INCRBACKUP _dir]; Then

if [!-D $FULLBACKUP]; Then

Error "All ready: $FULLBACKUP not present."

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

#判断最新全备的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 of Lsn:${fetch_full_lastlsn}"

echo "Incremental backup of 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

##### #判断LSN

check_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}"

Else

Error "Unknown backup type"

Fi

Else

echo "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 of final LSN:${FETCH_FULL_LASTLSN} \e[m" #红色

Echo-e "\e[31m--------------------------------------------\e[m" #红色

Break

Fi

Done

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 ... Just a moment, please.

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= "/USR/LOCAL/MYSQL/MY.CNF"

Mysqldatadir= ' Grep-i "^basedir" $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 execution unsuccessful

Else

echo "Start native MYSQL Port: $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 startup .... Please wait a moment ... "

Sleep 5

if [$PORTNUM = 1];

Then

ECHO-E "\e[32m MySQL * * * * * Startup Success * * * * \e[m"

Exit 0

Fi

init_num=$ (($INIT _num + 1))

Done

Echo-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

************************************************************************************************

Execution Result:

************************************************************************************************

#./restore.sh/backup/incre/

Enter a cutoff incremental backup name [default all]:2015-04-10_14-10-49

The input cutoff incremental backup is named: 2015-04-10_14-10-49

----------------------------

./restore.sh:mysql Restore Script

Database restore started at: 2015-04-10 14:24:51 5

Prepare: Full backup .....

*****************************

Restores the specified delta catalog file 2015-04-10_14-10-49 in the epoch Time: 1428646269

The restore will start at full standby 2015-04-10_14-05-40 to the end of the incremental 2015-04-10_14-10-49.

lsn:344693290 for full backups

lsn:413297865 for incremental backups

Prepare: Incremental backup Set 2015-04-10_14-08-19 .....

*****************************

Full backup of current lsn:413297865

The restore will start at full standby 2015-04-10_14-05-40 to the end of the incremental 2015-04-10_14-10-49.

lsn:413297865 for full backups

lsn:481905295 for incremental backups

Prepare: Incremental backup Set 2015-04-10_14-09-07 .....

*****************************

Full backup of current lsn:481905295

The restore will start at full standby 2015-04-10_14-05-40 to the end of the incremental 2015-04-10_14-10-49.

lsn:481905295 for full backups

lsn:550515490 for incremental backups

Prepare: Incremental backup Set 2015-04-10_14-10-49 .....

*****************************

Full backup of current lsn:550515490

Find the incremental backup file complete.

--------------------------------------------

Complete backup of the final lsn:550515490

--------------------------------------------

Prepare: A full backup and rollback of those uncommitted transactions .....

*****************************

Database Restore in ... Please wait a moment.

*****************************

1. Congratulations, restore success!.

*****************************

Modify the permissions of the MySQL directory.

2. Permissions modified successfully!

*****************************

Start the native MySQL port as: 3306 Service

MySQL startup .... Please wait a moment ...

MySQL startup .... Please wait a moment ...

MySQL startup .... Please wait a moment ...

MySQL * * * * STARTS successfully * * *

This article is from the "Ocpyang" blog, make sure to keep this source http://ocpyang.blog.51cto.com/3401739/1631027

Xtrabackup Auto-Restore v2