简介

Percona XtraBackup 是一款开源、免费的 MySQL 热备份软件，提供定时备份、增量备份等功能。

前置准备

系统: centos7.9

已安装软件: mysql5.7

安装

percona 需要和 mysql 版本对应，此处使用 mysql5.7，需要安装 percona-xtrabackup-24

安装仓库

[root@localhost ~] yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

测试仓库

[root@localhost ~] yum list | grep percona

激活仓库

[root@localhost ~] percona-release enable-only tools release

安装

[root@localhost ~] yum install percona-xtrabackup-24

权限要求

系统权限：需要系统账号有对应目录的读写权限，用来备份 mysql 文件，一般直接用 root 用户即可。

mysql账号权限：

mysql> CREATE USER 'bkpuser'@'%' IDENTIFIED BY 'As123456!';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'%';
mysql> FLUSH PRIVILEGES;

备份

此部分用来了解原理及基础的使用方法，如果想要直接使用的话，可以直接查看备份脚本部分

全量备份

开始备份

[root@localhost ~] xtrabackup --backup --user=bkpuser --password=As123456! --target-dir=/data/backups/base
...
xtrabackup: Transaction log of lsn (26970807) to (137343534) was copied.
160906 10:19:18 completed OK!
  • –backup: 表示备份操作
  • –user: mysql的用户名(即权限要求配置的 bkpuser)
  • –password: mysql 的密码
  • –target-dir: 备份文件存在的文件夹

全量备份预处理

由于数据是在不同的时间备份的，数据可能会被更改，所以在执行恢复前需要对数据预处理，执行：

[root@localhost ~] xtrabackup --prepare --target-dir=/data/backups/base
...
InnoDB: Shutdown completed; log sequence number 2755624
221021 16:59:23 completed OK!
  • –prepare: 表示预处理操作
  • –target-dir: 需要预处理数据的文件夹

恢复备份

恢复备份前必须先进行预处理，mysql 必须停止，并且 mysql 的 datadir 目录为空。

# 关闭 mysql 服务
[root@localhost ~] service mysqld stop
# 删除 mysql 的 datadir，一定要谨慎，别删错了
[root@localhost ~] rm -rf /var/lib/mysql/
# 恢复备份文件，如果想要恢复备份后删除备份文件，可以将 `--copy-back` 替换为 `--move-back`(其实手动copy过去也可以, 不一定要通过 xtrabackup 命令)
[root@localhost ~] xtrabackup --copy-back --target-dir=/data/backups/base
# 由于 /var/lib/mysql/ 被删除了，所以这里需要给文件夹重新赋权限，如果开启了selinux, 也需要关闭, 不然会启动mysql失败。
[root@localhost ~] chown -R mysql:mysql /var/lib/mysql/
# 重新启动
[root@localhost ~] service mysqld start

增量备份

增量备份的原理，是因为每个 InnoDB 页面都包含一个日志序列号 (LSN)。 LSN 是整个数据库的系统版本号。每个页面的 LSN 显示它最近的更改时间。

创建增量备份

全量备份的 target-dir 目录下会生成一个 xtrabackup_checkpoints 文件，里面包含的 to_lsn ，就是全量备份结束时的 LSN, 文件格式如下:

backup_type = full-prepared
from_lsn = 0
to_lsn = 2755117
last_lsn = 2755126
compact = 0
recover_binlog_info = 0
flushed_lsn = 2755126

基于之前做的全量备份，我们可以使用如下命令做第一次增量备份

[root@localhost ~] xtrabackup --backup --user=bkpuser --password=As123456! --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base
  • –target-dir: 此次增量备份保存数据的文件夹
  • –incremental-basedir: 基于哪次备份的文件夹做增量备份

查看第一次增量备份的 /data/backups/inc1/xtrabackup_checkpoints 文件,会发现from_lsn就是全量备份里面的 to_lsn

backup_type = incremental
from_lsn = 2755117
to_lsn = 2755757
last_lsn = 2755766
compact = 0
recover_binlog_info = 0
flushed_lsn = 2755766

如果想基于第一次增量备份的数据做第二次增量备份，使用以下命令

[root@localhost ~] xtrabackup --backup --user=bkpuser --password=As123456! --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1

查看第二次增量备份的 /data/backups/inc1/xtrabackup_checkpoints 文件

