备份简介

备份一般分为逻辑备份 Logical Backup ( Hot Backup )和物理备份 Physical Backup ( Cold Backup )。

逻辑备份 由恢复数据所需的 SQL 语句组成，例如 CREATE DATABASE，CREATE TABLE 和 INSERT。

逻辑备份的特点：

无需停机作业
还原弹性较佳
硬件无关,可任意还原到指定的数据库
档案较大, 备份与还原时间较长
无法备份 log 与配置文件
无事务的表（Non-transaction table）必须被锁定
可能影响联机操作处理效能

物理备份 是通过复制单个数据文件或目录来执行的。

物理备份的特点：

需停机作业( MariaDB 10 可执行 Hot Physical Backup )
目录/文件(Directories/Files) 操作
执行速度较快
档案 size 较小
可备份日志和配置文件

因此，逻辑备份和物理备份主要区别如下：

逻辑备份更加灵活，因为可以在其它不同的硬件配置、MariaDB 版本甚至其它 DBMS 上恢复数据，而物理备份不能在明显不同的硬件、不同的 DBMS 或可能甚至不同的 MariaDB 版本上导入。

逻辑备份可以在数据库和表级别执行，而物理数据库是目录和文件级别。在 MyISAM 和 InnoDB 存储引擎中，每个表都有一组等效的文件。（在 MariaDB 5.5 之前的版本中，默认情况下，多个 InnoDB 表存储在同一文件中，在这种情况下，无法按表进行备份。）

逻辑备份的大小大于等效物理备份的大小。

与等效的物理备份相比，逻辑备份花费更多的时间进行备份和还原。

日志文件和配置文件不是逻辑备份的一部分

备份工具：mysqldump 和 Mariabackup

mysqldump

mysqldump 简述

mysqldump 执行逻辑备份。这是执行备份和还原的最灵活的方法，并且是当数据量较小时的理想选择。

对于大型数据集，备份文件可能很大，并且恢复时间很长。

mysqldump 将数据转储为 SQL 格式（它也可以转储为其它格式，例如 CSV 或 XML），然后可以轻松地将其导入另一个数据库。假设转储中没有版本或特定于 DBMS 的语句，则可以将数据导入到其它版本的 MariaDB，MySQL 甚至是其它 DBMS 中。

mysqldump 将触发器(triggers)与表一起转储，因为它们是表定义的一部分。但是，存储过程，视图和事件(stored procedures, views, and events)不是，并且需要额外的参数来显式地重新创建（例如–routines 和–events）。但是，过程和函数也是系统表的一部分。

使用语法

备份语法

mysqldump db_name > backup-file.sql

具体例如：

shell> mysqldump [options] db_name [tbl_name ...] > backup-file.sql # 指定数据库的某些表
shell> mysqldump [options] --databases db_name ... > backup-file.sql # 指定某几个数据库
shell> mysqldump [options] --all-databases > backup-file.sql # 备份所有数据库

还原语法

mysql db_name < backup-file.sql

mysqldump 使用实例

（实际 demo 使用说明，后续一些指令说明可能会接续使用此演示。若不感兴趣可略过）

在 MariaDB 的命令窗口执行以下语句（创建示例表及其数据）：

CREATE DATABASE testbak;

