How to use Xtrabackup to back up and restore MySQL

    create user backup@'localhost' identified by '123456';
    grant reload,process,lock tables,replication client on *.* to backup@localhost;

    innobackupex --defaults-file=/etc/my.cnf --user=backup --password=123456 /backup/

    innobackupex --defaults-file=/etc/my.cnf --user=backup --password=123456 --incremental --incremental-basedir=/backup/2021-06-01_14-44-54 /backup/

    innobackupex --apply-log --redo-only /backup/2021-06-01_14-44-54/

    innobackupex --copy-back --datadir=/usr/local/mysql/data /backup/2021-06-01_14-44-54/

    chown -R mysql:mysql data/

    #!/bin/bash
    # Get the program path name program_dir=`dirname $0`/..
    # Read all variable values ​​in the configuration file and set them as global variables# Configuration file conf_file="$program_dir/conf/backup.conf"
    #mysql user user=`sed '/^user=/!d;s/.*=//' $conf_file`
    #mysql password password=`sed '/^password=/!d;s/.*=//' $conf_file`
    #mysql backup directory backup_dir=`sed '/^backup_dir=/!d;s/.*=//' $conf_file`
    #mysql backup compression package directory gzip_dir=`sed '/^gzip_dir=/!d;s/.*=//' $conf_file`
    # percona-xtrabackup command xtrabackup path xtrabackup_bin=`sed '/^xtrabackup_bin=/!d;s/.*=//' $conf_file`
    #mysql full backup prefix identifier full_backup_prefix=`sed '/^full_backup_prefix=/!d;s/.*=//' $conf_file`
    #mysql incremental prefix identifier increment_prefix=`sed '/^increment_prefix=/!d;s/.*=//' $conf_file`
    #Backup error log file error_log=$program_dir/var/`sed '/^error_log=/!d;s/.*=//' $conf_file`
    #Backup index file index_file=$program_dir/var/`sed '/^index_file=/!d;s/.*=//' $conf_file`
    # Backup date backup_date=`date +%F`
    # Backup time backup_time=`date +%H-%M-%S`
    # The day of the week for backup backup_week_day=`date +%u`
    # Create the relevant directory log_dir=$program_dir/log/backup
    var_dir=$program_dir/var
    mkdir -p $backup_dir
    mkdir -p $log_dir
    mkdir -p $var_dir
    mkdir -p $gzip_dir
    # Full backup function full_backup() {
      backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
      mkdir -p $backup_dir/$backup_folder
      $xtrabackup_bin \
        --user=$user \
        --password=$password \
        --backup \
        --target-dir=$backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1
      return $?
    }
    # Incremental backup function increment_backup() {
      backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
      incr_base_folder=`sed -n '$p' $index_file | \
                       awk -F '[, {}]*' '{print $3}' | \
                       awk -F ':' '{print $2}'`
      mkdir -p $backup_dir/$backup_folder
      $xtrabackup_bin \
        --user=$user \
        --password=$password \
        --backup \
        --target-dir=$backup_dir/$backup_folder \
        --incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log 2>&1
      return $?
    }
    # Delete the previous backup (usually used after full backup is completed)
    function delete_before_backup() {
      cat $index_file | awk -F '[, {}]*' '{print $3}' | \
        awk -v backup_dir=$backup_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s\n", backup_dir, $2)}}' | \
        /bin/bash
      cat $index_file | awk -F '[, {}]*' '{print $3}' | \
        awk -v gzip_dir=$gzip_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s\n", gzip_dir, $2)}}' | \
        /bin/bash
      cat $index_file | awk -F '[, {}]*' '{print $3}' | \
        awk -v log_dir=$log_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s.log\n", log_dir, $2)}}' | \
        /bin/bash
    }
    #Backup index file function backup_index_file() {
      cp $index_file ${index_file}_$(date -d "1 day ago" +%F)
    }
    #Backup index file function send_index_file_to_remote() {
      # ./expect_scp ip address account password ${index_file} target server folder port number echo 'send index file ok'
    }
    # Add an index, which records the latest backup function append_index_to_file() {
      echo "{week_day:$backup_week_day, \
             dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \
             type:${1}, \
             date:${backup_date}}" >> $index_file
    }
    # Log error messages to a file function logging_backup_err() {
      echo "{week_day:$backup_week_day, \
             dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \
             type:${1}, \
             date:${backup_date}}" >> $error_log
    }
    # Clear the index function purge_index_from_file() {
      > $index_file
    }
    # Clear error log information function purge_err_log() {
      > $error_log
    }
    #Package backup function tar_backup_file() {
      cd $backup_dir
      tar -jcf ${gzip_dir}/${1}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2 \
               ${1}_${backup_date}_${backup_time}_${backup_week_day}
      cd -> /dev/null
      rm -rf ${backup_dir}/${1}_${backup_date}_${backup_time}_${backup_week_day}
    }
    # Send backup to remote function send_backup_to_remote() {
      # ./expect_scp ip address account password ${gzip_dir}/${1}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2 target server folder port number echo "send $1 remote ok"
    }
    # Determine whether to perform full or incremental backup# 0:full, 1:incr
    function get_backup_type() {
      backup_type=0
      if [ 1 -eq `date +%H` ]; then
        backup_type=0
      else
        backup_type=1
      fi
      touch $index_file
      if [ ! -n "`cat $index_file`" ]; then
        backup_type=0
      fi
      return $backup_type
    }
    # Test the correctness of the configuration file function test_conf_file() {
      # Check if each variable is configured in the configuration file. If not, exit the program if [ ! -n "$user" ]; then echo 'fail: configure file user not set'; exit 2; fi
      if [ ! -n "$password" ]; then echo 'fail: configure file password not set'; exit 2; fi
      if [ ! -n "$backup_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi
      if [ ! -n "$gzip_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi
      if [ ! -n "$full_backup_prefix" ]; then echo 'fail: configure file full_backup_prefix not set'; exit 2; fi
      if [ ! -n "$increment_prefix" ]; then echo 'fail: configure file increment_prefix not set'; exit 2; fi
      if [ ! -n "$error_log" ]; then echo 'fail: configure file error_log not set'; exit 2; fi
      if [ ! -n "$index_file" ]; then echo 'fail: configure file index_file not set'; exit 2; fi
    }
    # Execute function main() {
      # Detection configuration file value test_conf_file
      # Determine whether to perform a full or incremental backup get_backup_type
      backup_type=$?
      case $backup_type in
        0 )
          # Full backup full_backup
          backup_ok=$?
          if [ 0 -eq "$backup_ok" ]; then
          # Full backup successful# Pack the latest backup tar_backup_file $full_backup_prefix
            # # Send tar backup to remote send_backup_to_remote $full_backup_prefix
            #Backup index file backup_index_file
            # Clear the previous backup delete_before_backup
            # Clear the index file purge_index_from_file
            # Add an index, which records the latest backup append_index_to_file $full_backup_prefix
            # Send index file to remote send_index_file_to_remote
          else
          # Full backup failed # Delete the backup directory rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
            # Logging error log logging_backup_err $full_backup_prefix
          fi
          ;;
        1 )
          # Incremental backup increment_backup
          backup_ok=$?
          if [ "$backup_ok" -eq 0 ]; then
          # Incremental backup successful # Pack the latest backup tar_backup_file $increment_prefix
            # # Send tar backup to remote send_backup_to_remote $increment_prefix
            # Add an index, which records the latest backup append_index_to_file $increment_prefix
            # # Send index file to remote send_index_file_to_remote
          else
          # Incremental backup failed # Delete the backup directory rm -rf ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
            # Record error log logging_backup_err $increment_prefix
          fi
          ;;
      esac
    }
    main

    #mysql username user=backup
    #mysql password password=123456
    # Backup path backup_dir=/data/backup
    # Backup compression package directory gzip_dir=/data/backups/backups_zip
    # innobackupex command path xtrabackup_bin=/opt/xtrabackup/bin/xtrabackup
    #Full backup information name prefix full_backup_prefix=full
    #Incremental backup information name prefix increment_prefix=incr
    # Error log file (this file is used to determine whether the backup was successful)
    # format:
    # {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30}
    error_log=mysql_increment_hot_backup.err
    # Index file# format:
    # {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30}
    index_file=mysql_increment_hot_backup.index

    #!/bin/bash
    # Get the program path name program_dir=`dirname $0`/..
    # Read all variable values ​​in the configuration file and set them as global variables# Configuration file conf_file="$program_dir/conf/restore.conf"
    # MySQL data folder data_dir=`sed '/^data_dir=/!d;s/.*=//' $conf_file`
    # Backup index file path backup_index_file=`sed '/^backup_index_file=/!d;s/.*=//' $conf_file`
    # percona-xtrabackup command xtrabackup path xtrabackup_bin=`sed '/^xtrabackup_bin=/!d;s/.*=//' $conf_file`
    # Backup file directory backup_restore_dir=`sed '/^backup_restore_dir=/!d;s/.*=//' $conf_file`
    # Check the correctness of the configuration file function exam_conf_file() {
        # Check if each variable is configured in the configuration file. If not, exit the program if [ ! -n "$data_dir" ]; then echo 'fail: configure file data_dir not set'; exit 2; fi
        if [ ! -n "$backup_index_file" ]; then echo 'fail: configure file backup_index_file not set'; exit 2; fi
        if [ ! -n "$xtrabackup_bin" ]; then echo 'fail: configure file xtrabackup_bin not set'; exit 2; fi
        if [ ! -n "$backup_restore_dir" ]; then echo 'fail: configure file backup_restore_dir not set'; exit 2; fi
    }
    # Check if the backup file is in compressed format function exam_backup_restore_file(){
        file_backup_restore_name_arr=`ls $backup_restore_dir`
        for file_name in $file_backup_restore_name_arr;do
            if [ "${file_name##*.}"x = "bz2"x ];then
                tar -jxf $backup_restore_dir/$file_name -C $backup_restore_dir
                rm -rf $backup_restore_dir/$file_name
            fi

        done
    }
    # Check if MySQL is stopped function exam_mysql_is_stop(){
        if [ 0 -eq `ps -ef | grep mysql | grep -v grep | wc -l` ]; then
            echo "MySQL service has stopped"
        else
            /etc/init.d/mysqld stop
            echo "Stop MySQL service"
            sleep 3
            echo "MySQL service has been stopped"
        fi
    }
    # Check if MySQL data file is deleted function exam_data_is_del(){
        if [ -d $data_dir ];then
            echo "Deleting MySQL data files"
            rm -rf $data_dir
        else
            echo "MySQL data files have been deleted"
        fi
    }
    # Read the backup index file function read_backup_index() {
        cat $backup_index_file | awk '{print $2}' | awk -F: '{print $2}' | awk '{sub(/.$/,"")}1'
    }
    # Prepare full files function ready_full(){
        full_file_name=`echo ${1} | awk '{print $1}'`
        $xtrabackup_bin/innobackupex \
            --apply-log \
            --redo-only \
            $backup_restore_dir/$full_file_name

        echo "All files are ready"
    }
    # Prepare to add files function ready_incr(){
        backup_index=$(read_backup_index)
        full_file_name=`echo $backup_index | awk '{print $1}'`
        for file_name in $backup_index;do
            if [ 1 -eq `echo "$file_name" | grep incr | wc -l` ]; then 
                $xtrabackup_bin/innobackupex \
                    --apply-log \
                    --redo-only \
                    $backup_restore_dir/$full_file_name \
                    --incremental-dir=$backup_restore_dir/$file_name
            fi
        done
        echo "Additional files are ready"
    }
    # Execute backup and restore function exec_backup_restore(){
        echo "Start backup and restore"
        full_file_name=`echo ${1} | awk '{print $1}' `
        $xtrabackup_bin/innobackupex \
            --copy-back \
            --datadir=$data_dir \
            $backup_restore_dir/$full_file_name
    }
    # Execute function main() {
        # Check the correctness of the configuration file exam_conf_file
        # Check if the backup file is in compressed format exam_backup_restore_file
        # Check if MySQL is stopped exam_mysql_is_stop
        # Check if the MySQL data file is deleted exam_data_is_del
        # Read the index file backup_index=$(read_backup_index)
        # Prepare full backup file ready_full $backup_index
        # Prepare to add the file ready_incr
        # Execute backup and restore exec_backup_restore $backup_index
        # Grant permissions to the data file echo "Re-grant permissions to the data directory"
        chown -R mysql:mysql $data_dir
        echo "Starting MySQL"
        /etc/init.d/mysqld start
        echo "Backup and restore successful"
    }
    main

    #MySQL data folder data_dir=/opt/mysql/data
    #Backup index file path backup_index_file=/opt/xtrabackup/backup/var/mysql_increment_hot_backup.index
    #xtrabackup bin directory xtrabackup_bin=/opt/xtrabackup/bin
    # Backup file directory backup_restore_dir=/data/backups/backups_zip