在之前的文章中,我谈到了如何使用 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'],'[email protected]:3306')
这个命令指定我们正在将test模式中的t1,t2表复制到172.16.1.224实例。如果我们想要复制多张表,我们将在数组中添加更多表名,这是第二个参数,注意目标库不能有同名的表:
当我运行这个命令时,在控制台中我会看到以下输出:
MySQL localhost:3306 ssl JS > util.copyTables('test',['t1','t2'],'[email protected]: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'], '[email protected]:3306')
请注意,这是一个不同的实例,源实例为node223,目标实例为node224, 如果我们想要复制多个模式,我们将在第一个参数的数组中添加项目。
运行此命令时的控制台输出如下:
MySQL localhost:3306 ssl JS > util.copySchemas(['prod_test1','prod_test2'], '[email protected]: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;
这个查询的输出类似于以下内容:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prod_test1 |
| prod_test2 |
| sys |
| test |
+--------------------+
8 rows in set (0.00 sec)
我们看到 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)之外,所有模式都会被复制。
注意:目标实例不能存在与源实例相同的库名,否则会报错
目录库只能有以下数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
要将我的整个本地实例(node223)复制到node224实例,我将使用以下命令:
util.copyInstance('[email protected]:3306',{includeSchemas:["prod_test1","prod_test2","test"],excludeUsers:["test","root"]})
这个命令的控制台输出类似于:
MySQL localhost:3306 ssl JS > util.copyInstance('[email protected]: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: ''
当实例复制完成后,我们可以通过连接到node224实例并运行以下查询来验证模式是否已复制:
mysql> show databases;
这个查询的结果是:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prod_test1 |
| prod_test2 |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
正如我们所看到的,所有非系统模式都已复制到新实例中。
总结:
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
————————————————————————————
标签:LTS,复制到,rows,TGT,DDL,实例,MySQL,prod From: https://www.cnblogs.com/shukuinfo/p/18387112