Emergency

Restore Incremental backup with Innobackupex

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Restore Incremental backup with Innobackupex

    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: https://www.percona.com/blog/2014/04...na-xtrabackup/

    Script backup full:
    Code:
    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
    and incremental:
    Code:
    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"
    Script restore Incemental backup:
    Code:
    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
    Somebody help me this issue.

  • #2
    All script have updated, but still this issue.
    Code:
    #!/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.

    Comment

    Working...
    X