backup_type = incremental
from_lsn = 2755757
to_lsn = 2762910
last_lsn = 2762919
compact = 0
recover_binlog_info = 0
flushed_lsn = 2762919

增量备份预处理

增量备份的预处理方式与全量备份的不同。

  1. 全量备份预处理会包含两个步骤，一是提交事务并且针对日志文件中的数据进行重放；二是未提交的事务进行回滚。

  2. 增量备份预处理要跳过第二个步骤，因为未提交的事务可能会在下一个增量备份中提交，我们可以使用 --apply-log-only来避免日志回滚，如果没有使用此参数的话，后面的增量备份可能会无法使用。

基于前面的操作，我们现在有这些备份文件

/data/backups/base
/data/backups/inc1
/data/backups/inc2

首先预处理之前的全量备份，但是要阻止日志回滚

[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
...
InnoDB: Shutdown completed; log sequence number 2755756
InnoDB: Number of pools: 1
221021 18:20:37 completed OK!

接着预处理第一次增量备份，此时会把增量的数据添加到全量备份(/data/backups/base)中。此时使用 /data/backups/base 恢复备份会就是第一次增量备份时的数据。

[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1
...
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2755757)
...
221021 18:23:59 completed OK!

增量备份不能预处理两次,即使用 --incremental-basedir 备份的数据，因为会重复执行sql

接着预处理第二次增量备份，此时使用 /data/backups/base 恢复备份就是第二次增量备份时的数据

[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2
...
221021 18:24:45 completed OK!

压缩

mysql 数据量过大时会导致备份速度慢，这时可以使用压缩功能来加快备份时间

前置准备

压缩功能基于 qpress 工具实现的，且需要通过 percona-release 配置此工具

[root@localhost ~]# percona-release enable tools
[root@localhost ~]# yum install -y qpress

压缩备份

使用 --compress 即可实现压缩功能,备份后的文件都是以 qp 结尾的

[root@localhost ~]# xtrabackup --backup --user=bkpuser --password=As123456! --compress --target-dir=/data/compressed/

结合 --compress-threads 进行多线程压缩

[root@localhost ~]# xtrabackup --backup --user=bkpuser --password=As123456! --compress --compress-threads=4 --target-dir=/data/compressed/
...
221024 11:00:11 [00] Compressing /data/compressed/backup-my.cnf.qp
221024 11:00:11 [00]        ...done
221024 11:00:11 [00] Compressing /data/compressed/xtrabackup_info.qp
221024 11:00:11 [00]        ...done
xtrabackup: Transaction log of lsn (2756164) to (2756173) was copied.
221024 11:00:12 completed OK!

解压缩

先使用 --decompress 解压缩文件，压缩后的文件没有 .qp 后缀。解压后默认不会删除压缩文件，如果要删除，可以使用 --remove-original 属性。即使不删除，使用 --copy-back 或 --move-back 也不会移动压缩文件。加压缩后与普通备份的文件一样，先预处理后恢复备份。

[root@localhost ~]# xtrabackup --decompress --target-dir=/data/compressed/

加密

自行查看文档，本文不讨论，文档地址：https://docs.percona.com/percona-xtrabackup/2.4/backup_scenarios/encrypted_backup.html

备份脚本

为了简化上面的备份步骤，可以自行编辑 shell 脚本。下面提供一套每天定时备份,最多保留7天备份的脚本。

ST_percona_backup.sh

此脚本可以在线热备份数据库，每天备份一次，如果一天运行多次，则只有第一次会生效，最多会生成1个全量备份和6个增量备份(可通过days配置)。配置在定时任务中，每天至少执行两次。

执行示例: sh /percona/ST_percona_backup.sh

#!/bin/bash
#Script for SecureTransport - Online MySQL backup with Parcona XtraBackup
#Created by Genata 2019
#Make sure to have enough free space for backups.

# my.cnf 配置文件的目录，
FDH=/etc/my.cnf
# xtrabackup 的执行目录
hd=/usr/bin/
# 日志文件
ld=/percona/logs
# 备份存放的文件夹
bd=/percona/backup
# 保存多少天的增量数据
days=6
# 多线程的线程数
pp=4
# mysql 的账号密码
dbuser=bkpuser
dbpass=As123456!
hn=`uname -n`

if [ ! -d "$ld" ]; then
    mkdir -p $ld
fi
echo "[`date -Iseconds`]" Backup start... >>$ld/ST_percona_backup.log
date -Iseconds >$ld/e_backup_percona

#Get current date in format YYYY-MM-DD
#Created directory structure is:
# -YYYY-MM-DD
#   |-full
#   |-inc1
#   |-inc2
#   ...
#   |-incN
#where N=days configured. Set days=6 for weekly cycle.

bp=`date +%F`
if [ -d "$bd/$bp" ]; then
  if [ -d "$bd/$bp/full" ]; then
    echo "[`date -Iseconds`]" Full backup for today $bp already exist. Exiting. >>$ld/ST_percona_backup.log
    echo "[`date -Iseconds`]" Backup done. >>$ld/ST_percona_backup.log
    rm $ld/e_backup_percona 2>/dev/null
    exit 0
  else
    echo "[`date -Iseconds`]" Full backup for today do not exist. Removing ${bp}. >>$ld/ST_percona_backup.log
    rm -r -f "$bd/$bp"
    bp=""
  fi
else
  bp=""
fi

for i in `seq 1 $days`; do
  bp1=`date -d "-$i day" +%F`
  if [ -d "$bd/$bp1" ]; then
    bp=$bp1
    bi=$i
    break
  fi
done
if [ -z "$bp" ]; then
  #Perform Full backup
  bp=`date +%F`
  echo "[`date -Iseconds`]" Will create full backup $bd/$bp/full >>$ld/ST_percona_backup.log
  mkdir -p $bd/$bp
  echo "[`date -Iseconds`]" Start full backup $bd/$bp/full >>$ld/ST_percona_xtrabackup.log
  ${hd}xtrabackup --defaults-file=$FDH --backup --user=$dbuser --password=$dbpass --parallel=$pp --target-dir=$bd/$bp/full >>$ld/ST_percona_xtrabackup.log 2>&1
  pxe=$?
  if [ $pxe -eq 0 ]; then
    echo "[`date -Iseconds`]" Backup created sucessfully. Hostname: $hn >>$ld/ST_percona_backup.log
	echo $hn >$bd/$bp/full/backup_hostname
  else
    rm -r -f "$bd/$bp/full"
    echo "[`date -Iseconds`]" Error creating backup. xtrabackup exit code: $pxe >>$ld/ST_percona_backup.log
  fi
else
  #Perform Incremental backup
  if [ -d "$bd/$bp/full" ]; then
    if [ -d "$bd/$bp/inc$bi" ]; then
      echo "[`date -Iseconds`]" Incremental backup for today already exist. Exiting. >>$ld/ST_percona_backup.log
      echo "[`date -Iseconds`]" Backup done. >>$ld/ST_percona_backup.log
      rm $ld/e_backup_percona 2>/dev/null
      exit 0
    else
      if [ "$bi" -eq "1" ]; then
        #Perform first incremental from full backup
        echo "[`date -Iseconds`]" Will create incremental backup $bd/$bp/inc$bi from $bd/$bp/full >>$ld/ST_percona_backup.log
        mkdir -p $bd/$bp/inc$bi
        echo "[`date -Iseconds`]" Start incremental backup $bd/$bp/inc$bi from $bd/$bp/full >>$ld/ST_percona_xtrabackup.log
        ${hd}xtrabackup --defaults-file=$FDH --backup --user=$dbuser --password=$dbpass --parallel=$pp --target-dir=$bd/$bp/inc$bi --incremental-basedir=$bd/$bp/full >>$ld/ST_percona_xtrabackup.log 2>&1
        pxe=$?
        if [ $pxe -eq 0 ]; then
          echo "[`date -Iseconds`]" Backup created sucessfully. Hostname: $hn >>$ld/ST_percona_backup.log
		  echo $bd/$bp/inc$bi/backup_hostname
        else
          rm -r -f "$bd/$bp/inc$bi"
          echo "[`date -Iseconds`]" Error creating backup. xtrabackup exit code: $pxe >>$ld/ST_percona_backup.log
        fi
      else
        bi1=$((bi -1))
        if [ -d "$bd/$bp/inc$bi1" ]; then
          #Perform N -th incremental from N-1 -th backup
          echo "[`date -Iseconds`]" Will create incremental backup $bd/$bp/inc$bi from $bd/$bp/inc$bi1 >>$ld/ST_percona_backup.log
          mkdir -p $bd/$bp/inc$bi
          echo "[`date -Iseconds`]" Start incremental backup $bd/$bp/inc$bi from $bd/$bp/inc$bi1 >>$ld/ST_percona_xtrabackup.log
          ${hd}xtrabackup --defaults-file=$FDH --backup --user=$dbuser --password=$dbpass --parallel=$pp --target-dir=$bd/$bp/inc$bi --incremental-basedir=$bd/$bp/inc$bi1 >>$ld/ST_percona_xtrabackup.log 2>&1
          pxe=$?
          if [ $pxe -eq 0 ]; then
            echo "[`date -Iseconds`]" Backup created sucessfully. Hostname: $hn >>$ld/ST_percona_backup.log
			echo $bd/$bp/inc$bi/backup_hostname
          else
            rm -r -f "$bd/$bp/inc$bi"
            echo "[`date -Iseconds`]" Error creating backup. xtrabackup exit code: $pxe >>$ld/ST_percona_backup.log
          fi
        else
          #Searching for previous backup.
          echo "[`date -Iseconds`]" Incremental backup "$bd/$bp/inc$bi1" do not exist. Searching for previous backup. >>$ld/ST_percona_backup.log
          bd1=`ls -1trd $bd/$bp/inc* 2>/dev/null | tail -1`
          if [ -z "$bd1" ]; then
            #Perform first incremental from full backup
            echo "[`date -Iseconds`]" Will create incremental backup $bd/$bp/inc$bi from $bd/$bp/full >>$ld/ST_percona_backup.log
            mkdir -p $bd/$bp/inc$bi
            echo "[`date -Iseconds`]" Start incremental backup $bd/$bp/inc$bi from $bd/$bp/full >>$ld/ST_percona_xtrabackup.log
            ${hd}xtrabackup --defaults-file=$FDH --backup --user=$dbuser --password=$dbpass --parallel=$pp --target-dir=$bd/$bp/inc$bi --incremental-basedir=$bd/$bp/full >>$ld/ST_percona_xtrabackup.log 2>&1
            pxe=$?
            if [ $pxe -eq 0 ]; then
              echo "[`date -Iseconds`]" Backup created sucessfully. Hostname: $hn >>$ld/ST_percona_backup.log
			  echo $bd/$bp/inc$bi/backup_hostname
            else
              rm -r -f "$bd/$bp/inc$bi"
              echo "[`date -Iseconds`]" Error creating backup. xtrabackup exit code: $pxe >>$ld/ST_percona_backup.log
            fi
          else
            #Perform N -th incremental from N-1 -th backup
            echo "[`date -Iseconds`]" Will create incremental backup $bd/$bp/inc$bi from $bd1 >>$ld/ST_percona_backup.log
            mkdir -p $bd/$bp/inc$bi
            echo "[`date -Iseconds`]" Start incremental backup $bd/$bp/inc$bi from $bd1 >>$ld/ST_percona_xtrabackup.log
            ${hd}xtrabackup --defaults-file=$FDH --backup --user=$dbuser --password=$dbpass --parallel=$pp --target-dir=$bd/$bp/inc$bi --incremental-basedir=$bd1 >>$ld/ST_percona_xtrabackup.log 2>&1
            pxe=$?
            if [ $pxe -eq 0 ]; then
              echo "[`date -Iseconds`]" Backup created sucessfully. Hostname: $hn >>$ld/ST_percona_backup.log
			  echo $bd/$bp/inc$bi/backup_hostname
            else
              rm -r -f "$bd/$bp/inc$bi"
              echo "[`date -Iseconds`]" Error creating backup. xtrabackup exit code: $pxe >>$ld/ST_percona_backup.log
            fi
          fi
        fi
      fi
    fi
  else
    echo "[`date -Iseconds`]" Full backup for $bp do not exist. Removing ${bp}. Please run this script again. Exiting. >>$ld/ST_percona_backup.log
    rm -r -f "$bd/$bp"
    echo "[`date -Iseconds`]" Backup done. >>$ld/_ST_percona_backup.log
    rm $ld/e_backup_percona 2>/dev/null
    exit 2
  fi
fi
echo "[`date -Iseconds`]" Backup done. >>$ld/ST_percona_backup.log
rm $ld/e_backup_percona 2>/dev/null

exit $pxe

ST_percona_prepare.sh

此脚本是针对上面的备份文件做预处理的。此脚本包含一个日期参数用来指定恢复哪一天的备份数据。它会根据指定的日期自动的合并全量备份和增量备份，预处理后的文件默认存放在 $bd/prep 文件夹中。 此脚本比较耗时，可以每天定时执行，也可以在需要恢复数据前再手动执行。

执行示例: sh /percona/ST_percona_prepare.sh 2022-10-24

#!/bin/bash
#Script for SecureTransport - Prepare online MySQL backup for restore with Parcona XtraBackup
#Created by Genata 2019
#Prepare can be done on another machine as long as script has full access to backups.
#Note that prepare always use a copy of original directories. Make sure to have enough free space for prepare. 

if [ -z "$1" ]; then
  echo "Usage: $(basename $0) <Restore date>"
  exit 1
fi

# xtrabackup 文件目录
hd=/usr/bin/
# 日志目录
ld=/percona/logs
# 备份的文件夹目录
bd=/percona/backup
# 增量备份的天数
days=6
# 多线程数量
pp=4
#The next value is used instead of buffer_pool_size. Recomended to use 1G or 2G or 4G based on the available memory. 
um=1G

rd=`date -d "$1" +%F 2>/dev/null`
cr=$?
if [ "$cr" -gt 0 ]; then
  echo Invalid restore date
  exit $cr
fi

bp=$rd
if [ -d "$bd/$bp" ]; then
  echo Prepare full backup from $bp
  echo "[`date -Iseconds`]" Prepare full backup start... >>$ld/ST_percona_prepare.log
  rm -r -f "$bd/prep"
  rm "$bd/prep_date" 2>/dev/null
  cp -r "$bd/$bp/full" "$bd/prep"
  echo ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prepare.log
  ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prep_xtrabackup.log 2>&1
  pxe=$?
  if [ $pxe -eq 0 ]; then
    echo ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prepare.log
    ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prep_xtrabackup.log 2>&1
    pxe=$?
    if [ $pxe -eq 0 ]; then
      echo Full backup $rd prepared sucessfully.
      echo "[`date -Iseconds`]" Full backup $rd prepared sucessfully. >>$ld/ST_percona_prepare.log
      echo $rd > "$bd/prep_date"
    else
      rm -r -f "$bd/prep"
      rm "$bd/prep_date" 2>/dev/null
      echo Error preparing full backup. xtrabackup exit code: $pxe
      echo "[`date -Iseconds`]" Error preparing full backup. xtrabackup exit code: $pxe >>$ld/ST_percona_prepare.log
    fi
  else
    rm -r -f "$bd/prep"
    rm "$bd/prep_date" 2>/dev/null
    echo Error preparing full backup. xtrabackup exit code: $pxe
    echo "[`date -Iseconds`]" Error preparing full backup. xtrabackup exit code: $pxe >>$ld/ST_percona_prepare.log
  fi
  echo "[`date -Iseconds`]" Prepare full backup done. >>$ld/ST_percona_prepare.log
  exit $pxe
else
  bp=""
fi

for i in `seq 1 $days`; do
  bp1=`date -d "$rd -$i day" +%F`
  if [ -d "$bd/$bp1" ]; then
    bp=$bp1
    bi=$i
    break
  fi
done

if [ -z "$bp" ]; then
  echo Could not find backup from $rd
  echo "[`date -Iseconds`]" Could not find backup from $rd >>$ld/ST_percona_prepare.log
  exit 2
else
  if [ -d "$bd/$bp/inc$bi" ]; then
    if [ -d "$bd/$bp/full" ]; then
      echo Prepare full backup from $bp and inc $bi
      echo "[`date -Iseconds`]" Prepare incremental backup start... >>$ld/ST_percona_prepare.log
      rm -r -f "$bd/prep"
      rm "$bd/prep_date" 2>/dev/null
      cp -r "$bd/$bp/full" "$bd/prep"
      echo ${hd}xtrabackup --prepare --apply-log-only --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prepare.log
      ${hd}xtrabackup --prepare --apply-log-only --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prep_xtrabackup.log 2>&1
      pxe=$?
      if [ $pxe -eq 0 ]; then
        echo "[`date -Iseconds`]" Incremental backup step full prepared sucessfully. >>$ld/ST_percona_prepare.log
      else
        rm -r -f "$bd/prep"
        rm "$bd/prep_date" 2>/dev/null
        echo Error preparing incremental backup step full. xtrabackup exit code: $pxe
        echo "[`date -Iseconds`]" Error preparing incremental backup step full. xtrabackup exit code: $pxe >>$ld/ST_percona_prepare.log
        echo "[`date -Iseconds`]" Prepare incremental backup done. >>$ld/ST_percona_prepare.log
        exit $pxe
      fi
      if [ "$bi" -gt "1" ]; then
        for i in `seq 1 $((bi-1))`; do
          if [ -d "$bd/$bp/inc$i" ]; then
            rm -r -f "$bd/inc" 2>/dev/null
            cp -r "$bd/$bp/inc$i" "$bd/inc"
            echo ${hd}xtrabackup --prepare --apply-log-only --use-memory=$um --target-dir="$bd/prep" --incremental-dir=$bd/inc \#$bp/inc$i >>$ld/ST_percona_prepare.log
            ${hd}xtrabackup --prepare --apply-log-only --use-memory=$um --target-dir="$bd/prep" --incremental-dir=$bd/inc >>$ld/ST_percona_prep_xtrabackup.log 2>&1
            pxe=$?
            if [ $pxe -eq 0 ]; then
              echo Incremental backup step inc$i prepared sucessfully.
              echo "[`date -Iseconds`]" Incremental backup step inc$i prepared sucessfully. >>$ld/ST_percona_prepare.log
            else
              rm -r -f "$bd/prep"
              rm "$bd/prep_date" 2>/dev/null
              rm -r -f "$bd/inc"
              echo Error preparing incremental backup step inc$i. xtrabackup exit code: $pxe
              echo "[`date -Iseconds`]" Error preparing incremental backup step inc$i. xtrabackup exit code: $pxe >>$ld/ST_percona_prepare.log
              echo "[`date -Iseconds`]" Prepare incremental backup done. >>$ld/ST_percona_prepare.log
              exit $pxe
            fi
          fi
        done
      fi
      rm -r -f "$bd/inc" 2>/dev/null
      cp -r "$bd/$bp/inc$bi" "$bd/inc"
      echo ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" --incremental-dir=$bd/inc \#$bp/inc$bi >>$ld/ST_percona_prepare.log
      ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" --incremental-dir=$bd/inc >>$ld/ST_percona_prep_xtrabackup.log 2>&1
      pxe=$?
      if [ $pxe -eq 0 ]; then
        echo Incremental backup step inc$bi prepared sucessfully.
        echo "[`date -Iseconds`]" Incremental backup step inc$bi prepared sucessfully. >>$ld/ST_percona_prepare.log
        rm -r -f "$bd/inc"
      else
        rm -r -f "$bd/prep"
        rm "$bd/prep_date" 2>/dev/null
        rm -r -f "$bd/inc"
        echo Error preparing incremental backup step inc$bi. xtrabackup exit code: $pxe
        echo "[`date -Iseconds`]" Error preparing incremental backup step inc$bi. xtrabackup exit code: $pxe >>$ld/ST_percona_prepare.log
        echo "[`date -Iseconds`]" Prepare incremental backup done. >>$ld/ST_percona_prepare.log
        exit $pxe
      fi
      echo All increments were merged sucessfully.
      echo "[`date -Iseconds`]" All increments were merged sucessfully. >>$ld/ST_percona_prepare.log
      echo ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prepare.log
      ${hd}xtrabackup --prepare --use-memory=$um --target-dir="$bd/prep" >>$ld/ST_percona_prep_xtrabackup.log 2>&1
      pxe=$?
      if [ $pxe -eq 0 ]; then
        echo Incremental backup $rd prepared sucessfully.
        echo "[`date -Iseconds`]" Incremental backup $rd prepared sucessfully. >>$ld/ST_percona_prepare.log
        echo $rd > "$bd/prep_date"
      else
        rm -r -f "$bd/prep"
        rm "$bd/prep_date" 2>/dev/null
        echo Error preparing incremental backup. xtrabackup exit code: $pxe
        echo "[`date -Iseconds`]" Error preparing incremental backup. xtrabackup exit code: $pxe >>$ld/ST_percona_prepare.log
      fi
      echo "[`date -Iseconds`]" Prepare incremental backup done. >>$ld/ST_percona_prepare.log
      exit $pxe
    else
      echo Could not find full backup in $bp
      echo "[`date -Iseconds`]" Could not find full backup in $bp >>$ld/ST_percona_prepare.log
      exit 2
    fi
  else
    echo Could not find backup from $rd
    echo "[`date -Iseconds`]" Could not find backup from $rd >>$ld/ST_percona_prepare.log
    exit 2
  fi
fi

exit 0

ST_percona_restore.sh

恢复脚本，此脚本的功能是删除mysql的datadir目录，并将上一步预处理后的文件($bd/prep目录)复制到mysql的datadir目录，如果恢复失败，则会使用之前创建的备份恢复原数据库。此脚本不会在恢复数据完成后重新启动mysql服务，需要手动启动。mysql的datadir目录的文件夹权限可能也需要重新配置。

#!/bin/bash
#Script for SecureTransport - Restore online MySQL backup with Parcona XtraBackup
#Created by Genata 2019

# my.cnf 配置文件
FDH=/etc/my.cnf
# xtrabackup 执行文件目录
hd=/usr/bin/
# 日志文件地址
ld=/percona/logs
# 备份文件目录
bd=/percona/backup

mdd=`cat "$FDH" |grep datadir | cut -d "=" -f2`
bmd=mysql_`date +%F`

if [ ! -d "$bd/prep" ]; then
  echo Backup ready for restore does not exist. Please use percona_prepare.sh first.
  exit 1
fi

pb=`cat $bd/prep_date 2>/dev/null`
if [ -z "$pb" ]; then
  echo Prepared backup could not be determined. Please use percona_prepare.sh first.
  exit 2
fi

if [ -d "$bd/$bmd" ]; then
  while true; do
    read -p "Backup directory $bd/$bmd exist and will be overwritten. 
Do you want to continue? [y/N]" yn
    case $yn in
      [Yy]* ) rm -r -f $bd/$bmd 2>/dev/null; break;;
      [Nn]* ) exit;;
      * ) exit;;
    esac
  done
