MariaDB 和 MySQL 数据的备份和恢复 (包含脚本) (生产环境可用)

使用 MySQL 数据库的小伙伴，可以先看 mariabackup，xtrabackup 和 mariabackup 的备份方式基本是一样的。文档的最后也有 xtrabackup 官方文档的连接。

mariabackup 官方参考文档：https://mariadb.com/kb/en/mariabackup-overview/
参考文档：https://my.oschina.net/u/1867229/blog/1631793

mariabackup

Mariabackup 在 MariaDB 10.1.23 和 MariaDB 10.2.7 中首次发布。它在 MariaDB 10.1.26 和 MariaDB 10.2.10 中首次作为 GA 发布。是从 Percona XtraBackup 2.3.8 派生出来的。

与 Percona XtraBackup 相比的差异：

• Percona XtraBackup 将其 InnoDB redo log 文件复制到 xtrabackup_logfile 文件，而
  Mariabackup 使用 ib_logfile0 文件。
• Mariabackup 不支持 Percona XtraBackup 基于 libgcrypt 的备份加密。
• 从 Mariabackup 到 innobackupex 没有符号链接，就像 xtrabackup 一样。 相反，Mariabackup
  具有 --innobackupex 命令行选项以启用与 innobackupex 兼容的选项。
• 不支持 --compact 和 --rebuild_indexes 选项。
• 在 MariaDB 10.1.24 中，从 Mariabackup 中删除了对 --stream=tar 的支持。
• xbstream 实用程序已重命名为 mbstream。 但是，要在创建备份时选择此输出格式，Mariabackup 的 --stream
  选项仍然需要 xbstream 值。
• Mariabackup 不支持无锁 binlog。

mariabackup 常用选项

官方文档：https://mariadb.com/kb/en/mariabackup-options/

-H,--host

定义要备份的 MariaDB 服务器的主机。使用此选项可实现远程备份。

-u,--user

定义用于连接到 MariaDB 服务器的用户名。

-p,--password

定义用于连接到 MariaDB 服务器的密码。

-S, --socket

定义用于连接到本地数据库的套接字。

--backup

备份数据库。备份将写入 --target-dir 选项设置的目标目录。

--databases=“example.table1 example.table2”

注意： 部分备份不推荐使用，因为在还原部分备份数据时，关于 innodb 表空间的还原会比较麻烦。

定义要备份的数据库和表，多个用空格分隔。也可以使用 --databases-exclude 选项设置不想备份的特定数据库或表。还可以将要备份的或者不想备份的数据库或表写入文件，使用 --databases-file 指定文件来进行备份时的限制。

--defaults-file

定义默认配置文件(my.cnf)的路径。

--defaults-group

定义要在配置文件(my.cnf)中读取的选项组。例如：如果发现自己经常执行压缩操作，则可以通过以下方式设置 --compress-threads 和 --compress-chunk-size 选项：

[mariabackup] 
compress_threads = 12 
compress_chunk_size = 64K

--parallel

定义用于并行数据文件传输的线程数。

--incremental-basedir

指定基于哪一个备份进行增量备份。如果 .ibd 文件中的页面比指定目录中的备份更新，则 Mariabackup 将只复制这些页面。

--history [= name]

定义是否要在 PERCONA_SCHEMA 库 xtrabackup_history 表中记录备份历史记录。例如：

MariaDB [(none)]> use PERCONA_SCHEMA
Database changed
MariaDB [PERCONA_SCHEMA]> show tables;
+--------------------------+
| Tables_in_PERCONA_SCHEMA |
+--------------------------+
| xtrabackup_history       |
+--------------------------+
MariaDB [PERCONA_SCHEMA]> select * from xtrabackup_history\G;
*************************** 1. row ***************************
            uuid: d96e470f-a5e0-11eb-b967-000c2933a360
            name: 
       tool_name: mariabackup
    tool_command: --defaults-file=/etc/my.cnf --user=root --password=... --socket=/tmp/mysql.sock --parallel=4 --backup --history --target-dir=/data/mysql_back/mysql-3306/data/incr_2021-04-27_00-00-05_2 --incremental-basedir=/data/mysql_back/mysql-3306/data/full_2021-04-26_00-19-17_1
    tool_version: 10.2.15-MariaDB
