MySQL Shell Dump/Load

在MySQL Shell8.0.17中引入了一个多线程CSV导入工具：util.importTable()。在使用之前需要先开启local_infile参数，下面就是一个简单的示例

1

util.importTable("/tmp/productrange.csv", {schema: "mydb", table: "products", dialect: "csv-unix", skipRows: 1, showProgress: true})

更多关于util.importTable()的用法，可参考util.importTable

在此基础上，MySQL Shell8.0.21又引入了逻辑转储还原工具，具有易用性、高性能、集成性的特点，接下来将重点谈谈该工具。工具中包含：

util.dumpInstance()：转储整个数据库实例，包含用户
util.dumpSchemas()：转储一组schema
util.loadDump()：将转储数据加载到目标实例

MySQL Shell dump/load具有多种强大的功能：

多线程转储，将大表拆分chunk
并行加载chunk，结合MySQL8.0.21中Disable redo_log提升加载性能
加载时转储仍在进行中
暂停和恢复加载
内置压缩，可以选择zstd或gzip
加载数据时延迟创建二级索引
直接从OCI对象存储中进行转储和加载

测试案例

对于util.dumpSchemas()或util.dumpInstance()需要满足以下要求：

原实例和目标实例版本需要MySQL5.7或更高
实例对象名或schema对象名字符集需要为latin或utf8
转储程序会将不安全的数据类型(例如：文本形式存储的列)转换为base64，因此列的大小不能超过max_allowed_packet的0.74倍
设置ocimds=true选项，确保与MySQL数据库服务的兼容性
仅对innodb存储引擎的表保证数据一致性，所有表必须使用innodb存储引擎

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63


  MySQL  localhost  JS > util.dumpSchemas(["sysbench"],"/service/sysbench.dump",{ocimds:true, threads:88, compatibility: ["strip_definers", "strip_restricted_grants"]})
Checking for compatibility with MySQL Database Service 8.0.21
NOTE: MySQL Server 5.7 detected, please consider upgrading to 8.0 first. You can check for potential upgrade issues using util.checkForServerUpgrade().
Compatibility checks finished.
Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing DDL for schema `sysbench`
Writing DDL for table `sysbench`.`sbtest10`
Writing DDL for table `sysbench`.`sbtest1`
Preparing data dump for table `sysbench`.`sbtest1`
Writing DDL for table `sysbench`.`sbtest2`
Writing DDL for table `sysbench`.`sbtest3`
Writing DDL for table `sysbench`.`sbtest4`
Writing DDL for table `sysbench`.`sbtest5`
Writing DDL for table `sysbench`.`sbtest6`
Writing DDL for table `sysbench`.`sbtest7`
Writing DDL for table `sysbench`.`sbtest8`
Writing DDL for table `sysbench`.`sbtest9`
Data dump for table `sysbench`.`sbtest1` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest10`
Data dump for table `sysbench`.`sbtest10` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest2`
Data dump for table `sysbench`.`sbtest2` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest3`
Data dump for table `sysbench`.`sbtest3` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest4`
Data dump for table `sysbench`.`sbtest4` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest5`
Data dump for table `sysbench`.`sbtest3` will be written to 6 files
Data dump for table `sysbench`.`sbtest1` will be written to 5 files
Data dump for table `sysbench`.`sbtest5` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest6`
Data dump for table `sysbench`.`sbtest6` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest7`
Data dump for table `sysbench`.`sbtest7` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest8`
Data dump for table `sysbench`.`sbtest8` will be chunked using column `id`
Preparing data dump for table `sysbench`.`sbtest9`
Data dump for table `sysbench`.`sbtest9` will be chunked using column `id`
Running data dump using 88 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `sysbench`.`sbtest10` will be written to 6 files
Data dump for table `sysbench`.`sbtest2` will be written to 7 files
Data dump for table `sysbench`.`sbtest5` will be written to 5 files
Data dump for table `sysbench`.`sbtest6` will be written to 6 files
Data dump for table `sysbench`.`sbtest9` will be written to 6 files
Data dump for table `sysbench`.`sbtest4` will be written to 6 files
Data dump for table `sysbench`.`sbtest8` will be written to 7 files
Data dump for table `sysbench`.`sbtest7` will be written to 6 files
1 thds dumping - 102% (8.83M rows / ~8.65M rows), 402.54K rows/s, 78.07 MB/s uncompressed, 35.47 MB/s compressed    
Duration: 00:00:12s                                                                                             
Schemas dumped: 1                                                                                               
Tables dumped: 10                                                                                       
Uncompressed data size: 1.71 GB                                                                       
Compressed data size: 776.71 MB                                                                                 
Compression ratio: 2.2                                                                                          
Rows written: 8833335                                                                                           
Bytes written: 776.71 MB                                                                                        
Average uncompressed throughput: 135.71 MB/s                                                                    
Average compressed throughput: 61.55 MB/s

