You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by az...@apache.org on 2022/12/07 07:33:36 UTC
[shardingsphere] branch master updated: Add heterogeneous migration example for openGauss (#22716)
This is an automated email from the ASF dual-hosted git repository.
azexin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new d4622c36e3a Add heterogeneous migration example for openGauss (#22716)
d4622c36e3a is described below
commit d4622c36e3a36576774b4430022b40c93c27bc26
Author: Hongsheng Zhong <zh...@apache.org>
AuthorDate: Wed Dec 7 15:33:30 2022 +0800
Add heterogeneous migration example for openGauss (#22716)
* Update heterogeneous migration example for openGauss
* Rename resources to storage units
---
.../shardingsphere-proxy/migration/usage.cn.md | 63 ++++++++++++++++++----
.../shardingsphere-proxy/migration/usage.en.md | 47 +++++++++++++---
2 files changed, 92 insertions(+), 18 deletions(-)
diff --git a/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.cn.md b/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.cn.md
index c6e14988ddd..34bdedbe88d 100644
--- a/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.cn.md
+++ b/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.cn.md
@@ -80,8 +80,6 @@ GRANT CREATE, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX ON migration_ds_0.* TO
1. 在 MySQL 已准备好源端库、表、数据。
-示例：
-
```sql
DROP DATABASE IF EXISTS migration_ds_0;
CREATE DATABASE migration_ds_0 DEFAULT CHARSET utf8;
@@ -95,8 +93,6 @@ INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,
2. 在 MySQL 准备目标端库。
-示例：
-
```sql
DROP DATABASE IF EXISTS migration_ds_10;
CREATE DATABASE migration_ds_10 DEFAULT CHARSET utf8;
@@ -110,7 +106,7 @@ CREATE DATABASE migration_ds_12 DEFAULT CHARSET utf8;
#### 操作步骤
-1. 在 proxy 新建逻辑数据库并配置好资源和规则。
+1. 在 proxy 新建逻辑数据库并配置好存储单元和规则。
```sql
CREATE DATABASE sharding_db;
@@ -144,7 +140,7 @@ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
如果是迁移到异构数据库，那目前需要在 proxy 执行建表语句。
-2. 在 proxy 配置源端资源。
+2. 在 proxy 配置源端存储单元。
```sql
REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
@@ -336,7 +332,7 @@ CREATE DATABASE migration_ds_12;
#### 操作步骤
-1. 在 proxy 新建逻辑数据库并配置好资源和规则。
+1. 在 proxy 新建逻辑数据库并配置好存储单元和规则。
```sql
CREATE DATABASE sharding_db;
@@ -370,7 +366,7 @@ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
如果是迁移到异构数据库，那目前需要在 proxy 执行建表语句。
-2. 在 proxy 配置源端资源。
+2. 在 proxy 配置源端存储单元。
```sql
REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
@@ -526,7 +522,9 @@ GRANT ALL PRIVILEGES TO migration_user;
#### 前提条件
-1. 在 openGauss 已准备好源端库、表、数据。
+1. 准备好源端库、表、数据。
+
+1.1. 同构数据库。
```sql
DROP DATABASE IF EXISTS migration_ds_0;
@@ -539,6 +537,20 @@ CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHA
INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
```
+1.2. 异构数据库。
+
+MySQL 示例：
+```sql
+DROP DATABASE IF EXISTS migration_ds_0;
+CREATE DATABASE migration_ds_0 DEFAULT CHARSET utf8;
+
+USE migration_ds_0;
+
+CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
+
+INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
+```
+
2. 在 openGauss 准备目标端库。
```sql
@@ -554,13 +566,18 @@ CREATE DATABASE migration_ds_12;
#### 操作步骤
-1. 在 proxy 新建逻辑数据库并配置好资源和规则。
+1. 在 proxy 新建逻辑数据库并配置好存储单元和规则。
+
+1.1. 创建逻辑库。
```sql
CREATE DATABASE sharding_db;
\c sharding_db
+```
+1.2. 注册存储单元。
+```sql
REGISTER STORAGE UNIT ds_2 (
URL="jdbc:opengauss://127.0.0.1:5432/migration_ds_10",
USER="gaussdb",
@@ -577,7 +594,11 @@ REGISTER STORAGE UNIT ds_2 (
PASSWORD="Root@123",
PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
);
+```
+
+1.3. 创建分片规则。
+```sql
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_2,ds_3,ds_4),
SHARDING_COLUMN=order_id,
@@ -586,9 +607,17 @@ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
```
+1.4. 创建目标端表。
+
如果是迁移到异构数据库，那目前需要在 proxy 执行建表语句。
-2. 在 proxy 配置源端资源。
+```sql
+CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
+```
+
+2. 在 proxy 配置源端存储单元。
+
+2.1. 同构数据库。
```sql
REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
@@ -599,6 +628,18 @@ REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
);
```
+2.2. 异构数据库。
+
+MySQL 示例：
+```sql
+REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
+ URL="jdbc:mysql://127.0.0.1:3306/migration_ds_0?serverTimezone=UTC&useSSL=false",
+ USER="root",
+ PASSWORD="root",
+ PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
+);
+```
+
3. 启动数据迁移。
```sql
diff --git a/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.en.md b/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.en.md
index 43c3988e043..0930f95375c 100644
--- a/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.en.md
+++ b/docs/document/content/user-manual/shardingsphere-proxy/migration/usage.en.md
@@ -100,7 +100,7 @@ CREATE DATABASE migration_ds_12 DEFAULT CHARSET utf8;
#### Procedure
-1. Create a new logical database in proxy and configure resources and rules.
+1. Create a new logical database in proxy and configure storage units and rules.
```sql
CREATE DATABASE sharding_db;
@@ -134,7 +134,7 @@ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
If you are migrating to a heterogeneous database, you need to execute the table-creation statements in proxy.
-2. Configure the source resources in proxy.
+2. Configure the source storage units in proxy.
```sql
REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
@@ -326,7 +326,7 @@ CREATE DATABASE migration_ds_12;
#### Procedure
-1. Create a new logical database in proxy and configure resources and rules.
+1. Create a new logical database in proxy and configure storage units and rules.
```sql
CREATE DATABASE sharding_db;
@@ -360,7 +360,7 @@ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
If you are migrating to a heterogeneous database, you need to execute the table-creation statements in proxy.
-2. Configure the source resources in proxy.
+2. Configure the source storage units in proxy.
```sql
REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
@@ -515,7 +515,9 @@ GRANT ALL PRIVILEGES TO migration_user;
#### Requirements
-1. Prepare the source database, table, and data in openGauss.
+1. Prepare the source database, table, and data.
+
+1.1. Isomorphic database.
```sql
DROP DATABASE IF EXISTS migration_ds_0;
@@ -528,6 +530,20 @@ CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHA
INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
```
+1.2. Heterogeneous database.
+
+MySQL example:
+```sql
+DROP DATABASE IF EXISTS migration_ds_0;
+CREATE DATABASE migration_ds_0 DEFAULT CHARSET utf8;
+
+USE migration_ds_0
+
+CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
+
+INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');
+```
+
2. Prepare the target database in openGauss.
```sql
@@ -543,13 +559,19 @@ CREATE DATABASE migration_ds_12;
#### Procedure
-1. Create a new logical database and configure resources and rules.
+1. Create a new logical database and configure storage units and rules.
+
+1.1. Create logic database.
```sql
CREATE DATABASE sharding_db;
\c sharding_db
+```
+
+1.2. Register storage units.
+```sql
REGISTER STORAGE UNIT ds_2 (
URL="jdbc:opengauss://127.0.0.1:5432/migration_ds_10",
USER="gaussdb",
@@ -566,7 +588,11 @@ REGISTER STORAGE UNIT ds_2 (
PASSWORD="Root@123",
PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
);
+```
+
+1.3. Create sharding table rule.
+```sql
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_2,ds_3,ds_4),
SHARDING_COLUMN=order_id,
@@ -575,9 +601,16 @@ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
```
+1.4. Create target table.
+
If you are migrating to a heterogeneous database, you need to execute the table-creation statements in proxy.
-2. Configure the source resources in proxy.
+MySQL example:
+```sql
+CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
+```
+
+2. Configure the source storage units in proxy.
```sql
REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (