使用MySQL Shell进行MySQL逻辑备份

今天药忘吃喽~ 2023-10-01 08:07 29阅读 0赞

[我们的网站在][Link 1]增长，这当然是一个非常受欢迎的趋势，但也有不利的一面：我们数据库中的数据也在大幅增长。我们改变了我们的流程，定期从我们的主数据库中[归档我们最大的表数据][Link 2]，但这并没有解决所有问题，尤其是我们的逻辑备份/恢复程序仍然变得越来越繁琐。

最近，我们仔细研究了这个问题，目的是创建我们的逻辑数据库转储并再次快速恢复它们。并且肯定有一些地方可以从根本上优化！大多数更改都有一个共同点：**[MySQL shell][]**。

> 注意：这篇文章将假设一个正在运行的 MySQL 服务器，仅仅是因为我们自己使用它。它也[应该适用][Link 3]于 Percona 服务器，对于其他人（MariaDB），它留给您尝试……

### 我们使用逻辑数据库转储的目的 ###

逻辑转储（即将数据导出为一组格式方便的文本文件，以后可以再次导入）在我们的项目中有**两个主要目的**：

*  它们与二进制日志一起用作“备份”备份数据存储，以防主恢复过程（通过 ZFS 快照）由于某种原因失败，
 *  它们允许我们的开发人员在他们的本地机器上使用生产数据的子集。

这两个目的自然有**完全相反的要求**，例如我们肯定需要一个完整的备份以防万一发生严重的数据库事件，但同时，我们只需要本地开发人员导入的合理数据子集就可以了（我们不不想在我们的开发机器上浪费资源）。我们过去通过导出几个不同的数据库转储来处理这个问题`mysqldump`，一个用于每个目的，例如一个完整备份和另一个用于开发人员导入的部分备份。这当然使我们的出口运行时间更长。

但是由于 MySQL shell，我们不必再做任何这些了。

### 什么是 MySQL 外壳 ###

与 MySQL 服务器本身相比，[shell][MySQL shell]是一个相对较新的工具。总的来说，它是一个高级的 MySQL 客户端和代码编辑器。它支持通过 SQL、Python 和 JavaScript 开箱即用地编写服务器脚本。它还带有各种实用程序，用于转储和导入我们利用的数据。

与旧方法相比，MySQL shell 转储的最大优势在于，该工具从一开始就**考虑**到了**高性能**，因此：

*  它使用线程并行导出/导入数据。
 *  它可以选择将较大的表划分为也可以并行导入的数据块。
 *  它可以在导入表数据后推迟创建索引。
 *  它使用[zstd][]Facebook 的一种非常快速的压缩算法来压缩导出的数据。
 *  对我们来说，一个很大的好处是 shell 允许有选择地从转储中导入数据的子集。

我们将在下面讨论所有这些。Kenny Gryp 的这一[系列文章中][Link 4]也有关于 shell 转储/导入性能的非常好的介绍。该[基准测试][Link 5]表明，MySQL的外壳优于所有其他解决方案（特别是[mysqldump][]，[mysqlpump][]和[mydumper][]）在大多数情况下。

### 安装 MySQL shell ###

[MySQL 站点上][MySQL]有**官方二进制文件**可供下载，因此如果您使用受支持的平台之一，最简单的选择就是获取二进制文件。对于 Ubuntu / Debian Linux 用户，还有一个[官方 APT 存储库][APT]以及一个用于 RedHat / Oracle Linux的[YUM 存储库][YUM]。存储库包括外壳并具有自动版本更新的好处。对于其他一些 Linux 发行版，例如 Arch Linux，需要编译[源代码包][Link 6]（请注意，编译可能需要几个小时……）。[MacOS][]和[Windows][]二进制文件也可用。

一个特别的限制是 Linux 二进制文件[只支持][Link 7]类似 Intel 的 64 位架构。目前，您将无法在 ARM 处理器上安装 shell，因此不幸的是，这在基于 ARM 的 Linux 云实例上是行不通的。

请务必**安装**可用**的最新版本**的 shell。该工具目前正在大力开发中，并且正在快速添加新功能。

### 设置外壳 ###

我们建议在安装后查看[选项][Link 8]并配置 shell。Shell 选项存储在`$HOME/.mysqlsh/options.json`. 例如，我们的 shell-ready 帐户中有这样的内容：

{
        "history.autoSave": "true",
        "history.maxSize": "10000",
        "defaultMode": "sql",
        "pager": "less -SFw"
    }