ibbackup_version: 10.2.15-MariaDB
  server_version: 10.2.15-MariaDB-log
      start_time: 2021-04-27 00:00:05
        end_time: 2021-04-27 00:00:06
       lock_time: 0
      binlog_pos: filename 'mysql-bin.000003', position '342', GTID of the last change '0-1-26'
 innodb_from_lsn: 1672735
   innodb_to_lsn: 1672735
         partial: N
     incremental: Y
          format: file
      compressed: N
1 row in set (0.00 sec)

ERROR: No query specified

--prepare

准备现有备份以还原到 MariaDB 服务器。 --backup 备份的文件尚未准备好在服务器上使用。将数据还原到 MariaDB 之前，首先需要准备备份。

--incremental-dir

准备增量备份以还原到 MariaDB 服务器。将此选项与 --prepare 选项一起使用会使操作增加，而不是完全覆盖。

--export

如果在--prepare 阶段提供了这个选项，那么它会告诉 Mariabackup 为每个表空间的每个 InnoDB 文件创建 .cfg 文件。

–\copy-back

将备份还原到数据目录。必须先停止 MariaDB 服务器，然后再运行此命令。数据目录必须为空。如果不为空并且要使用备份覆盖数据目录，请使用 --force-non-empty-directories 选项。

--move-back

将备份还原到数据目录后，删除备份文件。(可理解为移动)

示例

完全备份示例：

mariabackup --backup \
--target-dir=/mysql_back/mysql_all_back/mysql3306 \
--user=root --password=123456

增量备份示例：

mariabackup --backup \
--target-dir=/mysql_back/mysql_zeng_back/mysql3306/20210425 \
--incremental-basedir=/mysql_back/mysql_all_back/mysql3306 \
--user=root --password=123456

完全备份准备示例：

mariabackup --prepare \
--target-dir=/mysql_back/mysql_all_back/mysql3306/

增量备份准备示例：

mariabackup --prepare \
--target-dir=/mysql_back/mysql_all_back/mysql3306/ \
--incremental-dir=/mysql_back/mysql_zeng_back/mysql3306/20210425

数据还原示例：

mariabackup --copy-back \
--target-dir=/mysql_back/mysql_all_back/mysql3306/

备份脚本

#!/usr/bin/env bash
#
#####################################################################
#    参考链接https://my.oschina.net/u/1867229/blog/1631793          #
#    原文使用的是 Percona XtraBackup 备份                            #
#    我这里使用 Mariabackup 备份                                     #
#####################################################################


# 读取配置文件中的所有变量值, 设置为全局变量
# 获取脚本目录
work_dir=`dirname $0`
# 配置文件位置
conf_file="$work_dir/../conf/mysql_backup.conf"
# mysql 用户和密码
user=`sed -n '/^user=/s/.*=//p' $conf_file`
password=`sed -n '/^password=/s/.*=//p' $conf_file`
# mysql socket 文件位置
socket_file=`sed '/^socket=/!d;s/.*=//' $conf_file |uniq`
# mysql 备份目录
backup_dir=`sed -n '/^backup_dir=/s/.*=//p' $conf_file`
# percona-xtrabackup 备份软件路径
xtrabackup_dir=`sed -n '/^xtrabackup_dir=/s/.*=//p' $conf_file`
# 全备是在一周的第几天
full_backup_week_day=`sed -n '/^full_backup_week_day=/s/.*=//p' $conf_file`
# mysql 全备前缀标识
full_backup_prefix=`sed -n '/^full_backup_prefix=/s/.*=//p' $conf_file`
# mysql 增量备前缀标识
increment_prefix=`sed -n '/^increment_prefix=/s/.*=//p' $conf_file`
# mysql 配置文件
mysql_conf_file=`sed -n '/^mysql_conf_file=/s/.*=//p' $conf_file`
# 备份错误日志文件
error_log=`sed -n '/^error_log=/s/.*=//p' $conf_file`
# 备份索引文件
index_file=`sed -n '/^index_file=/s/.*=//p' $conf_file`

# 备份日期
backup_date=`date +%F`
# 备份日期
backup_time=`date +%H-%M-%S`
# 备份日期
backup_week_day=`date +%u`