CREATE TABLE testbak.tablebak (
  `create_time` char(19) NOT NULL,
  `create_user` varchar(20) NOT NULL,
  `update_time` char(19) DEFAULT NULL,
  `update_user` varchar(20) DEFAULT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

INSERT INTO  testbak.tablebak
(`create_time`,`create_user`,`update_time`,`update_user`)
VALUES
('2020/02/21 15:41:20','david','2020/02/21 15:43:20','david'),
('2020/02/21 15:41:20','david','2020/02/21 15:43:20','david'),
('2020/02/21 15:41:20','david','2020/02/21 15:43:20','david'),
('2020/02/21 15:41:20','david','2020/02/21 15:43:20','david'),
('2020/02/21 15:41:20','david','2020/02/21 15:43:20','david');
CREATE DATABASE testbres;

作用时创建一个名为 testbak 的数据库，里面一个名为 tablebak 的表，添加了 5 条数据。并创建了一个空的数据库 testres，用于测试还原。

完成之后在终端中，使用 mysqldump 备份该数据库。

mysqldump -u test -p testbak > backup-testbak.sql -- >后是备份路径和文件名，此处就在用户名根目录下。

mysqldump备份数据库

还原的话需要指定还原的数据库名称。否则会报错类似ERROR 1049 (42000): Unknown database 'testres'

还原语句：

在终端中执行

mysql -u test -p  testres< backup-testbak.sql

mysqldump还原数据库

更多 mysqldump 的使用，可访问官网https://mariadb.com/kb/en/mysqldump/了解。

Mariabackup

Mariabackup 简介：

Mariabackup 是 MariaDB 提供的开源工具，用于执行 InnoDB，Aria 和 MyISAM 表的物理在线备份。对于 InnoDB，可以进行“热在线”备份。

当然，mariabackup 同样适用于 MySQL。

支持的功能

MariaDB 一开始使用 Percona 的 XtraBackup (因为采用 Xtra Storage)
衍生自/并取代 Percona XtraBackup
内建于 MariaDB 10.1.23 版之后
使用静态数据加密备份/还原表。
使用 InnoDB 页面压缩备份/恢复表。
支持 Galera Cluster（MariaDB Galera Cluster 是仅在 Linux 上运行的同步多主群集(synchronous multi-master cluster)。）
Microsoft Windows 支持。
从 MariaDB 10.2.16 和 MariaDB 10.3.8 开始，使用 MyRocks 存储引擎备份/还原表。

使用前先安装

ubuntu 下，终端执行：

sudo apt-get install mariadb-backup

其它系统可去官网https://mariadb.com/kb/en/mariabackup-overview/#installing-mariabackup 查看对应下载方式。

语法示例：

mariabackup

备份的选项

mariabackup --backup
      --target-dir /path/to/backup \
      --user user_name --password user_passwd

使用者信息，可以放到配置文件中

新增参数[mariabackup]并添加使用者和密码：

[mariabackup]
user=
password=

mariabackup 的备份

完整备份(full backup)：

示例：备份现有的 MariaDB：

mariabackup --backup \
   --target-dir=/home/sanotsu/mariadb/backup/ \
   --user=test --password=P@ssw0rd;

有两点值得提醒一下：
1、–target-dir 后面跟的是数据库备份的路径，最好是提前创建(如果用 sudo 执行则不需要)；
2、可能会出现操作系统无法处理文件/活页夹的问题，可尝试加 sudo，或者把活页夹的所有权从组 root 更改为 mariadb。

完整备份出错：

完整备份出错

正常执行：

完整备份正常执行

可以比较备份的活页夹和原始数据库的活页夹：

备份的活页夹和原始数据库的活页夹的比较

部分备份(partial backup)

上述是备份的全部，如果只想备份某个/某些数据库，需要再指定–databases 参数。
如果还需要指定备份某个数据库的某张表，就需要再指定–tables 参数。
这两个参数都支持正则表达式，可以更方便筛选。

例如：执行

mariabackup --backup \
   --target-dir=/home/sanotsu/mariadb/partialBackup/ \
   --databases='testbak' \
   --tables='tab_*' \
   --user=test --password=P@ssw0rd;

执行部分备份

可以查看备份结果：

部分备份结果

类似的部分备份的参数还有：

databases-exclude:指定不需要备份的数据库；
tables-exclude:指定不需要备份的表。

增量备份(incremental backup)：

增量备份的基础是完整备份，想要进行增量备份，首先需要进行一次完整备份，再执行增量备份时，就会在原本的完整备份的基础上，备份尚未备份的部分，而不是重新再备份所有，即递增而不是覆盖。

语法也简单，在完整备份下再加一个–incremental-basedir 参数指定基于哪一个备份的递增：

mariabackup --backup \
   --target-dir=/home/sanotsu/mariadb/incrementalBackup/ \
   --incremental-basedir=/home/sanotsu/mariadb/backup/ \
   --user=test --password=P@ssw0rd;

注意 –incremental-basedir 的值，每一个新的增量备份应该以上一个备份的目标路径为基准。

因为在执行这次增量备份之前，并没有新增或删除过其它数据库/表，但是也可以看出他们的区别。

下图是增量备份(左)和完整备份(右)的活页夹，可以从占用 size 大小，和指定数据库的指定表的相关信息看出，的确不是完全覆盖的备份：

增量备份结果

mariabackup 还原

还原实际上会有两步需要执行，一是准备（prepare），二是还原（restore）。

准备作业：

准备的作用是检查用于还原的备份的数据文件一致性。如果不一致，InnoDB 会中断避免数据库损坏。

不同的备份准备作业略有些不同

完整备份的准备：

语法：

mariabackup --prepare \
   --target-dir=/home/sanotsu/mariadb/backup/

如果权限不够，可能无法读取文件，解决方法与备份时说明一致，加 sudo 或者把该文件/夹 root 权限赋予 mariadb

备份准备的权限不够的报错及解决

部分备份的准备：

部分备份的还原前准备除了检查数据文件一致性外，还依赖 InnoDB 的可传输表空间。为了让 MariaDB 导入此类表空间，InnoDB 会寻找带有.cfg 扩展名的文件。为了让 Mariabackup 创建这些文件，还需要–export 在准备步骤中添加选项。

语法：

mariabackup --prepare --export \
   --target-dir=/home/sanotsu/mariadb/backup/

注意：在 MariaDB 10.2.8 及之前的版本, Mariabackup 不支持 –export 选项。更多区别可查看https://mariadb.com/kb/en/partial-backup-and-restore-with-mariabackup/#preparing-the-backup

增量备份的准备：

在 MariaDB 10.2 及其之后，准备作业是这样：

先准备完整备份

mariabackup --prepare \
   --target-dir=/home/sanotsu/mariadb/backup/

再准备增量备份：

mariabackup --prepare \
   --target-dir=/home/sanotsu/mariadb/backup/ \
   --incremental-dir=/home/sanotsu/mariadb/incrementalBackup/ \

这里的–incremental-dir 是增量备份时–target-dir 的值，–target-dir 是–incremental-basedir 的值。
注意多个层级的增量备份还原时的准备，有层级依赖，不要跨级。
即先还原 inc1，再还原 inc2、inc3……即依照增量顺序修改–incremental-dir 的值就好。

还原作业：

完整备份和增量备份

还原就稍微麻烦一点，不过以上完整备份和增量备份的还原方式都通用。
需要依次执行以下步骤：

1、停止（stop） MariaDB 服务进程；

2、确保用于还原的存储数据的目录为空。（mariadb 服务器系统参数 datadir）

查询用于还原的存储数据的位置

3、使用--copy-back或--move-back参数，执行还原操作，指令语法如：

mariabackup --copy-back \
    --target-dir=/home/sanotsu/mariadb/backup/

–copy-back 选项允许您保留原始备份文件。
–move-back 选项实际上将备份文件移到 datadir，因此原始备份文件会丢失。

4、调整数据目录的所有者，以匹配 MariaDB 服务器（通常 mysql 是两者）的用户和组。

例如，要将文件的所有权递归更改给 mysql 用户和组，执行

sudo chown -R mysql:mysql /var/lib/mysql/

5、启动 MariaDB 服务进程。

示例如下（部分显示删减）：

sudo service mariadb stop  # 关闭mariadb服务进程
sudo rm -rf /var/lib/mysql/*  #清空mariadb datadir目录下数据
mariabackup --copy-back --target-dir=/home/sanotsu/mariadb/backup/ #执行还原
sudo chown -R mysql:mysql /var/lib/mysql/ #给还原后的文件/夹附加mysql(mariadb的实际用户名)用户/组权限
sudo service mariadb start #启动mariadb服务进程

完整备份和增量备份的还原步骤

如果一切正常则还原成功。

部分备份的还原

注意，部分备份的还原，和完整备份的还原过程完全不同(quite different)。

部分备份不是功能齐全的数据目录。InnoDB 系统表空间中的数据字典仍将包含未包含在备份中的数据库和表的条目。

每个单独的 InnoDB 每表文件空间表空间文件都必须手动导入到目标服务器中。用于导入文件的过程将取决于是否涉及分区。

如果单表没有分区

恢复单个非分区表

通过丢弃表的原始表空间，将表.ibd 和.cfg 文件从备份位置复制到表的相关表空间位置，然后告诉服务器导入表空间，可以导入未分区的表。

1、在备份中找到该表的.ibd 和.cfg 文件；
2、目标服务器上，您需要创建表的副本。使用与 CREATE TABLE 在原始服务器上创建表相同的语句。
3、使用ALTER TABLE <table_name style="box-sizing: border-box;">DISCARD TABLESPACE</table_name>丢弃新表的表空间。
4、将该表的.ibd 和.cfg 文件从备份位置复制需要还原的 mariadb server 的相关路劲。
5、确定文件放到了正确的位置，使用ALTER TABLE <table_name style="box-sizing: border-box;">IMPORT TABLESPACE</table_name>语句导入新表的表空间。

如果单表有涉及到分区

恢复单个分区和分区表

创建占位符表，丢弃占位符表的原始表空间，将分区的文件.ibd 和.cfg 文件从备份位置复制到占位符表的相关表空间位置，然后告诉服务器导入表空间来导入分区表。此时，服务器可以将占位符表的表空间与分区的表空间交换。

1、将保存的表空间文件从原始服务器复制到目标服务器；
2、将分区表空间导入到目标服务器上。
- 2.1、首先，如果它尚不存在，那么我们需要在目标服务器上创建一个与原始服务器上的分区表匹配的分区表：
- 2.2、然后，使用该表作为模型，我们需要使用不使用分区的相同结构创建该表的占位符。这可以通过以下CREATE TABLE <table_name style="box-sizing: border-box;">AS SELECT</table_name> 语句完成
3、针对每个分区进行以下步骤
- 3.1、使用 ALTER TABLE <table_name style="box-sizing: border-box;">DISCARD TABLESPACE</table_name> 丢弃占位符表的表空间;
- 3.2、将下一个分区的.ibd 和.cfg 文件复制到占位符表目标 MariaDB 服务器上表的相关目录中
- 3.3、文件位于目标服务器上的正确目录中后，使用 ALTER TABLE <table_name style="box-sizing: border-box;">IMPORT TABLESPACE</table_name> 来导入新表的表空间。操作成功，则可以在占位符表中查看到包含源服务器上分区中的数据
- 3.4、通过 ALTER TABLE <table_name style="box-sizing: border-box;">EXCHANGE PARTITION 语句将分区从占位符转移到目标表，若成功，目标表将包含源表中的第一个分区。</table_name>
- 3.5、对要导入的每个分区重复以上 3.X 过程。对于每个分区，我们需要丢弃占位符表的表空间，然后将分区表的表空间导入到占位符表中，然后在占位符表和目标表的分区之间交换表空间。
- 3.6、所有分区都完成之后，目标表应该包含所有的对应的数据，则可以从数据库中删除占位符表。

单表(部分)备份，使用 mariabackup 的物理备份还原还是比较麻烦的，使用其它方法可能更简单便捷

更多 mariabackup 的部分备份/还原信息，可见官网

更多 mariabackup 的使用可见官网

（八）MariaDB的备份还原(mysqldump及mariabackup)