开发同事有个需求，要将生产环境的51张表迁移至测试环境，数据量约22GB左右。正好最近在研究mysql shell客户端工具，就用它试着完成此次迁移工作，以下是本次迁移的详细过程。
登录源库进行数据导出
./mysqlsh -uroot -p"xxx" -S /data/mysql/data/mysql3306.sock
util.dumpTables('order', ['settlement_main','settlement_account_info','settlement_account_bank'], '/tmp/order' ,{'compression': 'gzip'})
这里只是写了3张表，实际中共51张表，按照列表格式填写即可。导出的是一个目录，名称为：order。里面都是压缩过的文件。将目录传输到目标服务器，由于目标服务器所在的测试网段属于跨网段，因此打包一下，利用ngnix方式上传下载。
tar vcf order.tar order/
scp order.tar zc@10.10.10.12:/data/nginx-2.3.2/html
输入密码后开始传输。
登录目标数据库进行导入
先进行下载，然后导入：
wget test.fupin832.com/order.tar
tar vxf order.tar
./mysqlsh -uroot -p"xxx" -S /data/3306/mysql/mysql3306.sock --sql
sql> set global local_infile=on;
sql> drop table settlement_main;(所有表都要提前drop掉)
sql> \js
js> util.loadDump("/tmp/order", {dryRun: false,threads:8})
loaddump函数会自动解压文件并导入，无需指定解压参数。
结果展示
Loading DDL and Data from '/tmp/order' using 8 threads.
Opening dump...
Target is MySQL 8.0.30. Dump was produced from MySQL 8.0.33
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
1 thds loading | 100% (22.69 GB / 22.69 GB), 2.06 MB/s, 51 / 51 tables done
Executing common postamble SQL
Recreating indexes - done
376 chunks (45.60M rows, 22.69 GB) for 51 tables in 1 schemas were loaded in 1 hour 41 min 32 sec (avg throughput 3.73 MB/s)
0 warnings were reported during the load.
服务器配置：cpu 40c，内存126GB，ssd磁盘物理机
用时共计1小时41分钟32秒，效果明显好于mysqldump导入。
Mysql-shell-8.2.1直接导入
如果源库与目标库网络可以联通，那么mysql shell 8.2版本的新功能util.copyTables()可以直接完成上述步骤，其实就是将util.dumptables 和 util.loaddump进行了结合。相似的，新工具还有util.copyInstance() 和 util.copySchemas()用来完成整个实例或者单库的直接导入。下面为传输过程：
登录目标数据库修改参数：
SET GLOBAL local_infile = 1;
不然报错：
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
登录源库执行下面命令，将zy数据库中的t，t1两张表导入目标数据库：
util.copyTables('zy', ['t','t1'], 'zy@127.0.01:3308',{dryRun:false,threads:8})
结果展示：
Copying DDL and Data from in-memory FS, source: node1:3307, target: node1:3308.
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 8 threads.
NOTE: SRC: Progress information uses estimated values and may not be accurate.
NOTE: SRC: Table statistics not available forzy.t, chunking operation may be not optimal. Please consider running 'ANALYZE TABLEzy.t;' first.
TGT: Opening dump...
NOTE: TGT: Dump is still ongoing, data will be loaded as it becomes available.
TGT: Target is MySQL 8.0.30. Dump was produced from MySQL 8.0.30
TGT: Scanning metadata...
TGT: Scanning metadata - done
TGT: Checking for pre-existing objects...
TGT: Executing common preamble SQL
TGT: Executing DDL...
TGT: Executing DDL - done
TGT: Executing view DDL...
TGT: Executing view DDL - done
TGT: Loading data...
TGT: Starting data load
TGT: Recreating indexes...
TGT: Waiting for more data to become available...
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
SRC: Starting data dump
150% (3 rows / ~2 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: 15 bytes
SRC: Rows written: 3
SRC: Bytes written: 15 bytes
SRC: Average throughput: 15.00 B/s
TGT: Executing common postamble SQL
100% (15 bytes / 15 bytes), 2.00 B/s, 2 / 2 tables done
Recreating indexes - done
TGT: 2 chunks (3 rows, 15 bytes) for 2 tables in 1 schemas were loaded in 5 sec (avg throughput 2.99 B/s)
TGT: 0 warnings were reported during the load.---
Dump_metadata:
Binlog_file: mysql_bin.000004
Binlog_position: 2209
Executed_GTID_set: 31ee1f17-9b2c-11ee-9850-005056ad5819:1-13
该工具还支持使用where条件进行过滤，“where: {"schemaName.tableName": "string"}”符合条件的数据复制到目标服务器。