# 创建相关目录
log_dir=$work_dir/../log
var_dir=$work_dir/../var
mkdir -p $backup_dir
mkdir -p $log_dir
mkdir -p $var_dir


# 全量备份
function full_backup() {
  backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}

  mkdir -p $backup_dir/$backup_folder
  $xtrabackup_dir/bin/mariabackup \
    --defaults-file=$mysql_conf_file \
    --user=$user \
    --password=$password \
    --socket=$socket_file \
    --parallel=4 \
    --backup --history \
    --target-dir=$backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1
  return $?
}

# 增量备份
function increment_backup() {
  backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
  incr_base_folder=`sed -n -n '$p' $index_file | \
                   awk -F '[, {}]*' '{print $3}' | \
                   awk -F ':' '{print $2}'`

  mkdir -p $backup_dir/$backup_folder
  $xtrabackup_dir/bin/mariabackup \
    --defaults-file=$mysql_conf_file \
    --user=$user \
    --password=$password \
    --socket=$socket_file \
    --parallel=4 \
    --backup --history \
    --target-dir=$backup_dir/$backup_folder \
    --incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log 2>&1
  return $?
}

# 删除之前的备份(一般在全备完成后使用)
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 log_dir=$log_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s.log\n", log_dir, $2)}}' | \
    /bin/bash
}

# 备份索引文件
function backup_index_file() {
  cp $index_file ${index_file}_$(date -d "1 day ago" +%F)
}

# 备份索引文件
function send_index_file_to_remote() {
  echo 'send index file ok'
}

# 添加索引, 索引记录了当前最新的备份
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
}

# 记录错误消息到文件
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
}

# 清空索引
function purge_index_from_file() {
  > $index_file
}

# 清空错误日志信息
function purge_err_log() {
  > $error_log
}

# 打包备份
function tar_backup_file() {
  echo "tar $1 ok"
}

# 发送备份到远程
function send_backup_to_remote() {
  echo "send $1 remote ok"
}
 
# 判断是应该全备还是增量备份
# 0:full, 1:incr
function get_backup_type() {
  full_backup_week_day=`sed -n '/^full_backup_week_day=/s/.*=//p' $conf_file`
  backup_type=0
  if [ "$full_backup_week_day" -eq `date +%u` ]; then
    backup_type=0
  else
    backup_type=1
  fi
  if [ ! -n "`cat $index_file`" ]; then
    backup_type=0
  fi
  return $backup_type
}

# 测试配置文件正确性
function test_conf_file() {
  # 判断每个变量是否在配置文件中有配置，没有则退出程序
  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 "$full_backup_week_day" ]; then echo 'fail: configure file full_backup_week_day 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 "$mysql_conf_file" ]; then echo 'fail: configure file mysql_conf_file 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
}

# 执行
function run() {
  # 检测配置文件值
  test_conf_file

  # 判断是执行全备还是曾量备份
  get_backup_type
  backup_type=$?
  case $backup_type in
    0 )
      # 全量备份
      full_backup 
      backup_ok=$?
      if [ 0 -eq "$backup_ok" ]; then
      # 全备成功
        # # 打包最新备份
        # tar_backup_file $full_backup_prefix
        # # 将tar备份发送到远程
        # send_backup_to_remote $full_backup_prefix
        # 备份索引文件
        backup_index_file
        # # 发送索引文件到远程
        # send_index_file_to_remote
        # 清除之前的备份
        delete_before_backup
        # 清除索引文件
        purge_index_from_file
        # 添加索引, 索引记录了当前最新的备份
        append_index_to_file $full_backup_prefix
      else
      # 全备失败
        # 删除备份目录
        rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
        # 记录错误日志
        logging_backup_err $full_backup_prefix
      fi
      ;;
    1 )
      # 增量备份
      increment_backup
      backup_ok=$?
      if [ 0 -eq "$backup_ok" ]; then
      # 增量备份成功
        # # 打包最新备份
        # tar_backup_file $increment_prefix
        # # 将tar备份发送到远程
        # send_backup_to_remote $increment_prefix
        # 添加索引, 索引记录了当前最新的备份
        append_index_to_file $increment_prefix
      else
      # 增量备份失败
        # 删除备份目录
        rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
        # 记录错误日志
        logging_backup_err $increment_prefix
      fi
      ;;
  esac
}

