tiench
1
I have a script perform backup full and incremental using innobackupex with “–incremental-lsn”, full and incremental backup is successfully but restored incremental unsuccessfully - the entire data is restored to full backup, not incremental.
According the guide: [url]https://www.percona.com/blog/2014/04/24/encrypted-and-incremental-mysql-backups-with-percona-xtrabackup/[/url]
Script backup full:
[INDENT]innobackupex --user=$mysqlUser --password=$mysqlPwd --extra-lsndir="$backupDirLSNS"/"$dateNow" --no-timestamp --encrypt=AES256 \
--encrypt-key-file="$backupDir"/backups.key --rsync "$backupDirFull"/"$dateNow" > "$backupLog"/"$dateNow".backup.full 2>&1 \
&& echo $dateNowOS > "$backupDir"/latest_full[/INDENT]
and incremental:
[INDENT]lastFull=`cat "$backupDir"/latest_full`
lastFullDir=`date -d@"$lastFull" '+%Y-%m-%d_%H-%M-%S'`
last_lns=$(cat $backupDirLSNS/$lastFullDir/xtrabackup_checkpoints | grep to_lsn | cut -d'=' -f2)
innobackupex --user=$mysqlUser --password=$mysqlPwd --extra-lsndir="$backupDirLSNS"/"$dateNow" --no-timestamp --encrypt=AES256 \
--encrypt-key-file="$backupDir"/backups.key --incremental --incremental-lsn="$last_lns" --incremental-basedir="$backupDirFull"/"$lastFullDir" \
--rsync "$backupDirInc"/"$dateNow"[/INDENT]
Script restore Incemental backup:
[INDENT]declare -a arr_inc
binc=`ls -l $backupDirInc | grep '^d' | awk '{print$9}'`
for i in $binc
do
arr_inc=("${arr_inc[@]}" "$i")
done
read x
brinc=`echo ${arr_inc[x-1]}`
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo "Do you want to continue restore backup [$brinc] (yes/no)?"
read answer
if [ "$answer" == "yes" ]; then
binc=`cut -d_ -f1 <<<"$brinc"`
bfull=`ls -l $backupDirFull | grep '^d' | awk '{print$9}'`
for i in $bfull
do
tmpfull=`echo $i | cut -d"_" -f1`
if [ "$binc" == "$tmpfull" ]; then
brfull=$i
fi
done
#Decrypt backup
find "$backupDirInc"/"$brinc" -name "*.xbcrypt" -type f -exec bash -c 'f={} && nf=${f%.xbcrypt} && cat {} | xbcrypt -da AES256 -f /root/backup/backups.key -o ${nf} && rm {}' \;
#Preparing Backup Incremental
innobackupex --user=$mysqlUser --password=$mysqlPwd --apply-log-only --redo-only --incremental-dir="$backupDirInc"/"$brinc"/ "$backupDirFull"/"$brfull"/ > "$backupLog"/"$dateNow".prepare.inc 2>&1
#Restoring Backup Incremental
echo -e "Process is running, please wait ..."
service mysqld stop > /dev/null 2>&1
mv $dataDir/* $tmpDataDir
innobackupex --user=$mysqlUser --password=$mysqlPwd --copy-back "$backupDirInc"/"$brinc" > "$backupLog"/"$dateNow".restore.inc 2>&1
if grep -q "completed OK" "$backupLog"/"$dateNow".restore.inc; then
chown -R mysql:mysql $dataDir
service mysqld start
echo "Retore Backup Incremental completed OK"
else
echo "Retore Backup Incremental FAILED"
fi
else
exit 1;
fi[/INDENT]
Somebody help me this issue.
tiench
2
All script have updated, but still this issue.
#!/bin/bash
# Owner: Tien Cao
################################################################
#Define var
backupDir=/root/backup
backupDirFull=/root/backup/full
backupDirInc=/root/backup/inc
restoreDirInc=/root/backup/restore/inc
backupDirLSNS=/root/backup/lsns
keyBackup=/root/backup/backups.key
logDir=/root/backup/logs
backupDirComp=/root/backup/compress/
backupLog=/root/backup/logs
tmpDataDir=/tmp/mysql
dataDir=/var/lib/mysql
hoursBeforeFull=24
mysqlUser=bkpuser
mysqlPwd=tiencao@123
ftpUser=xtra-ftp
ftpPwd=tiench@123
ftpHost=171.244.17.158
keepDays=30
dateNow=`date +%Y-%m-%d_%H-%M-%S`
dateNowOS=`date +%s`
keepDays=30
delDay=`date -d "-$keepDays days" +%Y-%m-%d`
USER_ARGS=" --user=$mysqlUser --password=$mysqlPwd"
ARGS=" $USER_ARGS --extra-lsndir=$backupDirLSNS/$dateNow --no-timestamp --encrypt=AES256 --encrypt-key-file=$keyBackup --rsync"s
compfull(){
cd $1
tar -czf $2.full.tar.gz $2
ncftpput -R -v -u $ftpUser -p $ftpPwd $ftpHost / $2.full.tar.gz
mv $2.full.tar.gz $backupDirComp
}
compinc(){
cd $1
tar -czf $2.inc.tar.gz $2
ncftpput -R -v -u $ftpUser -p $ftpPwd $ftpHost / $2.inc.tar.gz
mv $2.inc.tar.gz $backupDirComp
}
empty_dir(){
if [ ! -d "$tmpDataDir" ]; then
mkdir $tmpDataDir
else
rm -rf "$tmpDataDir"/*
fi
}
#Perform Full/Inc Backups
do_backup(){
if [[ -z "$(command -v innobackupex)" ]]; then
echo "The innobackupex executable was not found, check if you have installed percona-xtrabackup."
exit 1
fi
if [ ! -d "$backupDirFull" ]; then
echo "Backup directory $backupDirFull does not exist. Check your config and create the backup directory"
exit 1
fi
if [ ! -f $backupDir/latest_full ]; then
touch $backupDir/latest_full
fi
if [ ! -s $backupDir/latest_full ]; then
echo "Perform backup full now"
innobackupex $ARGS "$backupDirFull"/"$dateNow" > "$backupLog"/"$dateNow".backup.full 2>&1 && echo $dateNowOS > "$backupDir"/latest_full \
&& compfull $backupDirFull $dateNow > "$backupLog"/"$dateNow".ftp.full 2>&1
if grep -q "completed OK" "$backupLog"/"$dateNow".backup.full; then
echo "Backup Full Daily completed OK"
else
echo "Backup Full Daily FAILED"
fi
else
#Calculate the time since the last full backup
lastFull=`cat "$backupDir"/latest_full`
difference=$((($dateNowOS - $lastFull) / 60 / 60))
lastFullDir=`date -d@"$lastFull" '+%Y-%m-%d_%H-%M-%S'`
last_lns=$(cat $backupDirLSNS/$lastFullDir/xtrabackup_checkpoints | grep to_lsn | cut -d'=' -f2)
if [ $difference -lt $hoursBeforeFull ]; then
echo "Backup Daily Full have been performed before at $lastFullDir, system will perfom backup incremental now"
innobackupex $ARGS --incremental --incremental-lsn="$last_lns" "$backupDirInc"/"$dateNow" --incremental-basedir="$backupDirFull"/"$lastFullDir" \
> "$backupLog"/"$dateNow".backup.inc 2>&1 && compinc $backupDirInc $dateNow > "$backupLog"/"$ateNow".ftp.inc 2>&1
if grep -q "completed OK" "$backupLog"/"$dateNow".backup.inc; then
echo "Backup Incremental completed OK"
else
echo "Backup Incremental FAILED"
fi
fi
fi
}
show_full_backup(){
number=`ls -l $backupDirFull | wc -l`
bfull=`ls -l $backupDirFull | grep '^d' | awk '{print$9}'`
for i in `seq 1 $number`
do
k=0
for j in $bfull
do
k=$(expr $k + 1)
if [ $i -eq $k ]; then
echo -e "${NUMBER} $i) ${NORMAL} Backup: $j"
break
fi
done
done
}
show_inc_backup(){
number=`ls -l $backupDirInc | wc -l`
binc=`ls -l $backupDirInc | grep '^d' | awk '{print$9}'`
for i in `seq 1 $number`
do
k=0
for j in $binc
do
k=$(expr $k + 1)
if [ $i -eq $k ]; then
echo -e "${NUMBER} $i) ${NORMAL} Backup: $j"
break
fi
done
done
}
restore_full_backup(){
declare -a arr_full
bfull=`ls -l $backupDirFull | grep '^d' | awk '{print$9}'`
for i in $bfull
do
arr_full=("${arr_full[@]}" "$i")
done
echo -e "List Full Backups"
show_full_backup;
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo -e "Input the corresponding ${RED_TEXT}[number]${NORMAL} to restore"
read x
brnum=`echo ${arr_full[x-1]}`
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo -e "Do you want to continue restore backup ${RED_TEXT}[$brnum]${NORMAL} (yes/no)?"
read answer
if [ "$answer" == "yes" ]; then
echo -e "${OUT_PUT}****************************************${NORMAL}"
#Decrypt Incremental Backups
find $backupDirInc/$brnum -name "*.xbcrypt" -type f -exec bash -c 'f={} && nf=${f%.xbcrypt} && cat {} | xbcrypt -da AES256 -f /root/backup/backups.key -o ${nf} && rm {}' \;
#Preparing Full Backups
innobackupex $USER_ARGS --no-timestamp --apply-log-only --redo-ony "$backupDirFull"/"$brnum" > "$backupLog"/"$dateNow".prepare.full 2>&1 && echo $dateNow > tmpfile.txt
brfull=`cat tmpfile.txt`
echo -e "Process is running, please wait ..."
empty_dir;
service mysqld stop
mv $dataDir/* $tmpDataDir
#Restore Full Backups
innobackupex --copy-back "$backupDirFull"/"$brfull" > "$backupLog"/"$dateNow".restore.full 2>&1
if grep -q "completed OK" "$backupLog"/"$dateNow".restore.full; then
chown -R mysql:mysql $dataDir
service mysqld start
echo "Retore Backup Full Daily completed OK"
else
echo "Retore Backup Full Daily FAILED"
fi
else
echo "Good Bye"
exit 1;
fi
}
restore_inc_backup(){
declare -a arr_inc
binc=`ls -l $backupDirInc | grep '^d' | awk '{print$9}'`
for i in $binc
do
arr_inc=("${arr_inc[@]}" "$i")
done
echo -e "List Incremental Backups"
show_inc_backup;
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo -e "Input the corresponding ${RED_TEXT}[number]${NORMAL} to restore"
read x
brnum=`echo ${arr_inc[x-1]}`
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo "Do you want to continue restore backup [$brnum] (yes/no)?"
read answer
if [ "$answer" == "yes" ]; then
echo -e "${OUT_PUT}****************************************${NORMAL}"
binc=`cut -d_ -f1 <<<"$brnum"`
bfull=`ls -l "$backupDirFull" | grep '^d' | awk '{print$9}'`
for i in $bfull
do
tmpfull=`echo $i | cut -d"_" -f1`
if [ "$binc" == "$tmpfull" ]; then
brfull=$i
fi
done
#Decrypt Incremental Backups
find $backupDirInc/$brnum -name "*.xbcrypt" -type f -exec bash -c 'f={} && nf=${f%.xbcrypt} && cat {} | xbcrypt -da AES256 -f /root/backup/backups.key -o ${nf} && rm {}' \;
#Preparing Incremental Backups
innobackupex $USER_ARGS --apply-log-only "$backupDirFull"/"$brfull"/ --incremental-dir="$backupDirInc"/"$brinc"/ > "$backupLog"/"$dateNow".prepare.inc 2>&1 && echo $dateNow > tmpfile.txt
brinc=`cat tmpfile.txt`
echo -e "Process is running, please wait ..."
empty_dir;
service mysqld stop
mv $dataDir/* $tmpDataDir
#Restore Full Backups
innobackupex --copy-back "$backupDirFull"/"$brfull"/"$brinc" > "$backupLog"/"$dateNow".restore.inc 2>&1
if grep -q "completed OK" $backupLog/$dateNow.restore.inc; then
chown -R mysql:mysql $dataDir
service mysqld start
echo "Retore Incremental Backup completed OK"
else
echo "Retore Incremental Backup FAILED"
fi
else
exit 1;
fi
}
show_menu(){
echo -e "********** MAIN MENU **********"
echo -e "${MENU}${NUMBER} 1)${MENU} Create Full/Inc Backup ${NORMAL}"
#echo -e "${MENU}${NUMBER} 2)${MENU} Create Incremental Backup ${NORMAL}"
echo -e "${MENU}${NUMBER} 2)${MENU} Show All Backups Current ${NORMAL}"
echo -e "${MENU}${NUMBER} 3)${MENU} Restore Full Backups ${NORMAL}"
echo -e "${MENU}${NUMBER} 4)${MENU} Restore Incremental Backups ${NORMAL}"
echo -e "${MENU}*******************************${NORMAL}"
echo -e "Please input a menu option and enter or ${RED_TEXT}[enter]${NORMAL} to exit"
read opt
}
clear;
show_menu;
while [ opt != '' ]
do
if [[ $opt = "" ]]; then
exit;
else
case $opt in
1) clear;
do_backup;
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo -e "Press ${RED_TEXT} [enter]${NORMAL} key to backup menu. . .";
read enterKey;
clear;
show_menu;
;;
2) clear;
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo "List Full Backups"
show_full_backup;
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo "List Incremental Backups"
show_inc_backup;
echo -e "${OUT_PUT}****************************************${NORMAL}"
echo -e "Press ${RED_TEXT} [enter]${NORMAL} key to backup menu. . .";
read enterKey;
clear;
show_menu;
;;
3) clear;
echo -e "${OUT_PUT}****************************************${NORMAL}"
restore_full_backup;
show_menu;
;;
4) clear;
echo -e "${OUT_PUT}****************************************${NORMAL}"
restore_inc_backup;
show_menu;
;;
*) clear;
show_menu;
;;
esac
fi
done
Somebody help me this issue, please.