使用MySQL Shell 8.4.1-LTS 直接将数据复制到 MySQL实例

在之前的文章中，我谈到了如何使用 MySQL Shell 通过多线程过程来转储和加载数据，以及如何以不同格式导出表数据，然后可以将这些数据导入到新的 MySQL 实例中。这篇文章将讨论我们如何直接将数据复制到另一个 MySQL 实例，而无需执行单独的转储和加载操作。

在开始这个演示之前，我按照本文中概述的流程创建了二个新的实例（172.16.1.223:3306 172.16.1.224:3306）。在创建每个实例后，我连接到每个实例，并运行了以下SQL命令：

set global local_infile = 'ON';

如果我们不将local_infile设置为ON，我们无法移动我们的数据。

安装mysql-shell 8.4.1 LTS版本,注意：8.0.x版本的shell没有这几个功能

在node223上安装：
[root@node223 ~]# yum localinstall mysql-shell-8.4.1-1.el7.x86_64.rpm

复制表数据
如果我们只需要复制几张表，我们将使用util.copyTables()方法。该方法接受四个参数。
要从中复制表的模式的名称。
我们希望复制的模式中的表列表。
新MySQL实例的连接信息。
一个选项JSON对象。
第四个参数是可选的，在本文中我们不会讨论任何选项。有关可用选项的更多信息，请查阅文档。

将test库下的t1,t2表复制到另一个实例(172.16.1.224)对应的test库中
在node223主机上通过mysqlsh登录本机的实例中
对于本示例，我正在使用以下命令：
[root@node223 ~]# mysqlsh test@localhost:3306
util.copyTables('test',['t1','t2'],'test@172.16.1.224:3306')

这个命令指定我们正在将test模式中的t1,t2表复制到172.16.1.224实例。如果我们想要复制多张表，我们将在数组中添加更多表名，这是第二个参数,注意目标库不能有同名的表：

当我运行这个命令时，在控制台中我会看到以下输出：