run

脚本配置文件

# mysql 用户名
user=root

# mysql 密码
password=123456

# 备份路劲
backup_dir=/data/mysql_back/mysql-3306/data

# percona-xtrabackup 备份软件路径
xtrabackup_dir=/usr/local/mysql

# 全备是在一周的第几天
full_backup_week_day=1

# 全量备信息名称前缀
full_backup_prefix=full

# 增量备信息名称前缀
increment_prefix=incr

# mysql配置文件
mysql_conf_file=/etc/my.cnf

# mysql socket 文件
socket=/tmp/mysql.sock

# 错误日志文件(更具此文件知道备份是否成功)
# format:
# {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30}
error_log=/data/mysql_back/mysql-3306/var/mysql_increment_hot_backup.err

# 索引文件
# format:
# {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr}
index_file=/data/mysql_back/mysql-3306/var/mysql_increment_hot_backup.index

备份脚本测试

1 同步时间为国内标准时间

[root@master ~]# ntpdate -s ntp1.aliyun.com

2 启动或重启 mariadb

[root@master ~]# service mysqld restart

3 写入数据

MariaDB [(none)]> create database back_all_testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use back_all_testdb;
Database changed
MariaDB [back_all_testdb]> create table test (id int,name varchar(10));
Query OK, 0 rows affected (0.00 sec)

#创建存储过程
MariaDB [back_all_testdb]> delimiter $$
MariaDB [back_all_testdb]> create procedure pre()
    -> begin
    -> declare i int;
    -> set i=1;
    -> while i<100 do
    -> insert into test SET id=i, name=concat('wpf');
    -> set i=i+1;
    -> end while;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

MariaDB [back_all_testdb]> delimiter ;

#执行存储过程
MariaDB [back_all_testdb]> call pre();
Query OK, 1 row affected (0.03 sec)

#查看是否写入
MariaDB [back_all_testdb]> select count(*) from test;
+----------+
| count(*) |
+----------+
|       99 |
+----------+
1 row in set (0.01 sec)

4 创建备份依赖路径

mkdir -p /data/mysql_back/mysql-3306/{conf,script}

5 编辑脚本配置文件

vim /data/mysql_back/mysql-3306/conf/mysql_backup.conf

6 编辑脚本

vim /data/mysql_back/mysql-3306/script/mysql_backup.sh

7 创建索引文件

不创建也可以，我主要是为了防止第一次执行脚本的时候，输出额外报错信息。

[root@master ~]# touch /data/mysql_back/mysql-3306/var/mysql_increment_hot_backup.index

8 修改时间为周一

因为我脚本配置文件中定义的是每周一进行完全备份

date -s 20210426

9 执行脚本

[root@master ~]# sh /data/mysql_back/mysql-3306/script/mysql_backup.sh

10 查看是否备份成功

#未发现报错日志
[root@master ~]# ls /data/mysql_back/mysql-3306/var/
mysql_increment_hot_backup.index

#备份数据也正常
[root@master ~]# ls /data/mysql_back/mysql-3306/data/
full_2021-04-26_00-00-23_1
[root@master ~]# ls /data/mysql_back/mysql-3306/data/full_2021-04-26_00-00-23_1/
aria_log.00000001  back_all_testdb  ib_buffer_pool  ib_logfile0  performance_schema      xtrabackup_checkpoints
aria_log_control   backup-my.cnf    ibdata1         mysql        xtrabackup_binlog_info  xtrabackup_info

11 再次写入数据