选项	可选值	说明
dryRun	[true/false]	显示转储内容的信息，以及有关 MySQL 数据库服务兼容性检查的结果，但不执行转储
osBucketName	[string]	转储到Oracle Cloud时对象存储存储桶名称
osNamespace	[string]	由对象存储存储存储桶命名的 Oracle Cloud命名空间
threads	[int]	用于从 MySQL 实例转储数据的并行线程数
maxRate	[string]	转储期间每个线程的数据读取吞吐量的最大字节数
showProgress	[true/false]	显示转储的进度信息
compression	[string]	转储使用的压缩格式，默认为ztsd
excludeSchemas	[string]	排除指定的schema
excludeTables	[string]	排除指定的table
users	[true/false]	转储包含用户及权限
events	[true/false]	转储包含事件
routines	[true/false]	转储包含函数和存储过程
triggers	[true/false]	转储包含触发器
defaultCharacterSet	[string]	转储使用的会话字符集
tzUtc	[true/false]	在转储开始时将时区设置为UTC
consistent	[true/false]	在转储期间锁定实例进行备份，默认为true。通过FLUSH TABLE WITH READ LOCK进行全局锁定，转储会话设置RR隔离级别以及启动一致性快照事务，当所有会话启动事务后，释放全局读锁并启动备份锁进行转储
ddlOnly	[true/false]	仅包括转储中转储项的DDL，并且不转储数据
dataOnly	[true/false]	仅包括转储数据
chunking	[true/false]	转储时将表拆分为多个chunk
bytesPerChunk	[string]	写入每个chunk数据文件的近似字节数
ocimds	[true/false]	启用检查和修改与MySQL数据库服务兼容
compatibility	[force_innodb/strip_definers/strip_restricted_grants/strip_role_admin/strip_tablespaces]	针对兼容性要求做出的要求，可设置多值。force_innodb为更改表为innodb；strip_definers为从视图、例程、事件和触发器中删除子句，以便使用默认定义器创建这些对象；strip_restricted_grants为从grant中删除指定权限；strip_role_admin为从grant语句中删除role_admin权限；strip_tablespaces为从grant语句中删除tablespace子句；

在导出的文件中，主要分为三种文件：json文件主要记录schema或表的的结构信息，例如:schema下的表，视图等对象，表下面的字段，主键，触发器等；sql文件主要为对象的创建语句；tsv.zst和tsv.zst.idx主要为表数据以及索引

在通过util.loadDump()加载转储数据时，可以选择导入或排除单个表或schema，默认情况下，用户及其权限不导入，可以手动选择导入。

加载支持暂停和恢复，加载进度存放在持久化文件中，记录了成功完成的步骤和中断或失败的步骤，加载程序在恢复或重试导入时引用进度状态文件，并跳过已完成的步骤，对于已加载的表，将自动消除重复数据。第一次按ctrl+C，不会启动新任务，但当前任务依旧在执行，再按Ctrl+C再次停止现有任务，出现错误信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


[root@t-luhx01-v-szzb sysbench.dump]# cat load-progress.d1f7823e-e767-11ea-9fbb-005056ab71f1.json
{"op":"SCHEMA-DDL","done":false,"schema":"sysbench"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest7"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest7"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest8"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest8"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest6"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest6"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest9"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest9"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest5"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest5"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest1"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest1"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest2"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest2"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest10"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest10"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest3"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest3"}
{"op":"TABLE-DDL","done":false,"schema":"sysbench","table":"sbtest4"}
{"op":"TABLE-DDL","done":true,"schema":"sysbench","table":"sbtest4"}

转储的DDL由单个线程执行，数据则按指定线程数并行加载，如果拆分了chunk则可以使用多个线程处理表，否则每个线程一次只能加载一张表。为了进一步加快加载速度，我们可以设置延迟创建索引，MySQL8.0还支持disable redo_log(非生产环境)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79


 MySQL  localhost  JS > util.loadDump("/service/sysbench.dump",{threads : 88});
