使用MySQL Shell进行逻辑数据库备份
Pon Suresh Pandian [hudson 译]
2020年12月9日
Mysqldump是MySQL的流行逻辑备份工具,它最初是由Igor Romanenko写的。 Mysqldump执行逻辑备份（一组SQL语句）。默认情况下，mysqldump不会转储information_schema表，也不会使用performance_schema。但mysqldump的主要缺点是，它在执行备份和恢复时只使用一个线程。（即使您的服务器也有64核）。为了克服这个缺点，MySQL引入了新的实用程序：MySQL Shell。 本文将介绍这些新的备份实用程序。
MySQL Shell概述
MySQL shell 是MySQL服务器的强大高级客户端和代码编辑器 。MySQL shell 8.0.21包含一些令人兴奋的新实用程序，用于创建逻辑转储并对整个数据库实例（包括用户）执行逻辑恢复。 MySQL shell 8.0.22包含特定表的逻辑备份和还原。
实用程序
- util.dumpInstance()–转储整个数据库实例，包括用户
- util.dumpSchemas() –转储一组模式
- util.loadDump() –将转储加载到目标数据库
- util.loadTables() –加载特定的表和视图。
util.dumpInstance()
dumpInstance()实用程序将转储MySQL数据目录中显示的所有数据库。在进行转储时，它将排除information_schema、mysql、ndbinfo、performance_schema和sys模式。
语法
util.dumpInstance(outputUrl[, options])
它将转储到本地文件系统，outputUrl是一个字符串，指定要放置转储文件的本地目录的路径。您可以指定绝对路径或相对于当前工作目录的路径。 在该实用程序中，有一个预运行选项（dryRun），用于检查模式和查看兼容性问题，然后运行转储，并应用适当的兼容性选项来解决问题。
选项
让我们看看这个转储实用程序的一些重要选项。
ocimds:[True|False]
当此选项设置为true时，它将检查CREATE TABLE语句中的数据字典、索引字典和加密选项是否在DDL文件中注释掉，以确保所有表都位于MySQL数据目录中，并使用默认模式加密。 它还将检查除InnoDB之外的CREATE TABLE语句中的任何存储引擎，查看是否向用户或角色授予了不合适的权限，以及是否存在其他兼容性问题。 如果发现任何不一致的SQL语句，将引发异常并暂停转储。 因此，我们建议在开始转储过程之前，使用dryRun选项列出转储中项目的所有问题。使用兼容性选项自动修复转储输出中的问题。
注意：此选项仅支持实例转储实用程序和模式转储实用工具
示例1
MySQL localhost:3306 ssl cart JS > util.dumpInstance("/home/vagrant/production_backup", {ocimds: true,compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.22
NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade().
NOTE: User 'backupuser'@'localhost' had restricted privileges (RELOAD, SUPER, CREATE TABLESPACE) removed
NOTE: User 'root'@'127.0.0.1' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE) removed
NOTE: User 'root'@'::1' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE) removed
NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY) removed
ERROR: Table 'cart'.'sales' uses unsupported storage engine MyISAM (fix this with 'force_innodb' compatibility option)
Compatibility issues with MySQL Database Service 8.0.22 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL.
Util.dumpInstance: Compatibility issues were found (RuntimeError)
所以在我的购物车数据库中有一个myisam表。干运行选项显然会抛出错误。 如果要在转储文件中自动修复这些错误，请在命令中将兼容性选项作为参数传递。
示例2
MySQL localhost:3306 ssl cart JS > util.dumpInstance("/home/vagrant/production_backup", {dryRun: true ,ocimds: true,compatibility: ["strip_restricted_grants","force_innodb"]})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.22
NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade().
NOTE: User 'backupuser'@'localhost' had restricted privileges (RELOAD, SUPER, CREATE TABLESPACE) removed
NOTE: User 'root'@'127.0.0.1' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE) removed
NOTE: User 'root'@'::1' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE) removed
NOTE: User 'root'@'localhost' had restricted privileges (RELOAD, SHUTDOWN, FILE, SUPER, CREATE TABLESPACE, PROXY) removed
NOTE: Table 'cart'.'sales' had unsupported engine MyISAM changed to InnoDB
Compatibility issues with MySQL Database Service 8.0.22 were found and repaired. Please review the changes made before loading them.
Writing global DDL files
Writing users DDL
Writing DDL for schema `cart`
Writing DDL for table `cart`.`salaries`
Writing DDL for table `cart`.`sales`
Writing DDL for table `cart`.`t1`
Preparing data dump for table `cart`.`salaries`
Data dump for table `cart`.`salaries` will be chunked using column `id`
Preparing data dump for table `cart`.`sales`
Data dump for table `cart`.`sales` will be chunked using column `id`
Preparing data dump for table `cart`.`t1`
NOTE: Could not select a column to be used as an index for table `cart`.`t1`. Chunking has been disabled for this table, data will be dumped to a single file.
现在试运行很好，没有例外。让我们运行转储实例命令以进行实例备份。 在进行导出之前，目标目录必须为空。如果该目录尚未存在于其父目录中，则实用程序会创建它。
示例3
MySQL localhost:3306 ssl cart JS > util.dumpInstance("/home/vagrant/production_backup", {compatibility: ["strip_restricted_grants","force_innodb"],threads : 12})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Writing DDL for schema `cart`
Writing DDL for view `cart`.`price`
Writing DDL for table `cart`.`dummy`
Writing DDL for table `cart`.`salaries`
Writing DDL for schema `sbtest`
Writing DDL for table `sbtest`.`sbtest1`
Writing DDL for table `sbtest`.`sbtest10`
.
.
.
1 thds dumping - 99% (624.55K rows / ~625.40K rows), 896.15K rows/s, 10.13 MB/s uncompressed, 3.73 MB/s compressed
Duration: 00:00:00s
Schemas dumped: 2
Tables dumped: 18
Uncompressed data size: 7.14 MB
Compressed data size: 2.79 MB
Compression ratio: 2.6
Rows written: 624550
Bytes written: 2.79 MB
Average uncompressed throughput: 7.14 MB/s
Average compressed throughput: 2.79 MB/s
上面我们使用了一个兼容性选项。因此，在进行转储时，它会将myisam表转换为innodb，并将它们存储到文件中。
日志
[vagrant@centos14 production_backup]$ cat cart@sales.sql
-- MySQLShell dump 1.0.1 Distrib Ver 8.0.22 for Linux on x86_64 - for MySQL 8.0.22 (MySQL Community Server (GPL)), for Linux (x86_64)
--
-- Host: localhost Database: cart Table: sales
-- ------------------------------------------------------
-- Server version 5.7.32
--
-- Table structure for table `sales`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE IF NOT EXISTS `sales` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`address` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
如果使用mysqldump，它会将输出存储到单个文件中。但在这里，它会生成更多的文件，我们将在下面解释。 如下是备份目录中的可用文件。
[vagrant@centos13 production_backup]$ ls -lrth
total 52K
-rw-r-----. 1 vagrant vagrant 707 Nov 6 02:36 @.json
-rw-r-----. 1 vagrant vagrant 287 Nov 6 02:36 cart.json
-rw-r-----. 1 vagrant vagrant 240 Nov 6 02:36 @.sql
-rw-r-----. 1 vagrant vagrant 240 Nov 6 02:36 @.post.sql
-rw-r-----. 1 vagrant vagrant 2.6K Nov 6 02:36 @.users.sql
-rw-r-----. 1 vagrant vagrant 733 Nov 6 02:36 cart@salaries.sql
-rw-r-----. 1 vagrant vagrant 486 Nov 6 02:36 cart.sql
-rw-r-----. 1 vagrant vagrant 575 Nov 6 02:36 cart@salaries.json
-rw-r-----. 1 vagrant vagrant 8 Nov 6 02:36 cart@salaries@0.tsv.zst.idx
-rw-r-----. 1 vagrant vagrant 8 Nov 6 02:36 cart@salaries@@1.tsv.zst.idx
-rw-r-----. 1 vagrant vagrant 47 Nov 6 02:36 cart@salaries@0.tsv.zst
-rw-r-----. 1 vagrant vagrant 24 Nov 6 02:36 cart@salaries@@1.tsv.zst
-rw-r-----. 1 vagrant vagrant 252 Nov 6 02:36 @.done.json
- @.json文件包含服务器详细信息和用户列表、数据库名称及其字符集。
- card.json文件包含视图、SP、函数名以及表列表。
- @.sql和@.post.sql文件包含MySQL服务器版本详细信息。
- @.users.sql文件包含数据库用户列表。
- cart@salaries.sql文件包含表结构。
- card.sql文件包含一个数据库语句。
- cart@salaries.json文件包含列名和字符集。
- cart@salaries@0.tsv.zst.idx文件是一个二进制文件。它存储表索引统计信息。
- cart@salaries@0.tsv.zst文件是一个二进制文件，它存储数据。
- @.done.json文件包含备份结束时间和数据文件大小（KB）。
util.dumpSchemas()
该实用程序将转储参数中指定的的特定模式。
语法
util.dumpSchemas(schemas, outputUrl[, options])
示例
MySQL localhost:3306 ssl cart JS > util.dumpSchemas(["cart"], "/home/vagrant/production_backup",{compatibility: ["strip_restricted_grants","force_innodb"],threads :12})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error or not be completely consistent if schema changes are made while dumping.
Global read lock has been released
Writing global DDL files
Writing DDL for table `cart`.`price_tag`
Writing DDL for schema `cart`
Writing DDL for table `cart`.`salaries`
Writing DDL for table `cart`.`sales`
NOTE: Table 'cart'.'sales' had unsupported engine MyISAM changed to InnoDB
Preparing data dump for table `cart`.`price_tag`
Data dump for table `cart`.`price_tag` will be chunked using column `id`
Data dump for table `cart`.`price_tag` will be written to 1 file
Preparing data dump for table `cart`.`salaries`
Data dump for table `cart`.`salaries` will be chunked using column `id`
Data dump for table `cart`.`salaries` will be written to 2 files
Preparing data dump for table `cart`.`sales`
Data dump for table `cart`.`sales` will be chunked using column `id`
Running data dump using 12 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `cart`.`sales` will be written to 1 file
1 thds dumping - 150% (3 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 3
Uncompressed data size: 53 bytes
Compressed data size: 0 bytes
Compression ratio: 53.0
Rows written: 3
Bytes written: 0 bytes
Average uncompressed throughput: 53.00 B/s
Average compressed throughput: 0.00 B/s
util.dumpTables
如果您想转储特定的表，我们可以使用dumpTables实用程序。 对于较大的表，mysqldump将花费更多的时间。使用dumpTables实用程序可减少时间。
语法
util.dumpTables(schema, tables, outputUrl[, options])
示例
util.dumpTables("sbtest", [ "sbtest14", "sbtest16" ], "/home/vagrant/specific_table",{dryRun: true})
MySQL localhost:33060+ ssl sbtest JS > util.dumpTables("sbtest", [ "sbtest14", "sbtest16" ], "/home/vagrant/specific_table",{threads: 12})
Acquiring global read lock
Global read lock acquired
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing DDL for table `sbtest`.`sbtest16`
Writing DDL for table `sbtest`.`sbtest14`
Preparing data dump for table `sbtest`.`sbtest16`
Data dump for table `sbtest`.`sbtest16` will be chunked using column `id`
Preparing data dump for table `sbtest`.`sbtest14`
Data dump for table `sbtest`.`sbtest14` will be chunked using column `id`
Running data dump using 12 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `sbtest`.`sbtest16` will be written to 1 file
Data dump for table `sbtest`.`sbtest14` will be written to 1 file
1 thds dumping - 99% (78.07K rows / ~78.08K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 892.39 KB
Compressed data size: 348.91 KB
Compression ratio: 2.6
Rows written: 78068
Bytes written: 348.91 KB
Average uncompressed throughput: 892.39 KB/s
Average compressed throughput: 348.91 KB/s
转储加载实用程序
转储加载实用程序提供数据流到远程存储、表或表块的并行加载、进度状态跟踪、恢复和重置功能，以及在转储仍在进行时进行并发加载的选项。
注：dump loading utility 使用 LOAD DATA LOCAL INFILE语句，因此在导入时需要全局启用这个 local_infile 参数
转储加载实用程序检查sql_require_primary_key系统变量是否设置为ON，如果是，如果转储文件中的表没有主键，则返回错误。
语法
util.loadDump(url[, options])
示例
MySQL localhost:3306 ssl sbtest JS > util.loadDump("/home/vagrant/specific_table", {progressFile :"/home/vagrant/specific_table/log.json",threads :12})
Loading DDL and Data from '/home/vagrant/specific_table' using 12 threads.
Opening dump...
Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22
Checking for pre-existing objects...
Executing common preamble SQL
[Worker006] Executing DDL script for `sbtest`.`sbtest1`
[Worker004] Executing DDL script for `sbtest`.`sbtest12`
2 thds loading 100% (892.39 KB / 892.39 KB), 0.00 B/s, 0 / 2 tables done[Worker001] sbtest@sbtest12@@0.tsv.zst: Records: 39034 Deleted: 0 Skipped: 0 Warnings: 0
[Worker005] sbtest@sbtest1@@0.tsv.zst: Records: 39034 Deleted: 0 Skipped: 0 Warnings: 0
Executing common postamble SQL
2 chunks (78.07K rows, 892.39 KB) for 2 tables in 1 schemas were loaded in 1 sec (avg throughput 892.39 KB/s)
0 warnings were reported during the load.
默认情况下，只有在表完全加载后才创建表的全文索引，这样可以加快导入速度。 您还可以选择在导入期间禁用索引创建，然后创建索引。 转储加载实用程序跨多个线程导入以最大化并行性。如果转储文件是由MySQL Shell的转储实用程序压缩的，则转储加载实用程序将处理解压缩。 可以选择要导入或从导入中排除的各个表或模式。 您可以选择在导入过程中使用SET sql_log_bin=0语句跳过目标MySQL实例上的二进制日志记录。
结论
这是 MySQL 8.0中功能强大的实用程序之一。现在可以从MySQL 5.6中转储并将这些转储加载到MySQL 5.7或8.0中。但是从MySQL 5.6转储时不支持转储用户帐户。在我的下一篇博客中，我们将比较MySQLdump和MySQL shell实用程序的备份/恢复速度。