MariaDB [(none)]> create database back_zeng_testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use back_zeng_testdb;
Database changed
MariaDB [back_zeng_testdb]> create table test (id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

MariaDB [back_zeng_testdb]> delimiter $$
MariaDB [back_zeng_testdb]> create procedure pre()
    -> begin
    -> declare i int;
    -> set i=0;
    -> while i<100 do
    -> insert into test SET id=i, name=concat('haha');
    -> set i=i+1;
    -> end while;
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

MariaDB [back_zeng_testdb]> delimiter ;
MariaDB [back_zeng_testdb]> call pre();
Query OK, 1 row affected (0.04 sec)

MariaDB [back_zeng_testdb]> select count(*) from test;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

11 修改时间为周二，以进行增量备份

date -s 20210427

12 执行脚本

[root@master ~]# sh /data/mysql_back/mysql-3306/script/mysql_backup.sh

13 查看是否备份成功

#查看索引文件，发现有增量备份的记录
[root@master ~]# cat /data/mysql_back/mysql-3306/var/mysql_increment_hot_backup.index
{week_day:1,          dir:full_2021-04-26_00-00-05_1,          type:full,          date:2021-04-26}
{week_day:2,          dir:incr_2021-04-27_00-00-15_2,          type:incr,          date:2021-04-27}

#查看增量备份数据，发现正常
[root@master ~]# ls /data/mysql_back/mysql-3306/data/incr_2021-04-27_00-00-15_2/
aria_log.00000001  back_all_testdb  back_zeng_testdb  ibdata1.delta  ib_logfile0  PERCONA_SCHEMA      xtrabackup_binlog_info  xtrabackup_info
aria_log_control   backup-my.cnf    ib_buffer_pool    ibdata1.meta   mysql        performance_schema  xtrabackup_checkpoints

14 全量备份准备

[root@master ~]# mariabackup --prepare --target-dir=/data/mysql_back/mysql-3306/data/full_2021-04-26_00-00-05_1/

15 删除或移动 mariadb 原来的数据目录

[root@master ~]# service mysqld stop
[root@master ~]# rm -rf /data/mysql/db_file/*

16 还原数据

[root@master ~]# mariabackup --copy-back --target-dir=/data/mysql_back/mysql-3306/data/full_2021-04-26_00-00-05_1/

17 查看是否还原成功

[root@master ~]# chown  -R mysql:mysql /data/mysql/db_file/
[root@master ~]# service mysqld start

#查看数据，数据还原成功
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| back_all_testdb    |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use back_all_testdb
Database changed
MariaDB [back_all_testdb]> select count(*) from test;
+----------+
| count(*) |
+----------+
|       99 |
+----------+
1 row in set (0.00 sec)

18 增量备份还原

[root@master ~]# service mysqld stop
[root@master ~]# rm -rf /data/mysql/db_file/*

#准备增量备份数据
[root@master ~]# mariabackup --prepare --target-dir=/data/mysql_back/mysql-3306/data/full_2021-04-26_00-00-05_1/ --incremental-dir=/data/mysql_back/mysql-3306/data/incr_2021-04-27_00-00-15_2/

#还原数据
[root@master ~]# mariabackup --copy-back --target-dir=/data/mysql_back/mysql-3306/data/full_2021-04-26_00-00-05_1/

19 查看是否还原成功

[root@master ~]# chown -R mysql:mysql /data/mysql/db_file/
[root@master ~]# service mysqld start

#查看数据，数据还原成功
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| PERCONA_SCHEMA     |
| back_all_testdb    |
| back_zeng_testdb   |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use back_zeng_testdb
Database changed
MariaDB [back_zeng_testdb]> select count(*) from test;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

MariaDB [back_zeng_testdb]> select * from test limit 10;
+------+------+
| id   | name |
+------+------+
|    0 | haha |
|    1 | haha |
|    2 | haha |
|    3 | haha |
|    4 | haha |
|    5 | haha |
|    6 | haha |
|    7 | haha |
|    8 | haha |
|    9 | haha |
+------+------+
10 rows in set (0.00 sec)

至此，脚本测试完成。

PS：脚本中的压缩和远程传输功能暂未测试。

·

附加：Percona XtraBackup

Percona XtraBackup 是世界上唯一的开源免费 MySQL 热备份软件。

xtrabackup 选项和 mariabackup 选项的含义基本相同，这里就不再赘述了；使用MySQL数据库的小伙伴，或者有兴趣的可以查看 xtrabackup 选项的官方文档。

xtrabackup 的备份方式和 mariabackup 的备份方式基本一样，不过 xtrabackup 官方对于备份的步骤和输出讲解的还是比较详细的；不管先学哪一个都可以。