MySQL Shell Dump Utility 备份恢复操作示例 -- cnDBA.cn

注：本文理论部分参考如下博客：
https://blog.csdn.net/m0_67261762/article/details/125046721

1 Dump Utility 理论说明

1.1 备份恢复说明

MySQL 的备份有两种方法：逻辑备份和物理备份。

使用MYSQLDUMP进行逻辑备份与恢复时，备份是以SQL语句的形式导出，在恢复时需要重放SQL语句，效率很低，因此一般在备份数据量较小时较为适用。并且在用逻辑备份同步数据后，需要做pt-table-checksum，避免数据丢失。
生产环境建议使用Xtrabackup进行物理备份，但是改方法对版本有要求，只能是同版本才可以。

在上篇我们介绍了 MySQL Shell 工具，如下：

MySQL Shell 安装说明
https://www.cndba.cn/dave/article/131486

MySQL Shell 的Dump Utility特性支持实例、Schema、数据表三个级别的MySQL数据导出功能。并且Dump & Load特性自带兼容性检查、并行导入导出、以及备份文件压缩，而且效率比MYSQLDUMP更高。

官网关于 Dump Utility 的说明如下：

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

MySQL Shell 包含如下几个工具：

checkForServerUpgrade：检测目标实例能否升级到指定版本。
dumpInstance：备份实例。
dumpSchemas：备份指定库。
dumpTables：备份指定表。
loadDump：恢复dumpInstance，dumpSchemas，dumpTables 生成的备份。
exportTable：将指定表导出到文本文件。只支持单表，效果同 SELECT INTO OUTFILE 一样。注意exportTable 生成的备份只能通过 importTable 来恢复。
importTable：将指定文本的数据导入到表中。生产环境中需要导入大文件时，建议使用这个工具。它会将单个文件进行拆分，然后多线程并行执行 LOAD DATA LOCAL INFILE 操作。不仅提升了导入速度，还规避了大事务的问题。
importJson：将 JSON 格式的数据导入到 MySQL 中，比如将 MongoDB 中通过 mongoexport 导出的数据导入到 MySQL 中。

1.2 Dump Utility 注意事项

表上存在主键或唯一索引才能进行 chunk 级别的并行备份。字段的数据类型不限。不像 mydumper，分片键只能是整数类型。
对于不能进行并行备份的表，目前会备份到一个文件中。如果该文件过大，不用担心大事务的问题，util.loadDump 在导入时会自动进行切割。
util.dumpInstance 只能保证 InnoDB 表的备份一致性。
默认不会备份 information_schema，mysql，ndbinfo，performance_schema，sys。
备份实例支持 MySQL 5.6 及以上版本，导入实例支持 MySQL 5.7 及以上版本。
备份的过程中，会将 BLOB 等非文本安全的列转换为 Base64，由此会导致转换后的数据大小超过原数据。导入时，注意 max_allowed_packet 的限制。
导入之前，需将目标实现的 local_infile 设置为 ON。
util.dumpInstance 会加备份锁，备份锁可用来阻塞备份过程中的 DDL。

1.3 util.dumpInstance 关键特性

util.dumpInstance 的关键特性如下：

多线程备份。并发线程数由 threads 决定，默认是 4。相对于 mysqldump 的单线程备份，备份效率更高。
支持单表 chunk 级别的并行备份，前提是表上存在主键或唯一索引。
默认是压缩备份。
支持备份限速。可通过 maxRate 限制单个线程的数据读取速率。

1.4 util.loadDump 关键特性

util.loadDump 的关键特性如下：

多线程恢复。并发线程数由 threads 决定，默认是 4。
支持断点续传功能。
在导入的过程中，会在备份目录生成一个进度文件，用于记录导入过程中的进度信息。
文件名由 progressFile 指定，默认是 load-progress. .progress。
导入时，如果备份目录中存在 progressFile，默认会从上次完成的地方继续执行。如果要从头开始执行，需将 resetProgress 设置为 true。
支持延迟创建二级索引。
支持边备份，边导入。
通过 LOAD DATA LOCAL INFILE 命令来导入数据。
如果单个文件过大，util.loadDump 在导入时会自动进行切割，以避免产生大事务。

