[script] automatic backups (incremental & full) and restore :)

bigzaquibigzaqui Entrant Participant
Hi, first of all, thanks to the community for this awesome tool, is amazing the things you can do :)

Yesterday I spent the day writing this script, you can use it to do full or incremental backups of your /var/lib/mysql using xtrabackup. It automatically creates the incremental folders in order and in case of restore, it automatically applies the differential in order to get a functional preparated full backup.

Please read it and if you think there's something weird/wrong dont hesitate to tell me

#!/bin/bash -x
#Tue Jul  2 15:32:21 CEST 2013
#Made by Edward Z.
set -e #stops execution if a variable is not set
set -u #stop execution if something goes wrong

usage() { 
        echo "usage: $(basename $0) [option]" 
        echo "option=full: do a full backup of vinnie /var/lib/mysql using innobackupex, aprox time 6 hours."
        echo "option=incremental: do a incremental backup"
        echo "option=restore: this will restore the latest backup to vinnie, BE CAREFUL!"
        echo "option=help: show this help"
}

full_backup() {
        date
        if [ ! -d $BACKUP_DIR ]
        then
                echo "ERROR: the folder $BACKUP_DIR does not exists"
                exit 1
        fi
        echo "doing full backup..."
        echo "cleaning the backup folder..."
        rm -rf $BACKUP_DIR/*
        echo "cleaning done!"
        innobackupex $ARGS $BACKUP_DIR/FULL
        date
        echo "backup done!, now uncompressing the files..."
        for bf in `find $BACKUP_DIR/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
        date
        echo "uncompressing done!, preparing the backup for restore..."
        innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
        date
        echo "preparation done!"
}
incremental_backup()
{
        if [ ! -d $BACKUP_DIR/FULL ]
        then
                echo "ERROR: no full backup has been done before. aborting"
                exit -1
        fi

        #we need the incremental number
        if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
            NUMBER=1
        else
            NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
        fi
        date
        echo "doing incremental number $NUMBER"
        if [ $NUMBER -eq 1 ]
        then
                innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL
        else
                innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1))
        fi
        date
        echo $NUMBER > $BACKUP_DIR/last_incremental_number
        echo "incremental $NUMBER done!, now uncompressing the files..."
        for bf in `find $BACKUP_DIR/inc$NUMBER -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
        date
        echo "uncompressing done!, the preparation will be made when the restore is needed"

}

restore()
{
        echo "WARNING: are you sure this is what you want to do? (Enter 1 or 2)"
        select yn in "Yes" "No"; do
            case $yn in
                Yes ) break;;
                No ) echo "aborting... that was close."; exit;;
            esac
        done

        echo "cross your fingers :)"
        date
        echo "doing restore..."
        #innobackupex --apply-log --redo-only $BACKUP_DIR/FULL

        #we append all the increments
        P=1
        while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P+1)) ]
        do
              echo "processing incremental $P"
                innobackupex --apply-log --redo-only $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
                P=$(($P+1))
        done

        if [ -d $BACKUP_DIR/inc$P ]
        then
                #the last incremental has to be applied without the redo-only flag
                echo "processing last incremental $P"
                innobackupex --apply-log $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
        fi

        #we prepare the full
                innobackupex --apply-log $BACKUP_DIR/FULL

        #finally we copy the folder
        cp -r $DATA_DIR $DATA_DIR.back
        rm -rf $DATA_DIR/*
        innobackupex --copy-back $BACKUP_DIR/FULL

        chown -R mysql:mysql $DATA_DIR

}

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

BACKUP_DIR=/tmp/backup
DATA_DIR=/var/lib/mysql
USER_ARGS=" --user=root --password=password"

ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"

if [ $# -eq 0 ]
then
usage
exit 1
fi

    case $1 in
        "full")
            full_backup
            ;;
        "incremental")
        incremental_backup
            ;;
        "restore")
        restore
            ;;
        "help")
            usage
            break
            ;;
        *) echo "invalid option";;
    esac


Comments

  • ifeatuifeatu Entrant Participant
    Good script. Weird that innobackupex incrementals requires you to backup non-innodb artifacts in addition to the log files...forces the size of my incrementals to be at least the size of all my databases...any way around this?
  • JohnLofgrenJohnLofgren Entrant Participant
    Thanks for this script! Why use --compress just to uncompress the files immediately?
  • smga3000smga3000 Entrant Participant
    I was wondering the same thing but I think I ran across the answer, but it is pretty bizarre. There are two types of compression, there is the xbstream format and then there is the pipe to tar.gz. Apparently, you can't 'prepare' a compressed backup if I'm reading correctly. So that means that if you do a hotbackup, you can write it out compressed (for some reason) then you have to decompress it so you can 'prepare'/sync the logs and then you are done and can compress it again. Just sounds really stupid, I'm hoping someone can explain if I'm wrong or not because it will change my strategy.
  • JohnLofgrenJohnLofgren Entrant Participant
    My colleague changed the following to eliminate the unnecessary compress/decompress:
    ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"
    

    changed to:
    ARGS="--rsync $USER_ARGS --no-timestamp"
    

    and in both full_backup() and incremental_backup(), remove the for loops that decompress.
  • ma.prakashma.prakash Entrant Reader Participant
    Good Script, thanks brother. It is working fine.
  • renancerattorenanceratto Entrant Participant
    Hello!

    First of all, thank you so much. This script is exactly what I was looking for. And it works like a charm. :)

    I've made some minor modifications related to compression.
    The latest innobackupex version supports --decompress
    There is no need to uncompress the files after backup. You can create full and incremental backups using only compressed files.
    You need to uncompress only before restore, so I did that: use --decompress before --apply-log

    I've changed the BACKUP_DIR to use $(date +\%Y-\%m) parameter, so I can use this script in my crontab and have control over the periodicity.

    I'm open to suggestions if I misunderstood something.
    #!/bin/bash -x
    #Tue Jul  2 15:32:21 CEST 2013
    #Made by Edward Z.
    set -e #stops execution if a variable is not set
    set -u #stop execution if something goes wrong
    
    usage() { 
            echo "usage: $(basename $0) [option]" 
            echo "option=full: do a full backup of vinnie /var/lib/mysql using innobackupex."
            echo "option=incremental: do a incremental backup"
            echo "option=restore: this will restore the latest backup to vinnie, BE CAREFUL!"
            echo "option=help: show this help"
    }
    
    full_backup() {
            date
            if [ ! -d $BACKUP_DIR ]
            then
            mkdir $BACKUP_DIR
                    #echo "ERROR: the folder $BACKUP_DIR does not exists"
                    #exit 1
            fi
            echo "doing full backup..."
            echo "cleaning the backup folder..."
            rm -rf $BACKUP_DIR/*
            echo "cleaning done! Starting backup"
            innobackupex $ARGS $BACKUP_DIR/FULL
            date
            echo "backup done!" #, now uncompressing the files..."
    #        for bf in `find $BACKUP_DIR/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
    #        date
    #        echo "uncompressing done!, preparing the backup for restore..."
    #        innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
    #        date
    #        echo "preparation done!"
    }
    
    incremental_backup()
    {
            if [ ! -d $BACKUP_DIR/FULL ]
            then
                    echo "ERROR: no full backup has been done before. aborting"
                    exit -1
            fi
    
            #we need the incremental number
            if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
                NUMBER=1
            else
                NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
            fi
            date
            echo "doing incremental number $NUMBER"
            if [ $NUMBER -eq 1 ]
            then
                    innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL 
            else
                    innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1)) 
            fi
            date
            echo $NUMBER > $BACKUP_DIR/last_incremental_number
            echo "incremental $NUMBER done!" #, now uncompressing the files..."
    #        for bf in `find $BACKUP_DIR/inc$NUMBER -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
    #        date
    #        echo "uncompressing done!, the preparation will be made when the restore is needed"
    }
    
    restore()
    {
            echo "WARNING: are you sure this is what you want to do? (Enter 1 or 2)"
            select yn in "Yes" "No"; do
                case $yn in
                    Yes ) break;;
                    No ) echo "aborting... that was close."; exit;;
                esac
            done
    
            echo "cross your fingers :)"
            date
            echo "decompressing full backup"
            innobackupex --decompress $BACKUP_DIR/FULL 
        echo "doing restore..."
            innobackupex --apply-log --redo-only $BACKUP_DIR/FULL 
    
            #we append all the increments
            P=1
            while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P+1)) ]
            do
                  echo "decompressing incremental $P"
              innobackupex --decompress $BACKUP_DIR/inc$P 
                  echo "processing incremental $P"
                  innobackupex --apply-log --redo-only $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P 
                  P=$(($P+1))
            done
    
            if [ -d $BACKUP_DIR/inc$P ]
            then
            #the last incremental has to be applied without the redo-only flag
            echo "decompressing incremental $P"
                    innobackupex --decompress $BACKUP_DIR/inc$P 
                    echo "processing last incremental $P"
                    innobackupex --apply-log $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P 
            fi
    
            #we prepare the full
            innobackupex --apply-log $BACKUP_DIR/FULL 
    
            #finally we copy the folder
            cp -r $DATA_DIR $DATA_DIR.back
            rm -rf $DATA_DIR/*
            innobackupex --copy-back $BACKUP_DIR/FULL 
    
            chown -R mysql:mysql $DATA_DIR
    
    }
    
    #######################################
    #######################################
    #######################################
    BACKUP_DIR=/home/user/xtrabackup/$(date +\%Y-\%m)
    DATA_DIR=/var/lib/mysql
    USER_ARGS=" --user=backup--password=backuppass"
    
    ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"
    
    if [ $# -eq 0 ]
    then
    usage
    exit 1
    fi
    
        case $1 in
            "full")
                full_backup
                ;;
            "incremental")
            incremental_backup
                ;;
            "restore")
            restore
                ;;
            "help")
                usage
                break
                ;;
            *) echo "invalid option";;
        esac
    
  • tienchtiench Entrant Active Member Participant
    Hi bigzaqui,

    The script is working fine, but if end user choice the incremental backup to restore, in this case how need perform about it?

    Thanks,
  • yudorahadyayudorahadya Entrant Active Member Participant
    Hello all,

    thanks for sharing,

    but, after I try it, script not running, it's only print string in echo.

    script
    #!/bin/bash -x
    #Tue Jul  2 15:32:21 CEST 2013
    #Made by Edward Z.
    set -e #stops execution if a variable is not set
    set -u #stop execution if something goes wrong
    
    #######################################
    #######################################
    #######################################
    BACKUP_DIR=/data/backups/$(date +\%Y-\%m)
    DATA_DIR=/var/lib/mysql
    USER_ARGS=" --user=userbackup--password=12345Ssh!"
    
    ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"
    
    usage() {
            echo "usage: $(basename $0) [option]" 
            echo "option=full: do a full backup of vinnie /var/lib/mysql using innobackupex."
            echo "option=incremental: do a incremental backup"
            echo "option=restore: this will restore the latest backup to vinnie, BE CAREFUL!"
            echo "option=help: show this help"
    }
    
    full_backup() {
            date
            if [ ! -d $BACKUP_DIR ]
            then
            mkdir $BACKUP_DIR
                    #echo "ERROR: the folder $BACKUP_DIR does not exists"
                    #exit 1
            fi
            echo "doing full backup..."
            echo "cleaning the backup folder..."
            rm -rf $BACKUP_DIR/*
            echo "cleaning done! Starting backup"
            innobackupex $ARGS $BACKUP_DIR/FULL
            date
            echo "backup done!" #, now uncompressing the files..."
    #        for bf in `find $BACKUP_DIR/FULL -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
    #        date
    #        echo "uncompressing done!, preparing the backup for restore..."
    #        innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
    #        date
    #        echo "preparation done!"
    }
    
    incremental_backup()
    {
            if [ ! -d $BACKUP_DIR/FULL ]
            then
                    echo "ERROR: no full backup has been done before. aborting"
                    exit -1
            fi
    
            #we need the incremental number
            if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
                NUMBER=1
            else
                NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
            fi
            date
            echo "doing incremental number $NUMBER"
            if [ $NUMBER -eq 1 ]
            then
                    innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL
            else
                    innobackupex $ARGS --incremental $BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1))
            fi
            date
            echo $NUMBER > $BACKUP_DIR/last_incremental_number
            echo "incremental $NUMBER done!" #, now uncompressing the files..."
    #        for bf in `find $BACKUP_DIR/inc$NUMBER -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) ;echo "processing" $bf; rm $bf; done
    #        date
    #        echo "uncompressing done!, the preparation will be made when the restore is needed"
    }
    
    restore()
    {
            echo "WARNING: are you sure this is what you want to do? (Enter 1 or 2)"
            select yn in "Yes" "No"; do
                case $yn in
                    Yes ) break;;
                    No ) echo "aborting... that was close."; exit;;
                esac
            done
    
            echo "cross your fingers :)"
            date
            echo "decompressing full backup"
            innobackupex --decompress $BACKUP_DIR/FULL
        echo "doing restore..."
            innobackupex --apply-log --redo-only $BACKUP_DIR/FULL
    
            #we append all the increments
            P=1
            while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P+1)) ]
            do
                  echo "decompressing incremental $P"
              innobackupex --decompress $BACKUP_DIR/inc$P
                  echo "processing incremental $P"
                  innobackupex --apply-log --redo-only $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
                  P=$(($P+1))
            done
    
            if [ -d $BACKUP_DIR/inc$P ]
            then
            #the last incremental has to be applied without the redo-only flag
            echo "decompressing incremental $P"
                     innobackupex --decompress $BACKUP_DIR/inc$P
                     echo "processing last incremental $P"
                     innobackupex --apply-log $BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
             fi
    
             #we prepare the full
             innobackupex --apply-log $BACKUP_DIR/FULL
    
             #finally we copy the folder
             cp -r $DATA_DIR $DATA_DIR.back
             rm -rf $DATA_DIR/*
             innobackupex --copy-back $BACKUP_DIR/FULL
    
             chown -R mysql:mysql $DATA_DIR
    
     }
    
     if [ $# -eq 0 ]
     then
     usage
     exit 1
     fi
    
         case $1 in
             "full")
                 full_backup
                 ;;
             "incremental")
             incremental_backup
                 ;;
             "restore")
             restore
                 ;;
             "help")
                 usage
                 break
                 ;;
             *) echo "invalid option";;
         esac
    

    Output after running script
    [root@inc2 home]# /usr/bin/xtrabackup.sh 
    + set -e
    + set -u
    ++ date +%Y-%m-%d
    + BACKUP_DIR=/home/xtrabackup/2018-04-17
    + DATA_DIR=/var/lib/mysql
    + USER_ARGS=' --user=backupuser --password=password'
    + ARGS='--rsync  --user=backupuser --password=password --no-timestamp --compress --compress-threads=4'
    + '[' 0 -eq 0 ']'
    + usage
    ++ basename /usr/bin/xtrabackup.sh
    + echo 'usage: xtrabackup.sh [option]'
    usage: xtrabackup.sh [option]
    + echo 'option=full: do a full backup of vinnie /var/lib/mysql using innobackupex.'
    option=full: do a full backup of vinnie /var/lib/mysql using innobackupex.
    + echo 'option=incremental: do a incremental backup'
    option=incremental: do a incremental backup
    + echo 'option=restore: this will restore the latest backup to vinnie, BE CAREFUL!'
    option=restore: this will restore the latest backup to vinnie, BE CAREFUL!
    + echo 'option=help: show this help'
    option=help: show this help
    + exit 1
    

    what's wrong with my script?
    please give me advice.

    Thanks
  • scott.nemesscott.nemes MySQL Sage Active Member Advisor
    Hi yudorahadya;

    Looks like you need to pass it an argument to tell it what to do. Try "/usr/bin/xtrabackup.sh full" to do a full backup.

    -Scott
  • yudorahadyayudorahadya Entrant Active Member Participant
    Hi yudorahadya;

    Looks like you need to pass it an argument to tell it what to do. Try "/usr/bin/xtrabackup.sh full" to do a full backup.

    -Scott

    Thanks Scott for advice.

    But I have new problem after setup that script in cron tab.
    for one month, running script full backup and incremental backup. But, file incremental it's very large. So, it's make hardisk memory fast fully in server.
    I already check root cause. In my database many create table version with myisam. But, after read documentation percona, compressed file percona has only support in table version with innodb.
    And then, I adding script to make file tar after create full backup and incremental backup. But, it didn't solve problem. it same, make hardisk memory fast fully.

    And the secound question, If make incremental backup, mysql running FLUSH TABLES WITH READ LOCK and it's problem if there any problem for activity query in mysql. What can you do ?

    So, Any Idea about it ?

    Thank you

    yudorahadya
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.