转载篇 | MySQL Shell 8.2的新功能——复制实例、模式，和表

转摘一篇徐老师的文章，正文如下：

该功能事实上将MySQL之前的dump和load工具整合到了一处，省却了中间存储的过程，因此，功能上与这两款工具大致相同，关于这两款工具的详细使用方法请参照MySQL Shell转储和加载第1部分：演示！系列文章。

新功能利用了MySQL的“ LOAD DATA LOCAL INFILE”语句，因此，需要在目标服务器上开启变量：

SET GLOBAL local_infile = 1;

新功能由MySQL Shell的util提供，包含以下三个工具：

• util.copyInstance(connectionData[, options])

• util.copySchemas(schemaList, connectionData[, options])

• util.copyTables(schemaName, tablesList, connectionData[, options])

分别用于复制实例、模式，和表。

“connectionData”定义目标服务器的连接信息，可以是包含用户名和主机名称的字符串，例如，“user@localhost:3360”，也可以是URI，例如，“mysql://user@host:port?option=value,option=value”，或者是字典格式，例如，“{ "scheme": "mysql", "user": "u", "host": "h", "port": 1234, "option": "value" }”

其他的选项，根据工具的不同，选择模式列表或者表的列表等。下面以复制表功能作为演示，供读者参考各个选项的输入格式。

MySQL  localhost:3350 ssl  JS > util.copyTables('world',['city','country'],'root@localhost:3360',{dryRun:false})
Copying DDL and Data from in-memory FS, source: 127.0.0.1:3350, target: 127.0.0.1:3360.
SRC: Acquiring global read lock
SRC: Global read lock acquired
Initializing - done
SRC: 2 tables and 0 views will be dumped.
Gathering information - done
SRC: All transactions have been started
SRC: Locking instance for backup
SRC: Global read lock has been released
SRC: Writing global DDL files
SRC: Running data dump using 4 threads.
NOTE: SRC: Progress information uses estimated values and may not be accurate.
TGT: Opening dump...ata \ 0 1
NOTE: TGT: Dump is still ongoing, data will be loaded as it becomes available.
TGT: Target is MySQL 8.2.0. Dump was produced from MySQL 8.2.0
TGT: Scanning metadata...
TGT: Scanning metadata - done
TGT: Checking for pre-existing objects...
TGT: Executing common preamble SQL
TGT: Executing DDL...
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
SRC: Starting data dump
TGT: Executing DDL - done
TGT: Executing view DDL...
TGT: Executing view DDL - done
TGT: Loading data...
TGT: Starting data load
TGT: Recreating indexes...
101% (4.32K rows ~4.27K rows), 0.00 rows/s, 0.00 B/s
SRC: Dump duration: 00:00:00s
SRC: Total duration: 00:00:00s
SRC: Schemas dumped: 1
SRC: Tables dumped: 2
SRC: Data size: 176.36 KB
SRC: Rows written: 4318
SRC: Bytes written: 176.36 KB
SRC: Average throughput: 176.36 KB/s
TGT: Executing common postamble SQL
100% (176.36 KB 176.36 KB), 0.00 B/s, 2 / 2 tables done
Recreating indexes - done
TGT: 2 chunks (4.32K rows, 176.36 KB) for 2 tables in 1 schemas were loaded in 0 sec (avg throughput 176.36 KB/s)
TGT: 0 warnings were reported during the load.


---
Dump_metadata:
Binlog_file: localhost-bin.000002
Binlog_position: 773567
Executed_GTID_set: |-
3b113257-7c5e-11ee-9cfe-0800278c6a1a:1-4,
3e593c7f-7c61-11ee-adbf-0800278c6a1a:1-12,
e7ab7b31-7c61-11ee-aef4-0800278c6a1a:1-154,
e7ab929c-7c61-11ee-aef4-0800278c6a1a:1-9



与MySQL Shell的许多工具一样，该工具支持dryrun，用户可以利用空运行检查是否存在语法错误等。执行语句如下：

util.copyTables('world',['city','country'],'root@localhost:3360',{dryRun:true})

工具的选项中还包含“threads”选项，允许进行并行复制，默认值为4，实际使用的线程为设置值的2倍，原因在于复制需要一个转储线程和一个加载线程。

该工具还支持使用where条件进行过滤，“where: {"schemaName.tableName": "string"}”符合条件的数据复制到目标服务器。

此外，该工具还支持多个选项，供用户选择进行操作，详细内容请访问官网手册。“https://dev.mysql.com/doc/mysql-shell/8.2/en/mysql-shell-utils-copy.html”

以上内容是关于MySQL Shell 8.2的新功能简介。

文章至此。