MySQL/MariaDB全量增量备份及合并脚本

作者：Sender 来源：wavecn.com 发布日期：2023-08-27 最后修改日期：2023-08-27

DBA 自制工具这个话题中，最常见的是写脚本完成数据库自动化运维工作。而其中，最常见的必然就是备份脚本了。

数据库备份的方法与数据库产品本身的情况紧密相关。比如 Microsoft SQL Server，本身就具备全量备份、增量备份以及定时执行等运维功能，要考虑的就只是如何把备份转移的问题。我之前写过一篇用脚本执行MSSQL数据库备份后，转移备份文件到文件共享服务器的自动化运维介绍文章：

MSSQL数据库自动备份和自动复制转移备份

具体实践中，还可以在数据库服务器上挂载网络存储然后直接备份到网络存储上。

但对于其它一些没有整合提供备份功能的数据库产品，DBA 的动手能力就很重要了。比如本篇涉及的 MySQL 和 MariaDB 系列。

很长一段时间以来，MySQL/MariaBackup 没有提供足够有效的备份功能，只提供了一个直接 dump 出 SQL 的程序。但这种方式只能适用于少量数据的场合，更不用说想实现增量备份了。

直到 Percona 公司发布了 Percona XtraBackup，情况才有所改观。这个备份程序后来被 MariaDB 引入并另命名为 MariaBackup。为统一，本文一律叫 XtraBackup。该程序实现了 MySQL/MariaDB（包括 Percona 公司的 MySQL 分支 PerconaDB）的备份还原功能。

虽然后来 MySQL 提供了 MySQL Enterprise Backup 这个备份程序，但这是作为收费企业版的一部分来提供的，并不是面向所有用户。

插入一句：Percona 是长期且有实力的的 MySQL 分支版本提供商，对MySQL 和 MariaDB 的发展都提供了不少的反向输出。其网站上也有大量的 MySQL/MariaDB 运维知识：

https://www.percona.com/

XtraBackup 这个程序对 MySQL/MariaDB 数据库使用 InnoDB 存储引擎的表提供了无阻塞、在线的全量和增量备份功能，同时也可以兼顾 myisam 和 aria 引擎的表，还能支持 Galera Cluster 和 MGR 集群。具体使用方法也比较简单，基于程序提供的命令参数即可实现相关操作。

理解本文需要对 MySQL/MariaDB 以及 XtraBackup 备份程序有一定的熟悉，并清楚如何编写 bash 脚本。

一、使用要点

XtraBackup 的使用介绍文章已经很多，但首先还是要通读官方手册：

https://docs.percona.com/percona-xtrabackup/8.0/index.html

各路 DBA 大神写的使用介绍也很多，我这里不重复太多，只说我掌握的几个重点：

1、相对于 MySQL/MariaDB 的数据引擎插件体系，XtraBackup 并不支持全部的数据引擎，比如 TokuDB 引擎或者 MySQL Cluster 使用的 NDB 引擎。

如果数据库中存在 XtraBackup 不支持的引擎，就要针对性地对这些引擎相关的数据另外采取导出 SQL命令的方式实现备份。

2、XtraBackup 运行时需要给一个数据库用户，也就是要同时给出密码在命令行里面。众所周知这不是一个好的安全实践。因此，这个数据库用户必须是专用的，只授予必要的权限：

RELOAD
LOCK TABLES
REPLICATION CLIENT
PROCESS
SUPER
如果需要通过MySQL 5.6以上版本的 Transportable TableSpace(TTS) 实现恢复/迁移单个表，需要给予 CREATE TABLESPACE 权限。
如果操作的是 PerconaDB，需要授权对且仅对 PERCONA_SCHEMA.xtrabackup_history 表进行 CREATE/INSERT/SELECT 操作的权限。

3、XtraBackup 运行时要单独启动一个 InnoDB 引擎，因此要占一定量的内存，默认值128MiB是远远不够的。该内存量从我自己的实践看，必须能完整包含InnoDB字典表文件和最大的表的数据文件大小，且后者最好是两倍大小或以上，即：

预留内存最小值=ibdata1文件大小+最大的IBD文件大小*2

如果 MySQL/MariaDB 的配置已经把内存消耗所剩无几，那么 XtraBackup 就无法正常工作或速度巨慢。因此要计算好预留内存给 XtraBackup 工作期间用。

较新版本的 XtraBackup 专门针对这个内存问题增加了一些自动化内存分配的运行参数，也即 --use-free-memory-pct 和 --estimate-memory 参数，按官方文档指出应用这些参数后能获得较高的备份效率，读者可以自行测试。