1.5 util.dumpInstance 的参数解析

1.5.1 过滤相关

excludeSchemas：忽略某些库的备份，多个库之间用逗号隔开，如，
excludeSchemas: [“db1”, “db2”]
includeSchemas：指定某些库的备份。
excludeTables：忽略某些表的备份，表必须是 schema.table 的格式，多个表之间用逗号隔开，如，
excludeTables: [“sbtest.sbtest1”, “sbtest.sbtest2”]
includeTables：指定某些表的备份。
events：是否备份定时器，默认为 true。
excludeEvents：忽略某些定时器的备份。
includeEvents：指定某些定时器的备份。
routines：是否备份函数和存储过程，默认为 true。
excludeRoutines：忽略某些函数和存储过程的备份。
includeRoutines：指定某些函数和存储过程的备份。
users：是否备份账号信息，默认为 true。
excludeUsers：忽略某些账号的备份，可指定多个账号。
includeUsers：指定某些账号的备份，可指定多个账号。
triggers：是否备份触发器，默认为 true。
excludeTriggers：忽略某些触发器的备份。
includeTriggers：指定某些触发器的备份。
ddlOnly：是否只备份表结构，默认为 false。
dataOnly：是否只备份数据，默认为 false。

1.5.2 并行备份相关

chunking：是否开启 chunk 级别的并行备份功能，默认为 true。
bytesPerChunk：每个 chunk 文件的大小，默认 64M。
threads：并发线程数，默认为 4。

1.5.3 其它选项

tzUtc：是否设置 TIME_ZONE = ‘+00:00’，默认为 true。
consistent：是否开启一致性备份，默认为 true。若设置为 false，则不会加全局读锁，也不会开启事务的一致性快照。
dryRun：试运行。此时只会打印备份信息，不会执行备份操作。
maxRate：限制单个线程的数据读取速率，单位 byte，默认为 0，不限制。
showProgress：是否打印进度信息，如果是 TTY 设备（命令行终端），则为 true，反之，则为 false。
defaultCharacterSet：字符集，默认为 utf8mb4。
compression：备份文件的压缩算法，默认为 zstd。也可设置为 gzip 或 none（不压缩）。

1.6 util.loadDump 的参数解析

1.6.1 过滤相关

excludeEvents：忽略某些定时器的导入。
excludeRoutines：忽略某些函数和存储过程的导入。
excludeSchemas：忽略某些库的导入。
excludeTables：忽略某些表的导入。
excludeTriggers：忽略某些触发器的导入。
excludeUsers：忽略某些账号的导入。
includeEvents：导入指定定时器。
includeRoutines：导入指定函数和存储过程。
includeSchemas：导入指定库。
includeTables：导入指定表。
includeTriggers：导入指定触发器。
includeUsers：导入指定账号。
loadData：是否导入数据，默认为 true。
loadDdl：是否导入 DDL 语句，默认为 true。
loadUsers：是否导入账号，默认为 false。注意，即使将 loadUsers 设置为 true，也不会导入当前正在执行导入操作的用户。
ignoreExistingObjects：是否忽略已经存在的对象，默认为 off。

1.6.2 并行导入相关

backgroundThreads：获取元数据和 DDL 文件内容的线程数。备份集如果存储在本地，backgroundThreads 默认和 threads 一致。
threads：并发线程数，默认为 4。
maxBytesPerTransaction：指定单个 LOAD DATA 操作可加载的最大字节数。默认与 bytesPerChunk 一致。这个参数可用来规避大事务。

1.6.3 断点续传相关

