Buy Percona ServicesBuy Now!

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

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

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

    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


    Code:
    #!/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

  • #2
    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?

    Comment


    • #3
      Thanks for this script! Why use --compress just to uncompress the files immediately?

      Comment


      • #4
        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.

        Comment


        • #5
          My colleague changed the following to eliminate the unnecessary compress/decompress:
          Code:
          ARGS="--rsync $USER_ARGS --no-timestamp --compress --compress-threads=4"
          changed to:
          Code:
          ARGS="--rsync $USER_ARGS --no-timestamp"
          and in both full_backup() and incremental_backup(), remove the for loops that decompress.

          Comment


          • #6
            Good Script, thanks brother. It is working fine.

            Comment


            • #7
              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.

              Code:
              #!/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

              Comment


              • #8
                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,

                Comment


                • #9
                  Hello all,

                  thanks for sharing,

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

                  script
                  Code:
                  #!/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

                  Code:
                  [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

                  Comment


                  • #10
                    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
                    Scott Nemes
                    http://www.linkedin.com/in/scottnemes

                    Comment


                    • #11
                      Originally posted by scott.nemes View Post
                      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
                      Last edited by yudorahadya; 07-09-2018, 03:15 AM.

                      Comment

                      Working...
                      X