Loading DDL and Data from '/service/sysbench.dump' using 88 threads.
Target is MySQL 5.7.17-log. Dump was produced from MySQL 5.7.17-log
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `sysbench`
Executing DDL script for `sysbench`.`sbtest7`
Executing DDL script for `sysbench`.`sbtest8`
Executing DDL script for `sysbench`.`sbtest6`
Executing DDL script for `sysbench`.`sbtest9`
Executing DDL script for `sysbench`.`sbtest5`
Executing DDL script for `sysbench`.`sbtest1`
Executing DDL script for `sysbench`.`sbtest2`
Executing DDL script for `sysbench`.`sbtest10`
Executing DDL script for `sysbench`.`sbtest3`
Executing DDL script for `sysbench`.`sbtest4`
[Worker036] sysbench@sbtest3@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] sysbench@sbtest4@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker011] sysbench@sbtest10@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker005] sysbench@sbtest2@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker026] sysbench@sbtest8@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker065] sysbench@sbtest9@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker013] sysbench@sbtest1@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker021] sysbench@sbtest5@1.tsv.zst: Records: 166666  Deleted: 0  Skipped: 0  Warnings: 0
[Worker032] sysbench@sbtest7@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker033] sysbench@sbtest6@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker052] sysbench@sbtest10@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker056] sysbench@sbtest8@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker017] sysbench@sbtest2@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker018] sysbench@sbtest6@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker047] sysbench@sbtest3@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker072] sysbench@sbtest4@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker010] sysbench@sbtest9@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker008] sysbench@sbtest1@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker051] sysbench@sbtest5@4.tsv.zst: Records: 166666  Deleted: 0  Skipped: 0  Warnings: 0
[Worker015] sysbench@sbtest8@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker006] sysbench@sbtest7@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker050] sysbench@sbtest2@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker071] sysbench@sbtest3@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] sysbench@sbtest10@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker063] sysbench@sbtest4@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker007] sysbench@sbtest6@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker041] sysbench@sbtest1@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker028] sysbench@sbtest9@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker069] sysbench@sbtest8@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker042] sysbench@sbtest7@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker037] sysbench@sbtest2@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] sysbench@sbtest5@0.tsv.zst: Records: 166666  Deleted: 0  Skipped: 0  Warnings: 0
[Worker025] sysbench@sbtest4@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker066] sysbench@sbtest10@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker039] sysbench@sbtest9@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker009] sysbench@sbtest3@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker023] sysbench@sbtest1@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker024] sysbench@sbtest6@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker054] sysbench@sbtest7@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker004] sysbench@sbtest8@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker027] sysbench@sbtest2@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker060] sysbench@sbtest5@@5.tsv.zst: Records: 166670  Deleted: 0  Skipped: 0  Warnings: 0
[Worker019] sysbench@sbtest3@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker049] sysbench@sbtest1@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker034] sysbench@sbtest4@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker053] sysbench@sbtest6@5.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] sysbench@sbtest9@0.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker020] sysbench@sbtest10@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker062] sysbench@sbtest7@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker035] sysbench@sbtest8@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker045] sysbench@sbtest2@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker016] sysbench@sbtest3@1.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker038] sysbench@sbtest5@3.tsv.zst: Records: 166666  Deleted: 0  Skipped: 0  Warnings: 0
[Worker048] sysbench@sbtest4@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker067] sysbench@sbtest1@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker030] sysbench@sbtest9@3.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker043] sysbench@sbtest10@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker058] sysbench@sbtest6@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
[Worker040] sysbench@sbtest8@4.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker022] sysbench@sbtest7@2.tsv.zst: Records: 142857  Deleted: 0  Skipped: 0  Warnings: 0
[Worker068] sysbench@sbtest2@@6.tsv.zst: Records: 142858  Deleted: 0  Skipped: 0  Warnings: 0
                                                    
61 chunks (8.83M rows, 1.94 GB) for 10 tables in 1 schemas were loaded in 2 min 5 sec (avg throughput 15.51 MB/s)

选项	可选值	描述
progressFile	[string]	转储加载的进度状态文件的本地文件位置，它保留导入的进度状态
resetProgress	[true/false]	设置此选项以重置进度状态，然后从头开始再次启动导入
waitDumpTimeout	[int]	通过指定一个超时（以秒为单位）来激活并发加载，该超时在转储位置中的所有上载数据块都已处理后，加载将等待进一步的数据，这允许实用程序在转储仍在创建过程中导入转储
ignoreExistingObjects	[true/false]	忽略已经存在的对象，仅报告重复对象，不产生错误
ignoreVersion	[true/false]	忽略转储和加载的MySQL版本不一致的情况
skipBinlog	[true/false]	跳过目标 MySQL 实例上在导入过程中使用的会话的二进制日志记录
loadIndexes	[true/false]	创建二级索引，当需要在加载 DDL 文件后对表结构进行更改可以设置该参数，后续再通过再次运行转储加载来创建索引
deferTableIndexes	[true/false]	将辅助索引的创建推迟到加载表数据之后
analyzeTables	[true/false]	加载表后分析表
characterSet	[string]	用于导入目标 MySQL 实例的字符集
excludeSchemas	[array]	从导入中排除指定的schema
includeSchemas	[array]	仅从转储文件加载指定schema
excludeTables	[array]	从导入中排除指定表
includeTables	[array]	仅从转储文件加载指定表
loadDdl	[true/false]	仅从转储导入 DDL 文件
loadData	[true/false]	仅从转储导入数据文件
loadUsers	[true/false]	导入用于及权限

加载不会在目标MySQL应用源MySQL的gtid_executed中的GTID集，在加载之后，使用下列MySQL Shell命令从转储文件元数据复制GTID集：\sql SET @@GLOBAL.gtid_purged= "+gtidExecuted_set"; MySQL5.7需要忽略加号并且gtid_executed GTID设置必须为空

性能比较

DUMP DUMP

LOAD LOAD

上述测试结果来源于Benchmark Results，其中MySQL Shell的表现非常好，在大多数场景下都取得了不错的成绩

更多内容可参考以下链接: 1、MySQL Shell Dump & Load 2、Instance Dump Utility and Schema Dump Utility