MySQL localhost:3306 ssl JS > util.copyTables('test',['t1','t2'],'test@172.16.1.224:3306')
Copying DDL and Data from in-memory FS, source: node223:3306, target: node224:3306.
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...
NOTE: TGT: Dump is still ongoing, data will be loaded as it becomes available.
TGT: Target is MySQL 8.0.33. Dump was produced from MySQL 8.0.33
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
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
SRC: Starting data dump
100% (5 rows / ~5 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: 10 bytes
SRC: Rows written: 5
SRC: Bytes written: 10 bytes
SRC: Average throughput: 10.00 B/s
TGT: Executing common postamble SQL
100% (10 bytes / 10 bytes), 0.00 B/s (0.00 rows/s), 2 / 2 tables done
Recreating indexes - done
TGT: 4 chunks (5 rows, 10 bytes) for 2 tables in 1 schemas were loaded in 0 sec (avg throughput 10.00 B/s, 5.00 rows/s)
TGT: 3 DDL files were executed in 0 sec.
TGT: Data load duration: 0 sec
TGT: Total duration: 0 sec
TGT: 0 warnings were reported during the load.

---
Dump_metadata:
Binlog_file: binlog.000004
Binlog_position: 2159
Executed_GTID_set: ''

表复制完成后，我们可以验证新表是否存在于新实例中。使用MySQL Shell，我连接到node224(172.16.1.224)目标实例，然后运行SQL命令：
Use test;
Show tables;

这个查询的结果是：
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
6 rows in set (0.01 sec)

这个输出看起来很好，因为test模式现在存在于新实例中。要检查表是否已复制，我们运行以下命令：
show tables from test;

这个查询的结果是：
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
6 rows in set (0.00 sec)

我们可以看到表t1,t2表现在存在于新实例中。

复制模式
如果我们想要复制一个或多个模式，我们可以使用 util.copySchemas() 方法。该方法接受三个参数。

我们希望复制的模式列表。
新 MySQL 实例的连接信息。
一个选项 JSON 对象。
选项参数与 util.copyTables() 一样是可选的。
源库创建库表如下：
mysql> create database prod_test1;
Query OK, 1 row affected (0.01 sec)
mysql> create database prod_test2;
Query OK, 1 row affected (0.01 sec)
mysql> use prod_test1;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> use prod_test2;
Database changed
mysql> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

要复制整个 prod_test1，prod_test2 两个库，我们将使用以下命令：
util.copySchemas(['prod_test1','prod_test2'], 'test@172.16.1.224:3306')

请注意，这是一个不同的实例，源实例为node223,目标实例为node224, 如果我们想要复制多个模式，我们将在第一个参数的数组中添加项目。

运行此命令时的控制台输出如下：
MySQL localhost:3306 ssl JS > util.copySchemas(['prod_test1','prod_test2'], 'test@172.16.1.224:3306')
Copying DDL and Data from in-memory FS, source: node223:3306, target: node224:3306.
SRC: Acquiring global read lock
SRC: Global read lock acquired
Initializing - done
SRC: 2 schemas will be dumped and within them 2 tables, 0 views.
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...
NOTE: TGT: Dump is still ongoing, data will be loaded as it becomes available.
TGT: Target is MySQL 8.0.33. Dump was produced from MySQL 8.0.33
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: Executing common postamble SQL
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
SRC: Starting data dump
100% (4 rows / ~4 rows), 0.00 rows/s, 0.00 B/s
SRC: Dump duration: 00:00:00s
SRC: Total duration: 00:00:00s
SRC: Schemas dumped: 2
SRC: Tables dumped: 2
SRC: Data size: 8 bytes
SRC: Rows written: 4
SRC: Bytes written: 8 bytes
SRC: Average throughput: 8.00 B/s
100% (8 bytes / 8 bytes), 0.00 B/s (0.00 rows/s), 2 / 2 tables done
Recreating indexes - done
TGT: 4 chunks (4 rows, 8 bytes) for 2 tables in 2 schemas were loaded in 0 sec (avg throughput 8.00 B/s, 4.00 rows/s)
TGT: 4 DDL files were executed in 0 sec.
TGT: Data load duration: 0 sec
TGT: Total duration: 0 sec
TGT: 0 warnings were reported during the load.

---
Dump_metadata:
Binlog_file: binlog.000004
Binlog_position: 3545
Executed_GTID_set: ''

我们可以通过连接到node224目标实例并运行以下查询来检查：
mysql> show databases;

我们看到 prod_test1,prod_test2 模式已被创建。现在让我们通过运行以下查询来查看新模式中存在哪些表：
mysql> show tables from prod_test1;
mysql> show tables from prod_test2;

这个查询的结果是：
mysql> show tables from prod_test1;
+----------------------+
| Tables_in_prod_test1 |
+----------------------+
| t1 |
+----------------------+
1 row in set (0.00 sec)

mysql> show tables from prod_test2;
+----------------------+
| Tables_in_prod_test2 |
+----------------------+
| t2 |
+----------------------+
1 row in set (0.00 sec)

我们可以看到 prod_test1,prod_test2模式中的所有表都已复制到新的 MySQL 实例中。

复制一个完整的实例
我们使用 util.copyInstance() 方法将整个 MySQL 实例复制到一个新实例中。该方法接受两个参数。

1. 新 MySQL 实例的连接信息。
2. 一个选项 JSON 对象。
选项参数是可选的，就像上面的其他示例一样。

当我们复制整个实例时，除了系统模式（如 information_schema、mysql、performance_schema 和 sys）之外，所有模式都会被复制。

要将我的整个本地实例(node223)复制到node224实例，我将使用以下命令：
util.copyInstance('test@172.16.1.224:3306',{includeSchemas:["prod_test1","prod_test2","test"],excludeUsers:["test","root"]})
这个命令的控制台输出类似于：
MySQL localhost:3306 ssl JS > util.copyInstance('test@172.16.1.224:3306',{includeSchemas:["prod_test1","prod_test2","test"],excludeUsers:["test","root"]})
Copying DDL, Data and Users from in-memory FS, source: node223:3306, target: node224:3306.
SRC: Acquiring global read lock
SRC: Global read lock acquired
Initializing - done
SRC: 3 out of 7 schemas will be dumped and within them 4 tables, 0 views.
SRC: 0 out of 5 users 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: Writing users DDL
SRC: Running data dump using 4 threads.
NOTE: SRC: Progress information uses estimated values and may not be accurate.
TGT: Opening dump...
NOTE: TGT: Dump is still ongoing, data will be loaded as it becomes available.
TGT: Target is MySQL 8.0.33. Dump was produced from MySQL 8.0.33
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 user accounts SQL...
TGT: Executing view DDL...
TGT: Executing view DDL - done
TGT: Loading data...
TGT: Starting data load
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
SRC: Starting data dump
100% (9 rows / ~9 rows), 0.00 rows/s, 0.00 B/s
SRC: Dump duration: 00:00:00s
SRC: Total duration: 00:00:00s
SRC: Schemas dumped: 3
SRC: Tables dumped: 4
SRC: Data size: 18 bytes
SRC: Rows written: 9
SRC: Bytes written: 18 bytes
SRC: Average throughput: 18.00 B/s
TGT: Executing common postamble SQL
100% (18 bytes / 18 bytes), 0.00 B/s (0.00 rows/s), 4 / 4 tables done
Recreating indexes - done
TGT: 8 chunks (9 rows, 18 bytes) for 4 tables in 3 schemas were loaded in 0 sec (avg throughput 18.00 B/s, 9.00 rows/s)
TGT: 7 DDL files were executed in 0 sec.
TGT: 0 accounts were loaded
TGT: Data load duration: 0 sec
TGT: Total duration: 0 sec
TGT: 0 warnings were reported during the load.

---
Dump_metadata:
Binlog_file: binlog.000004
Binlog_position: 3545
Executed_GTID_set: ''

正如我们所看到的，所有非系统模式都已复制到新实例中。
总结：
MySQL Shell提供了多种将数据从一个实例复制或移动到另一个实例的方法。其中一些方法需要两个步骤——一个用于转储或导出数据，另一个用于加载或导入数据。通过在util对象中使用复制方法，我们可以在单个命令/步骤中将数据从一个MySQL实例复制到另一个实例。要获取有关我们讨论的命令的更多信息，请查阅文档。

文章看完了，如果觉得本文对您的工作或生活有用，希望分享给你身边的朋友，一起学习，共同进步哈~~~

欢迎关注我的公众号【数库信息技术】，你的关注是我写作的动力源泉

各大平台都可以找到我：
————————————————————————————
公众号：数库信息技术
墨天轮：https://www.modb.pro/u/427810
百家号：https://author.baidu.com/home/1780697309880431
CSDN ：https://blog.csdn.net/rscpass
51CTO: https://blog.51cto.com/u_16068254
博客园：https://www.cnblogs.com/shukuinfo
知乎:https://www.zhihu.com/people/shukuinfo
————————————————————————————

posted on 2024-08-29 17:19 数据库帮帮团阅读(13) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

shukuinfo

使用MySQL Shell 8.4.1-LTS 直接将数据复制到 MySQL实例

导航

公告