*  前两行打开命令历史自动保存。
 *  下一行确保 shell 将以 SQL 模式启动，准备接受 SQL 查询。
 *  最后，我们为结果设置了一个漂亮的[分页][Link 9]器，具有水平滚动、仅分页较长的输出和一些运动突出显示。

此外，如果您愿意，请不要忘记**[设置 shell prompt][shell prompt]**，我们发现它高度可定制且真正有用。

### 制作数据库转储 ###

我们的主要备份脚本现在非常简单。我们定期从我们的 CRON 运行它：

#!/bin/bash
    
    # Main script for exporting logical production db backups.
    # It uses MySQL Shell to make an instance dump.
    
    current_time=`date +"%FT%T"`
    backup_dir="/mnt/backups/nere-db-backup-${current_time}"
    
    # perform a full instance backup
    mysqlsh -h localhost --js <<EOF
      util.dumpInstance('${backup_dir}',
                        { bytesPerChunk: '100M', 
                          threads: 8, 
                          compatibility: ['strip_definers'] })
    EOF

首先，它确定存储数据库转储的位置，我们在备份中使用一个简单的基于时间戳的系统。

主要部分是 MySQL shell 运行。它使用该[util.dumpInstance()][util.dumpInstance]实用程序对我们的 MySQL 服务器实例上的所有数据库进行转储。该工具创建一个目录并将所有数据库对象导出到其中的各种文件中。

我们用来创建转储的选项如下：

*  `bytesPerChunk`：我们选择将较大的表划分为大约 100 兆字节的数据块（这是压缩前的大小）。块允许导入实用程序加载具有多个线程的单个表，即更快。
 *  `threads`：我们使用 8 个线程进行转储，并建议测试哪个数字最适合您。通常这是高导出速度和对生产服务器的最小可能负面影响之间的折衷。
 *  `strip_definers`：此兼容性选项`DEFINER`从转储中的视图和其他对象中删除该子句。即使`GRANT`在目标 MySQL 实例上没有相同的用户和s，这也允许重新创建它们。

MySQL shell，当进行转储的用户有足够的权限时，会使用特殊**的锁序列来最大程度地减少**导出过程中的**锁定问题**。详细信息可以在选项下的[文档中][util.dumpInstance]找到`consistent`，让我们在此声明，由于我们在`root`帐户下运行转储，因此即使在更重的生产负载条件下，我们也从未遇到过与转储相关的锁定问题。

#### 好的，转储运行的速度有多快？ ####

这是我们在正常服务器负载期间所做的导出之一的汇总统计信息：

Duration: 00:13:43s
    Schemas dumped: 4
    Tables dumped: 281
    Uncompressed data size: 316.47 GB
    Compressed data size: 23.02 GB
    Compression ratio: 13.7
    Rows written: 266414294
    Bytes written: 23.02 GB
    Average uncompressed throughput: 384.43 MB/s
    Average compressed throughput: 27.97 MB/s

MySQL shell**在大约 14 分钟内**导出了我们 4 个数据库中的约 2.66 亿行，其中包含约 300 GB 的数据。压缩转储本身大约需要 23 GB。这还不错！

### 逻辑转储的部分导入 ###

为了允许我们的开发人员将部分生产数据库加载到他们本地开发的 MySQL 实例中，我们需要能够从完整备份中**部分加载数据**。使用[util.loadDump()][util.loadDump]MySQL shell 提供的实用程序，它实际上很容易。该工具允许加载整个转储或仅加载给定的数据库甚至特定的表。

由于我们数据库中的一些表非常庞大，而且其中的历史数据对于本地开发人员实例来说并不那么重要，因此我们进一步采用了 shell 选项，并精心制作了一个导入脚本，允许我们**仅加载给定数量的**此类表的**最新块**（最新数据）。为了解释这一点，我们需要就 MySQL shell 转储在文件中的组织方式说几句。

#### 转储文件结构的简要介绍 ####

MySQL shell 转储是其中包含以下类型文件的目录：