fi

hn=`uname -n`
bhn=`cat $bd/prep/backup_hostname 2>/dev/null`
if [ "$hn" != "$bhn" ]; then
  while true; do
    read -p "Hostname of the current machine \"$hn\" differ from the hostname of the machine where backup was created \"$bhn\". 
In a Cluster environment create a backup for each node separately and upon restore make sure you are restoring it on the same server. 
Some configuration options are node specific and database contains node ID in a table ConfigurationOption. 
If you restore database on a wrong node ST will start but will not find specific options for current node and will not work properly.
Do you want to continue? [y/N]" yn
    case $yn in
      [Yy]* ) break;;
      [Nn]* ) exit;;
      * ) exit;;
    esac
  done
fi

echo Restore start... 
echo "[`date -Iseconds`]" Restore start... >>$ld/ST_percona_restore.log

mpid=`ps -ef | grep mysql | grep -v grep | awk '{print $2}' | tr "\n" " "`
if [ ! -z "$mpid" ]; then
  $FDH/bin/stop_all
fi
mpid=`ps -ef | grep mysql | grep -v grep | awk '{print $2}' | tr "\n" " "`
if [ ! -z "$mpid" ]; then
  echo kill -9 $mpid
fi

cp -r $mdd $bd/$bmd
echo Backup of $mdd to $bd/$bmd finished.
echo "[`date -Iseconds`]" Backup of $mdd to $bd/$bmd finished. >>$ld/ST_percona_restore.log

rm -r -f $mdd
mkdir -p $mdd
${hd}xtrabackup --defaults-file=$FDH --copy-back --target-dir="$bd/prep" >>$ld/ST_percona_restore_xtrabackup.log 2>&1
pxe=$?
if [ $pxe -eq 0 ]; then
  echo Restore of backup from $pb finished successfully.
  echo "[`date -Iseconds`]" Restore of backup from $pb finished successfully. >>$ld/ST_percona_restore.log
else
  echo Error restoring backup from $pb. xtrabackup exit code: $pxe. Rollback to previous version of database from $bd/$bmd ...
  echo "[`date -Iseconds`]" Error restoring backup from $pb. xtrabackup exit code: $pxe. Rollback to previous version of database from $bd/$bmd ... >>$ld/ST_percona_restore.log
  rm -r -f $mdd
  mkdir -p $mdd
  cp -r $bd/$bmd/* $mdd
fi

echo Restore done.
echo "[`date -Iseconds`]" Restore done. >>$ld/ST_percona_restore.log

exit 0

参考链接

官方文档(支持mysql5.7及以下): https://docs.percona.com/percona-xtrabackup/2.4/index.html

备份脚本参考: https://support.axway.com/kb/180269/language/en