4、如果是对高可用性集群数据库进行备份，比如 MySQL MGR、MariaDB Galera Cluster、Percona XtraDB Cluster 等，建议在集群内设立专门的不参与数据服务的节点用于备份过程，且该节点也同时作为专门的数据供给节点用于集群内数据同步，这样可以最大限度减少备份操作与正常数据库服务之间的相互影响，服务器的内存预留分配也能更灵活。

二、备份策略

备份策略设计是老生常谈了，一般地，每周全量、每天增量是标准策略。

在此基础上，通过 XtraBackup 提供的功能，每周全量备份可以采取执行完整的全量备份或合并增量备份到之前的全量备份的方式形成新的全量备份这两种方式之一。

由于考虑到单独进行全量备份相当耗时，多是采取合并增量的方式形成每周的全量备份。

需要注意，XtraBackup 的备份过程速度实际并不快。如果是较大规模的数据库（有大量的表文件）或工作压力较高的数据库（大量写入），XtraBackup 在执行备份时的扫描过程会比较慢，尤其是对于前者。如果是数据库是集群模式就更慢。

因此，日常增量备份的时间安排要做好测算，避免本来是闲时执行的增量备份一直执行到系统繁忙了都还未能结束。

另外，在进行备份合并后再产生当天增量备份的过程，应硬性设置为顺序执行，而不能分开安排，防止出现并发执行。

总之，备份任务的执行时间点和预留时长是需要观察、评估和安排的。

三、脚本实例

脚本共有5个，均保存在/root/backup_scripts/，分别是：

1、挂载和卸载 NFS 备份，这是因为我直接备份到远程文件存储。如果是备份到本机存储则不需要。

挂载（mount_backup.sh）

#!/bin/sh

/usr/bin/mount -t nfs 192.168.99.99:/BACKUP.DB /backup

卸载（umount_backup.sh）

#!/bin/sh

/usr/bin/umount /backup

2、全量备份（full_backup.sh）

#!/bin/sh

# 挂载存储
/bin/bash /root/backup_scripts/mount_backup.sh
# 设定日志名称
logname="/backup/logs/full_`date +%Y%m%d`.log"

echo "### FULL BACKUP BEGINS..." > ${logname}
# 运行全量备份，收集过程日志
/usr/bin/mariabackup --backup --use-memory=2GB --user=xtrabackup --password=xtrabackup --target-dir=/backup/full/ >> ${logname} 2>&1
# 检查备份是否成功
success=`tail -n 1 ${logname} | grep "completed OK\!" | wc -l`
# 如果备份不成功，向服务器管理员发邮件
if [[ "$success" = "0" ]]
then
  /usr/bin/tail -n 30 ${logname} | /bin/mail -s "xtrabackup full backup failed!" root@localhost
else
  echo "### FULL BACKUP FINISHED." >> ${logname}
fi
# 卸载存储
/bin/bash /root/backup_scripts/umount_backup.sh

流程图如下：

full backup

3、增量备份（inc_backup.sh）

#!/bin/sh

# 挂载存储
/bin/bash /root/backup_scripts/mount_backup.sh

# 设置日志名称
logname="/backup/logs/daily_`date +%Y%m%d`.log"

echo "### BACKUP DAILY BEGINS..." > ${logname}

# 把今天是星期几转换为数值
wn=`date +"%u"`
wnm=$((wn-1))

inctarget="/backup/inc${wn}"

# 检查今天的备份是否已存在。如果存在则直接退出和发邮件给服务器管理员
if [[ -d ${inctarget} ]]
then
  echo "### BACKUP DAILY: DIRECTORY ${inctarget} exits, backup aborted." >> ${logname}
  echo `ls /backup -hl` | /bin/mail -s "BACKUP DAILY: DIRECTORY ${inctarget} EXISTS!" root@localhost
  /bin/bash /root/backup_scripts/umount_backup
  exit
fi

# 需要检查获得上一天的增量备份并从该增量开始继续增量，而且还有可能找不到，要直接从根备份开始增量。
base=""
i=$wnm
while [ $i -ge 1 ]
do
  # 检查是否存在上一天或更早的增量备份目录，如果找到就以该天为增量基础
  bc="/backup/inc${i}"
  if [[ -d ${bc} ]]
  then
    base=${bc}
    break
  fi
  i=$(( $i-1 ))
done

# 如果找不到，就以完整备份为增量基础
if [[ "$base" = "" ]]
then
  base="/backup/full"