*  **转储元数据文件**\- `@.done.json`，`@.json`以及类似：这些文件包含的所有信息的模式，他们的表及其块转储包含。它们还存储导出转储的选项以及 SQL 命令以在需要时重新创建数据库用户帐户。
 *  **数据库元数据文件**\- `database_name.json`, `database_name.sql`: 这两个文件提供了有关转储中特定模式和 DDL SQL 命令及其定义的更多信息。
 *  **表元数据文件**\- `database_name@table_name.json`, `database_name@table_name.sql`: 同样，这两个文件提供有关特定表的元数据以及重新创建表结构的 SQL 命令。类似的模式也用于其他对象（视图等）。
 *  **（分块）表数据文件**\- `database_name@table_name@@chunk_number.tsv.zst`，`database_name@table_name@@chunk_number.tsv.zst.idx`：最后，这些文件包含[TSV 格式][TSV]的表数据，使用`zstd`. 如果数据不能表示为简单的文本，则采用 Base64 编码。（我们不确定这些`.idx`文件的用途，它们似乎包含以二进制格式编码的主 TSV 文件中的数据长度。）

这种结构的好处是很容易猜测每个文件的作用，而且**一切都是人类可读的**，它只是 SQL、JSON 和制表符分隔值。

#### 部分转储数据下载 ####

在将数据导入本地 MySQL 实例之前，我们需要将转储的相关部分下载到本地机器。我们进口的脚本使用[rsync][]了一组预先配置`--include`和`--exclude`引擎盖下选择这样做。总的来说，我们通常只能从 23 GB 的转储中下载大约 2 GB 的数据。

在与部分导入斗争了一段时间之后，我们得出了 MySQL shell 为成功加载一些数据而具有的以下重要要求：

1.  **给定表或模式的所有数据文件**（也在元数据文件中提到）必须存在于本地转储副本中。
2.  **给定表或模式的所有元数据文件**也必须存在。
3.  最后，**整个转储的元数据文件必须始终存在**于本地转储副本中。

因此，例如，如果我们想`T`从数据库中导入表`D`，该工具需要找到表的所有分块 TSV 数据文件`D.T`\+表的元数据文件`D.T`\+ 整个转储的元数据。如果我们想导入整个模式`D`，`D.*`表的所有数据+ 它们的元数据 + 模式的元数据`D`\+ 整个转储元数据必须存在。如果 shell 没有找到所需的文件，它会认为转储不完整，不幸的是只是说“没有加载数据”，然后在没有进一步提示的情况下退出。

#### 破解“最新块”导入的外壳 ####

如果我们只想从模式中导入表的子集及其所有数据并忽略所有其他表，那么上述信息就已经足够了。我们可以`rsync`从服务器获取相关数据和元数据文件，并调用`loadDump`带有`includeTables`加载它们的选项的shell 实用程序。

然而，对于一些巨大的表，我们实际上**只需要从它们加载最新的数据**，为此我们采取了不同的方法，并从导入工具的标准用法中转移了一些。

背后的想法很简单：如果我们**让工具相信它有可用的完整备份**怎么办？也许我们实际上可以只下载那些我们真正需要导入的数据块并**组成其余的**数据块？这正是我们的部分导入脚本的工作原理。

幸运的是，MySQL shell 不会交叉检查表数据文件的大小及其元数据记录。因此，**我们可以**为那些我们**不想导入的**表块**创建“假”空数据文件**。然后，我们可以调用 shell`loadDump`实用程序来加载“整个”数据库。

创建一个`zstd`压缩的空 TSV 文件很容易：

echo -n '' | zstd > database_name@table_name@@chunk_number.tsv.zst

我们还`.idx`使用二进制零值来组成随附文件（虽然我们不确定它是否真的需要），即像这样：

echo -n -e '\x0\x0\x0\x0\x0\x0\x0\x0' \
      > database_name@table_name@@chunk_number.tsv.zst.idx

#### 那么，部分导入脚本是如何工作的呢？ ####

原则上，能够部分导入我们的转储的脚本的工作方式如下：

1.  它会在服务器上找到最新的备份目录。
2.  它根据一个配置文件**计算它需要下载哪些文件**，该文件列出了完全排除的表和我们只想要最新数据块的表。
3.  它**下载的文件**使用`rsync`。现在我们有一个不完整的本地转储副本，缺少一些数据/元数据文件。这种转储还不能被 shell 加载。
4.  它获取服务器上原始备份的完整文件列表，对于本地丢失的每个文件，它使用上述命令之一创建一个“假”空文件。现在我们有了**转储**的**“完整”本地副本**，其中一些数据/元数据文件是空的。
5.  它`DROP`是将重新导入数据库的所有对象（表、视图等）。MySQL shell 要求目标数据库实例中不存在所有加载的对象。
6.  最后，它**调用 MySQL shell`loadDump`**实用程序来导入“整个”数据库。它在执行此操作时使用了几种优化，我们将在下面提到。

在 MySQL shell 中加载数据转储的最终命令类似于：