progressFile：在导入的过程中，会在备份目录生成一个 progressFile，用于记录加载过程中的进度信息，这个进度信息可用来实现断点续传功能。默认为 load-progress. .progress。
resetProgress：如果备份目录中存在 progressFile，默认会从上次完成的地方继续执行。如果要从头开始执行，需将 resetProgress 设置为 true。该参数默认为 off。

1.6.4 二级索引相关

deferTableIndexes：是否延迟（数据加载完毕后）创建二级索引。可设置：off（不延迟），fulltext（只延迟创建全文索引，默认值），all（延迟创建所有索引）。
loadIndexes：与 deferTableIndexes 一起使用，用来决定数据加载完毕后，最后的二级索引是否创建，默认为 true。

1.6.5 其它选项

analyzeTables：表加载完毕后，是否执行 ANALYZE TABLE 操作。默认是 off（不执行），也可设置为 on 或 histogram（只对有直方图信息的表执行）。
characterSet：字符集，无需显式设置，默认会从备份集中获取。
createInvisiblePKs：是否创建隐式主键，默认从备份集中获取。这个与备份时是否指定了 create_invisible_pks 有关，若指定了则为 true，反之为 false。
dryRun：试运行。此时只会打印备份信息，不会执行备份操作。
ignoreVersion：忽略 MySQL 的版本检测。默认情况下，要求备份实例和导入实例的大版本一致。
schema：将表导入到指定 schema 中，适用于通过 util.dumpTables 创建的备份。
showMetadata：导入时是否打印一致性备份时的位置点信息。
showProgress：是否打印进度信息。
skipBinlog：是否设置 sql_log_bin=0 ，默认 false。这一点与 mysqldump、mydumper 不同，后面这两个工具默认会禁用 Binlog。
updateGtidSet：更新 GTID_PURGED。可设置：off（不更新，默认值）, replace（替代目标实例的 GTID_PURGED）, append（追加）。
waitDumpTimeout：util.loadDump 可导入当前正在备份的备份集。处理完所有文件后，如果备份还没有结束（具体来说，是备份集中没有生成 @.done.json），util.loadDump 会报错退出，可指定 waitDumpTimeout 等待一段时间，单位秒。

2 操作示例

这里只看 dumpInstance，dumpSchemas，dumpTables 和 loadDump 工具，其他工具的使用参考官方手册。

命令语法：

util.dumpInstance(outputUrl[, options])
util.dumpSchemas(schemas, outputUrl[, options])
util.dumpTables(schema, tables, outputUrl[, options])
util.loadDump(url[, options])

相关选项的说明可以查看官方手册：

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html

2.1 导出实例：util.dumpInstance

2.1.1 导出操作