fi

echo "### BACKUP WITH ${inctarget} and ${base}" >> ${logname}
/usr/bin/mariabackup --backup --use-memory=1GB --user=xtrabackup --password=xtrabackup --target-dir=${inctarget} --incremental-basedir=${base} >> ${logname} 2>&1

# 检查备份操作执行情况并反馈邮件到ROOT用户。
# 检查日志最后一行是否包含关键的提示。这个提示内容是xtrabackup的文档特意指出的。
success=`tail -n 1 ${logname} | grep "completed OK\!" | wc -l`

# 如果不是备份成功的提示，则发邮件到管理员，成功备份不作处理。
if [[ "$success" = "0" ]]
then
  /usr/bin/tail -n 30 ${logname} | /bin/mail -s "BACKUP DAILY: Failed!" root@localhost
fi

echo "### BACKUP DAILY FINISHED." >> ${logname}

# 卸载存储
/bin/bash /root/backup_scripts/umount_backup.sh

流程图如下：

inc backup

4、增量合并（merge_backup.sh）

#!/bin/sh

# 挂载存储
/bin/bash /root/backup_scripts/mount_backup.sh

# 设定日志文件名
logname="/backup/logs/merge_`date +%Y%m%d`.log"

# 首先要对全备份进行准备操作，操作结果保存到日志
echo "### MERGE BEGIN: PREPARE FULL" > ${logname}

/usr/bin/mariabackup --prepare --use-memory=1G --target-dir=/backup/full >> ${logname} 2>&1

success=`tail -n 1 ${logname} | grep "completed OK\!" | wc -l`

# 检查操作是否成功，不成功时发送邮件到ROOT用户
if [[ "$success" = "1" ]]
then

  # 按从周一到周日，遍历处理全部增量备份
  # 有可能期间某一天是没有增量备份的，因此需要先检查，形成现有的增量备份清单后再尝试进行合并增量。

  dirlist=""
  x=1
  while [ $x -le 7 ]
  do
    # 检查增量目录是否存在，若存在就记录到列表。
    incdir="/backup/inc${x}"
    if [ -d ${incdir} ]
    then
      dirlist="${dirlist} ${incdir}"
    fi
    x=$(($x+1))
  done

  echo "### MERGE LIST: $dirlist" >> ${logname}

  #循环处理找到的所有增量目录，合并到完整备份
  for incdir in ${dirlist}
  do
    echo "### MERGE ENGAGING: PREPARE $incdir TO FULL" >> ${logname}
    /usr/bin/mariabackup --prepare --use-memory=1G --target-dir=/backup/full --incremental-dir=${incdir} >> ${logname} 2>&1
    success=`tail -n 1 ${logname} | grep "completed OK\!" | wc -l`

    # 检查合并操作是否成功，成功则删除增量备份目录。不成功则发送邮件到服务器管理员，然后跳出循环。
    if [[ "$success" = "1" ]]
    then
      echo "### MERGE ENGAGED: $incdir DONE, REMOVING DIRECTORY" >> ${logname}
      rm -rf ${incdir}
    else
      echo "### MERGE FAILED: $incdir" >> ${logname}
      /usr/bin/tail -n 30 ${logname} | /bin/mail -s "xtrabackup merge inc backup failed!" root@localhost
      break
    fi
  done

  # 切换当前目录后才卸载存储
  cd /

  echo "### MERGE FINISHED." >> ${logname}
else
  # 这里向服务器管理员提示合并一开始的准备就已经失败。
  /usr/bin/tail -n 30 ${logname} | /bin/mail -s "xtrabackup begin merge failed!" root@localhost
fi

/bin/bash /root/backup_scripts/umount_backup.sh

流程图如下：

merge backup

四、实施

最后是实施过程，由于过程并不复杂但操作输出极为冗长无聊，仅做文字描述。

1、利用 crond，在 crontab 设置定时任务每天运行增量，每周日运行增量合并。注意周日当天合并后仍然要产生增量备份，合并过程并不是新的备份。

2、手工运行一次全量备份，产生基本的完整数据。

3、手工运行一次增量备份，检查增量是否正常。

4、手工运行一次备份合并，检查合并是否正常。

5、观察每天定时任务是否正常运行。

6、观察每周定时合并是否正常运行。

2023-08-21 也谈DBA自制运维工具

2023-08-27 MySQL/MariaDB全量增量备份及合并脚本

2023-09-12 DBA自制工具：水平分表时批量生成DDL语句