##
MySQL如何在两个实例之间迁移表
迁移单表有非常多的方法(mysqldump，表空间传输等等)，今天使用mysql shell来完成并测试了一下速度。
1、环境准备
主机1:192.168.5.130，准备一张1千万的表
[root@mydb01 ~]# sysbench --db-driver=mysql --time=300 --threads=20 --report-interval=5 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=1 --table_size=10000000 oltp_read_write --db-ps-mode=disable prepare
主机2:192.168.5.140，创建目标库
create database db01;
2、迁移
MySQL 192.168.5.130:3306 ssl JS > util.copyTables('test_db', ['sbtest1'], 'root@192.168.5.140:3306',{schema: "db01",threads:"8",deferTableIndexes:"all"})
Copying DDL and Data from in-memory FS, source: 192.168.5.130:3306, target: 192.168.5.140:3306.
SRC: Acquiring global read lock
SRC: Global read lock acquired
Initializing - done
SRC: 1 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.
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.0.35. Dump was produced from MySQL 8.0.35
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...
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
SRC: Starting data dump
TGT: Executing view DDL - done
TGT: Loading data...
TGT: Starting data load
TGT: Recreating indexes...
103% (10.00M rows / ~9.62M rows), 65.02K rows/s, 12.10 MB/s
SRC: Dump duration: 00:02:12s
SRC: Total duration: 00:02:12s
SRC: Schemas dumped: 1
SRC: Tables dumped: 1
SRC: Data size: 1.96 GB
SRC: Rows written: 10000000
SRC: Bytes written: 1.96 GB
SRC: Average throughput: 14.77 MB/s
1 thds loading \ ?% (1.96 GB / ?), 11.66 MB/s, 1 / 1 tables done
TGT: Executing common postamble SQL
Recreating indexes - done
TGT: 35 chunks (10.00M rows, 1.96 GB) for 1 tables in 1 schemas were loaded in 2 min 47 sec (avg throughput 14.61 MB/s)
TGT: 0 warnings were reported during the load.
---
Dump_metadata:
Binlog_file: binlog.000006
Binlog_position: 835069358
Executed_GTID_set: 07ae84d6-9a51-11ee-b481-000c29d15c4d:1-106365
MySQL 192.168.5.130:3306 ssl JS >
3、总结
这里开了8个线程，并且推迟了所有的二级索引创建。不到3分钟就完成了。
实际生产环境应该更快（测试环境为3千元的洋垃圾）。
迁移的参数：源库的库和表，目标库的连接地址以及库，其他可选参数。