[dave@www.cndba.cn:/data/backup]# mysqlsh --uri root@192.168.56.109:3308 
MySQL  192.168.56.109:3308  JS > util.dumpInstance("/data/backup",{threads :8,defaultCharacterSet: "utf8mb4",consistent: true}) 
……
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
186% (80 rows / ~43 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed                 
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 3
Tables dumped: 28
Uncompressed data size: 5.85 KB
Compressed data size: 4.17 KB
Compression ratio: 1.4
Rows written: 80
Bytes written: 4.17 KB
Average uncompressed throughput: 5.85 KB/s
Average compressed throughput: 4.17 KB/s

这里的备份目录必须为空。并发线程默认是 4，我们这里改成 8，其他的参数可以参考官方手册。

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html#mysql-shell-utilities-dump-opt-control

2.1.2 备份目录文件说明

导出后会生产很多文件：

[dave@www.cndba.cn:/data/backup]# pwd
/data/backup
[dave@www.cndba.cn:/data/backup]# ll
总用量 1844
-rw-r----- 1 root root    81 12月 20 15:32 cndba@cndba@@0.tsv.zst
-rw-r----- 1 root root     8 12月 20 15:32 cndba@cndba@@0.tsv.zst.idx
-rw-r----- 1 root root    46 12月 20 15:32 cndba@_cndba_del@a@@0.tsv.zst
-rw-r----- 1 root root     8 12月 20 15:32 cndba@_cndba_del@a@@0.tsv.zst.idx
-rw-r----- 1 root root    46 12月 20 15:32 cndba@_cndba_del@b@@0.tsv.zst
……
-rw-r----- 1 root root   987 12月 20 15:32 cndba@_cndba_del.json
-rw-r----- 1 root root  1221 12月 20 15:32 cndba@_cndba_del.sql
-rw-r----- 1 root root   734 12月 20 15:32 cndba@cndba.json
-rw-r----- 1 root root   853 12月 20 15:32 cndba@cndba.sql

2.2 导出指定数据库：util.dumpSchemas

用法同 util.dumpInstance 类似，第一个参数必须为数组，即使只需备份一个库：

MySQL 192.168.56.109:3308 JS > util.dumpSchemas([‘cndba’],’/data/backup/schema’,{threads :8})

备份多个库：

MySQL 192.168.56.109:3308 JS > util.dumpSchemas([‘cndba’,’ustc’],’/data/backup/schema’,{threads :8})

这里就不包含@.users.sql文件，用户需要单独来迁移。

2.3 导出指定的表：util.dumpTables

用法同 util.dumpInstance 类似，第二个参数必须为数组：

MySQL 192.168.56.109:3308 JS > util.dumpTables(‘cndba’,[‘cndba’,’employees’,’members’],’/data/backup/table’,{threads :8})

2.4 导入数据：util.loadDump

2.4.1 先 drop 数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| _meta              |
| cndba              |
| mysql              |
| performance_schema |
| sys                |
| ustc               |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database cndba;
Query OK, 22 rows affected (3.33 sec)

2.4.2 全实例恢复

注意loadUsers默认是 false，需要在导入后手工执行@.users.sql 文件中的用户和权限。

MySQL  192.168.56.109:3308  JS > util.loadDump("/data/backup/full",{loadUsers: true,threads: 8})
Loading DDL, Data and Users from '/data/backup/full' using 8 threads.
Opening dump...
Target is MySQL 5.7.25-log. Dump was produced from MySQL 5.7.25-log
Scanning metadata - done       
Checking for pre-existing objects...
ERROR: Account 'dave5'@'localhost' already exists
ERROR: Account 'dave1'@'%' already exists
ERROR: Account 'dave6'@'%' already exists
ERROR: Account 'repl'@'%' already exists
ERROR: Account 'admin'@'%' already exists
ERROR: Account 'cndba'@'%' already exists
ERROR: Account 'dave'@'%' already exists
ERROR: Account 'proxysql_monitor'@'%' already exists
ERROR: Account 'orche_manager'@'%' already exists
ERROR: Schema `_meta` already contains a table named `cluster_info`
ERROR: Schema `_meta` already contains a table named `instance_info`
ERROR: Schema `ustc` already contains a table named `_dave_20231017144021_del`
ERROR: Schema `ustc` already contains a table named `dave`
ERROR: Schema `ustc` already contains a table named `ustc`
ERROR: Schema `ustc` already contains a table named `usts`
ERROR: Schema `ustc` already contains a procedure named `proc_initData`
ERROR: One or more objects in the dump already exist in the destination database. You must either DROP these objects or exclude them from the load.
Util.loadDump: While 'Scanning metadata': Duplicate objects found in destination database (MYSQLSH 53021)
 MySQL  192.168.56.109:3308  JS >

我们看这里的错误，在使用全实例导入时，必须要求对象不存在。

可以通过includeSchemas来控制。我们这里指导 cndba 库：

 MySQL  192.168.56.109:3308  JS > util.loadDump("/data/backup/full",{threads: 8,includeSchemas:["cndba"]})
Loading DDL and Data from '/data/backup/full' using 8 threads.
Opening dump...
Target is MySQL 5.7.25-log. Dump was produced from MySQL 5.7.25-log
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done       
Executing common preamble SQL
Executing DDL - done         
Executing view DDL - done       
Starting data load
Executing common postamble SQL                                                        
100% (2.18 KB / 2.18 KB), 0.00 B/s, 98 / 98 tables and partitions done                
Recreating indexes - done 
189 chunks (56 rows, 2.18 KB) for 22 tables in 1 schemas were loaded in 6 sec (avg throughput 2.18 KB/s)
0 warnings were reported during the load.
 MySQL  192.168.56.109:3308  JS > /sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  192.168.56.109:3308  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| _meta              |
| cndba              |
| mysql              |
| performance_schema |
| sys                |
| ustc               |
+--------------------+
7 rows in set (0.0005 sec)

恢复成功。

2.4.3 导入指定库

语法是一样的，我们这里换个目录：

 MySQL  192.168.56.109:3308  cndba  SQL > drop database cndba;
Query OK, 22 rows affected (3.2426 sec)
 MySQL  192.168.56.109:3308  cndba  SQL > drop database ustc;
Query OK, 4 rows affected (0.0533 sec)
 MySQL  192.168.56.109:3308  cndba  SQL > /js
Switching to JavaScript mode...

 MySQL  192.168.56.109:3308  JS > util.loadDump("/data/backup/schema",{threads:8})
Loading DDL and Data from '/data/backup/schema' using 8 threads.
Opening dump...
Target is MySQL 5.7.25-log. Dump was produced from MySQL 5.7.25-log
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done         
Executing view DDL - done       
Starting data load
Executing common postamble SQL                                                         
100% (5.70 KB / 5.70 KB), 0.00 B/s, 102 / 102 tables and partitions done               
Recreating indexes - done 
194 chunks (77 rows, 5.70 KB) for 26 tables in 2 schemas were loaded in 6 sec (avg throughput 5.70 KB/s)
0 warnings were reported during the load.
 MySQL  192.168.56.109:3308  JS > 

 MySQL  192.168.56.109:3308  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| _meta              |
| cndba              |
| mysql              |
| performance_schema |
| sys                |
| ustc               |
+--------------------+
7 rows in set (0.0006 sec)
 MySQL  192.168.56.109:3308  SQL >

恢复成功。

2.4.4 导入指定表

 MySQL  192.168.56.109:3308  SQL > use cndba
Default schema set to `cndba`.
 MySQL  192.168.56.109:3308  cndba  SQL > drop table cndba;
Query OK, 0 rows affected (0.0543 sec)

 MySQL  192.168.56.109:3308  cndba  SQL > /js
Switching to JavaScript mode...
 MySQL  192.168.56.109:3308  cndba  JS > util.loadDump("/data/backup/table", { includeTables: ["cndba.cndba"],threads :8});
Loading DDL and Data from '/data/backup/table' using 4 threads.
Opening dump...
Target is MySQL 5.7.25-log. Dump was produced from MySQL 5.7.25-log
NOTE: Load progress file detected. Load will be resumed from where it was left, assuming no external updates were made.
You may enable the 'resetProgress' option to discard progress for this MySQL instance and force it to be completely reloaded.
Scanning metadata - done       
Executing common preamble SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
Executing common postamble SQL                       
100% (148 bytes / 148 bytes), 0.00 B/s, 1 / 1 tables done
Recreating indexes - done 
1 chunks (4 rows, 148 bytes) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 148.00 B/s)
0 warnings were reported during the load.
 MySQL  192.168.56.109:3308  cndba  JS > 

 MySQL  192.168.56.109:3308  cndba  JS > /sql
Switching to SQL mode... Commands end with ;
 MySQL  192.168.56.109:3308  cndba  SQL > use cndba
Default schema set to `cndba`.
Fetching global names, object names from `cndba` for auto-completion... Press ^C to stop.
 MySQL  192.168.56.109:3308  cndba  SQL > select count(1) from cndba;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.0005 sec)
 MySQL  192.168.56.109:3308  cndba  SQL >

恢复成功。

签到成功

CNDBA社区