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

数据运维 2024-03-18 爱可生开源社区 手机阅读

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

该功能事实上将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的新功能简介。

        文章至此。