mysqlsh -h localhost -u db_user --js <<EOF
      util.loadDump('${local_backup_dir}',
                    { threads: 4,
                      includeSchemas: [ 'production_db' ],
                      skipBinlog: true, 
                      deferTableIndexes: 'all', 
                      analyzeTables: 'on', 
                      progressFile: '' })
    EOF

这里的一些选项特别有趣：

*  `threads` \- 同样，并行线程的数量需要进行试验，并且每台机器都会有所不同。
 *  `includeSchemas` \- 我们告诉 MySQL shell 从我们本地的“完整备份”导入给定的模式。
 *  `skipBinLog`\- 如果有人启用它，这会跳过将导入 SQL 查询写入二进制日志。这可以防止不必要的减慢导入速度和占用磁盘空间。
 *  `deferTableIndexes`\- 当我们使用此选项**加载数据后**让 shell 创建索引时，我们观察到最高的导入速度。
 *  `analyzeTables`\- 此选项`ANALYZE TABLE`在导入完成后对所有表进行shell 调用。这应该会加快使用重新导入的表的速度，并且不会大大减慢导入过程。
 *  `progressFile` \- 我们不支持暂时中断导入过程，因此我们不需要跟踪它的进度。

#### 数据导入期间的速度优化 ####

我们已经提到了一些速度优化，但让我们回顾一下：

*  **部分导入**——我们从不下载和导入我们在目标数据库实例上实际上不需要的数据，正如我们上面解释的那样。
 *  **推迟索引创建**\- 我们发现*在*加载数据*后*创建索引可以将整个导入速度提高大约 2 倍。但这取决于表/索引结构，因此我们始终建议测试这两种变体。
 *  **禁用二进制日志**\- 我们所有的开发人员都已禁用它们，但如果没有，它们将在导入过程中关闭。
 *  **禁用重做日志**\- 这是我们尚未讨论的内容。[重做日志][Link 10]是基于磁盘的日志，在 MySQL 服务器崩溃恢复期间使用。在导入期间禁用它会极大地加快进程，大约 2-3 倍。**但要小心！** [禁用此日志][Link 11]会使您的整个数据库服务器在服务器崩溃时容易遭受不可恢复的数据丢失。如果数据库服务器中有重要数据，则永远不要使用此选项。另外，在使用它时，您应该始终准备好备份。

#### 概括 ####

有了所有这些优化，我们典型的本地开发导入（约 180 个表中约 2 GB 的压缩数据）在我自己的机器上**运行不到 10 分钟**，不包括下载时间。它在 MySQL 服务器数据目录中创建了大约 15 GB 的数据和索引。

我们觉得我们可能已经达到了目前在逻辑转储速度优化方面的技术可能的极限。导出和导入都在多个并行线程中运行，并且每个线程的吞吐量都最大化。本地开发商进口的未来在于进一步减少进口的数据。我们对当前的解决方案非常满意，因为它也为未来开启了全新的可能性，例如导入过程中的数据匿名化。

总的来说，我们认为 MySQL shell 实用程序很棒，它们现在是我们备份/恢复策略的重要组成部分。

[Link 1]: https://www.nejremeslnici.cz/
[Link 2]: https://dev.to/nejremeslnici/archiving-large-mysql-tables-part-i-intro-4im1
[MySQL shell]: https://dev.mysql.com/doc/mysql-shell/8.0/en/
[Link 3]: https://www.percona.com/blog/2020/12/16/mysql-shell-8-0-22-data-export-import-utilities-tested-with-mysql-8-5-7-5-6/
[zstd]: https://github.com/facebook/zstd
[Link 4]: https://mysqlserverteam.com/mysql-shell-dump-load-part-1-demo/
[Link 5]: https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/
[mysqldump]: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
[mysqlpump]: https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html
[mydumper]: https://github.com/maxbube/mydumper
[MySQL]: https://dev.mysql.com/downloads/shell/
[APT]: https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/
[YUM]: https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
[Link 6]: https://aur.archlinux.org/packages/mysql-shell/
[MacOS]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-macos-quick.html
[Windows]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-windows-quick.html
[Link 7]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html
[Link 8]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-configuring-options.html
[Link 9]: https://www.man7.org/linux/man-pages/man1/less.1.html#OPTIONS
[shell prompt]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-prompt-themes.html
[util.dumpInstance]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
[util.loadDump]: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
[TSV]: https://en.wikipedia.org/wiki/Tab-separated_values
[rsync]: https://rsync.samba.org/
[Link 10]: https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html
[Link 11]: https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-disable-redo-logging