👩‍💻Data Migration From RDS for MySQL to DDM (Distributed Database Middleware)

Data Migration

Introduction 🔰

Hi everyone 👋😊 , in this article I will show you how to migrate data from RDS to an instance in DDM step by step. Data migration was performed using VPN Gateway. I wish you enjoyable reading ❗🤗☕🍫

Scenarios

This practice describes how to migrate data from RDS for MySQL to DDM in a different region using Data Replication Service (DRS), including how to create an RDS for MySQL instance and a DDM instance on Huawei Cloud, and how to migrate data over the VPN network.

Prerequisites

· You have a Huawei Cloud account.

· Your account balance is not below zero.

Service List

· Virtual Private Cloud (VPC)

· Virtual Private Network (VPN)

· RDS

· Distributed Database Middleware (DDM)

· Data Replication Service (DRS)

· Data Admin Service (DAS)

Deployment Architecture

In this example, the source is a Huawei Cloud RDS for MySQL instance and the destination is a DDM instance in a different region. Data is migrated from the source to the destination over a VPN.

VPN-based deployment architecture

Constraints

· Before data migration, you need to stop your workloads to ensure data integrity.

· The new RDS for MySQL instance must be of the same version as the source RDS for MySQL instance.

Notes on Usage

· The resource planning is for demonstration only. Adjust it as needed.

· The end-to-end test data is for reference only.

Create a Migration Task

1. Preparing for the Source RDS for MySQL Instance

1.1 Creating a VPC and Security Group

Create a VPC and security group to prepare for creating an RDS for MySQL

instance as the source.

Creating a VPC

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Cloud

The VPC console is displayed.

VPC Console

Step 4 Click Create VPC.

Creating a VPC

Step 5 Configure parameters as needed and click Create Now.

Step 6 Return to the VPC list and check whether the VPC is created.

When its status becomes available, the VPC is created.

1.2 Creating a Security Group

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Cloud

Step 4 In the navigation pane, choose Access Control > Security Groups.

Step 5 Click Create Security Group.

Step 6 Specify a security group name and other information.

Creating a Security Group

Step 7 Click OK.

Step 8 Return to the security group list and click the security group name (sg-DRS01 in this example).

Step 9 Click the Inbound Rules tab, and then click Add Rule.

Add Inbound Rules

Step 10 Configure an inbound rule to allow access from database port 3306.

Setting Inbound Rules

1.3 Creating an RDS for MySQL Instance

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Databases > Relational Database Service.

Step 4 Click Buy DB Instance.

Step 5 Configure the instance name and basic information and select an instance class.

Buying a DB Instance on RDS

Step 6 Select a VPC and security group and configure the database port.

The VPC and security group have been created in Creating a VPC and Security Group.

Creating a VPC and Security Group

Step 7 Set the instance password.

Set the instance password

Step 8 Click Next and complete the whole process.

Step 9 Return to the instance list.

If the instance status becomes Available, the instance is created.

1.4 Creating Test Data

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Databases > Relational Database Service.

Step 4 Locate the created RDS instance and choose More > Log In.

Step 5 In the displayed dialog box, enter the username and password of the instance and click Test Connection.

Step 6 After the connection is successful, click Log In.

Step 7 Click Create Database to create the db_test database.

Step 8 Run the following SQL statement in db_test to create table table3_:

CREATE TABLE `db_test`.`table3_` (
`Column1` INT(11) UNSIGNED NOT NULL,
`Column2` TIME NULL,
`Column3` CHAR NULL,
PRIMARY KEY (`Column1`)
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

SQL Preview

Step 9 Run the following statements in table table3_ to insert three lines of data:

INSERT INTO `db_test`.`table3_` (`Column1`,`Column2`,`Column3`) VALUES(1,'00:00:11','a');
INSERT INTO `db_test`.`table3_` (`Column1`,`Column2`,`Column3`) VALUES(2,'00:00:22','b');
INSERT INTO `db_test`.`table3_` (`Column1`,`Column2`,`Column3`) VALUES(5,'00:00:55','e');

1.5 Creating a VPN for the Source

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Network.

Step 4 In the navigation pane on the left, choose Virtual Private Network > VPN Gateways.

Step 5 On the VPN Gateways page, click Buy VPN Gateway.

Step 6 Specify a gateway name and other information

Specify a gateway name

Step 7 Specify the VPN connection information and click Submit.

Specify the VPN connection information

Step 8 After the VPN gateway is created, view its information in the VPN gateway list. Its status is Not connected. If this VPN gateway is in use by a VPC connection, the VPN gateway status changes to Normal.

VPN Gateways

1.2 Preparing for the Destination DDM Instance

1.2.1 Creating a VPC and Security Group

Create a VPC and security group to prepare for creating an RDS for MySQL

instance as the source.

Creating a VPC

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Cloud

Step 4 Click Create VPC.

Step 5 Configure parameters as needed and click Create Now.

Step 6 Return to the VPC list and check whether the VPC is created.

When its status becomes available, the VPC is created.

1.2.2 Creating a Security Group

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Cloud.

The VPC console is displayed.

Step 4 In the navigation pane, choose Access Control > Security Groups.

Step 5 Click Create Security Group.

Step 6 Specify the security group name and other information, and click OK.

Creating a Security Group

1.2.3 Creating a DDM Instance

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Distributed Database Middleware.

Step 4 On the displayed page, in the upper right corner, click Buy DDM Instance.

Step 5 Specify a node class and other information.

Buy DDM Instance

Step 6 Select a VPC and security group and configure the database port.

The VPC and security group have been created in Creating a VPC and Security Group.

Step 7 After the configuration is complete, click Next at the bottom of the page.

Step 8 Go to the Instances page to view and manage the instance.

The default database port is 5066 and cab be changed after the instance is created. If the status of the instance is Running, the instance has been created.

1.2.4 Creating an RDS for MySQL Instance

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Relational Database Service.

Step 4 Click Buy DB Instance.

Step 5 Configure the instance name and basic information.

Step 6 Select an instance class.

Step 7 Select a VPC and security group and configure the database port. The VPC and security group have been created in Creating a VPC and Security Group.

🙌 CAUTION :

The RDS for MySQL instance must be in the same VPC and subnet as the DDM instance.

Same VPC and subnet

Step 8 Set the instance password.

Step 9 Click Next and complete the whole process.

Step 10 Return to the instance list.

If the instance status is Available, the instance is created.

1.2.5 Creating a Schema and Associating It with the RDS for MySQL Instance

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Distributed Database Middleware

Step 4 On the Instances page, locate the required DDM instance and click Create Schema in the Operation column.

Step 5 On the displayed page, specify a sharding method, enter a schema name, set the number of shards, select the required DDM accounts, and click Next.

In this example, the schema is unsharded, and the schema name is db_test.

🚫 CAUTION :

Currently, only data can be migrated from the RDS for MySQL to DDM. To migrate table structures and other objects, you need to create schemas in the destination DDM instance based on table structures of the source RDS for MySQL instance.

Migration table structures and other objects

1.2.6 Creating a DDM Account

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Distributed Database Middleware.

Step 4 On the Instances page, locate the required DDM instance and click its name.

Step 5 In the navigation pane, choose Accounts.

Step 6 On the displayed page, click Create Account and configure parameters as needed.

Create Account and configure parameters

For details about the permissions required by the DDM account.

Step 7 Click OK.

1.2.7 Creating Table Structures in the Destination Database

Currently, only data can be migrated from RDS for MySQL instances to DDM instances. To migrate table structures and other objects, you need to create table structures and indexes in the destination database based on table structures of the source schema. Any source objects that have no corresponding objects created in the destination cannot be migrated.

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Distributed Database Middleware.

Step 4 On the Instances page, locate the required instance and click Log In in the Operation column.

Step 5 In the displayed dialog box, enter the username and password created in Creating a DDM Account, and click Test Connection.

Step 6 After the connection is successful, click Log In to log in to the DDM instance.

Step 7 Click the db_test schema created in Creating a Schema and Associating It with the RDS for MySQL Instance.

Step 8 Run the following SQL statements in database db_test to create table table3_ with the same structure as the source:

CREATE TABLE `db_test`.`table3_` (
`Column1` INT(11) UNSIGNED NOT NULL,
`Column2` TIME NULL,
`Column3` CHAR NULL,
PRIMARY KEY (`Column1`)
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

1.2.8 Creating a VPN for the Destination

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Network.

Step 4 In the navigation pane on the left, choose Virtual Private Network > VPN Gateways.

Step 5 On the VPN Gateways page, click Buy VPN Gateway.

Step 6 Specify a gateway name and other information

Buy VPN Gateway

💥 CAUTION:

The connected VPN gateway must have the same as VPC as the destination DDM instance, that is, the VPC created in Creating a VPC and Security Group.

Step 7 Specify the VPN connection information.

VPN Connection

💢 CAUTION :

· The local subnet must be the same as the VPC subnet where the destination DDM instance is located, that is, the subnet created in Creating a VPC and Security Group.

· The remote gateway and subnet are the gateway and subnet of the source VPN. Configure parameters based on the VPN created in Creating a VPN for the Source.

1.3 Modifying the VPN Configuration for the Source

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select AP-Singapore.

Step 3 Click the service list icon on the left and choose Networking > Virtual Private Network.

Step 4 In the navigation pane on the left, choose Virtual Private Network > VPN Connections.

Step 5 On the VPN Connections page, locate the VPN connection created in Creating a VPN for the Source and click Modify in the Operation column.

Step 6 On the Modify VPN Connection page, change values of Remote Gateway and Remote Subnet.

Modify VPN Connection

💫 CAUTION :

The remote gateway and subnet are the gateway and subnet of the destination VPN. Configure parameters based on the VPN created in Creating a VPN for the Destination.

Step 7 After the configuration is complete, view the VPN gateway in the list. The VPN gateway status is Normal.

1.4 Creating a DRS Migration Task

Create a DRS migration task to migrate data from RDS for MySQL to DDM in a different region.

1.4.1 Pre-Migration Check

Before you create a migration task, check whether migration conditions are met.

This section describes how to migrate data from RDS for MySQL to DDM.

1.4.2 Creating a Migration Task

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Data Replication Service.

Step 4 Click Create Migration Task.

Step 5 Configure the required parameters.

a. Specify a migration task name.

b. Configure replication instance information and specify a destination DB instance. Select the DDM instance created in Creating a DDM Instance as the destination.

Create Migration Task

Step 6 Click Next.

It takes about 5 to 10 minutes to create a replication instance.

Step 7 Configure task information.

a. Configure the source database information and click Test Connection. If a successful test message is returned, the database is connected.

b. Configure the destination database information and click Test Connection. If a successful test message is returned, the database is connected.

Step 8 Click Next.

Step 9 On the Set Task page, select a migration type.

✔ Migrate Object: Select Tables.

Step 10 Click Next. On the Check Task page, check the migration task.

✔ If any check item fails, view the cause and rectify the fault. After the fault is rectified, click Check Again.

✔ If all check items are successful, click Next.

Step 11 Click Submit.

Return to the Online Migration Management page and check the migration task status.

It takes several minutes to complete.

If the status changes to Full migration, the migration task has been started.

✅ NOTE:

· For migration from MySQL to DDM, full migration and full+incremental migration are both supported.

· If you create a full migration task, the task automatically stops after full data is migrated to the destination.

· If you create a full+incremental migration task, a full migration is executed first. After the full migration is complete, an incremental migration starts.

· During the incremental migration, data is continuously migrated so the task will not automatically stop.

1.5 Checking Migration Results

You can use either of the following methods to check the migration results:

1. DRS compares migration objects and data and provides comparison results. For details, see Viewing Migration Results on the DRS Console.

2. Log in to the destination to check whether databases, tables, and data are migrated. Confirm the data migration status. For details, see Viewing Migration Results on the DDM Console.

1.5.1 Viewing Migration Results on the DRS Console

Step 1 Log in to the management console.

Step 2 Click in the upper left corner and select region CN-Hong Kong.

Step 3 Click the service list icon on the left and choose Databases > Data Replication Service.

Step 4 Locate the required DRS instance and click its name.

Step 5 In the navigation pane on the left, choose Migration Comparison.

Step 6 Click the Object-Level Comparison tab and check whether some objects are missing.

Click Compare. After the comparison is complete, view the comparison results.

DRS-MySQL to DDM

Step 7 Choose Data-Level Comparison and check whether the number of rows of migrated objects is consistent between the source and destination.

a. Click Create Comparison Task.

b. In the displayed dialog box, select the comparison type, time, and object.

Create Comparison Task

c. After the comparison task is complete, view the data comparison results.

d. To view the comparison details, click View Results next to the comparison task.

DRS- View Results

Conclusion 🎉💥

In this article, I showed step by step how to migrate data from Relational database service to DDM on Huawei cloud. We learned that we need to pay attention to the region and network properties while migrating data. I have shared the points that need attention with you. Don’t forget to follow me on Linkedin too 🤗🙌

References 📜📚

RDS: Relational Database Service_Huawei Cloud

DDM: Distributed Database Middleware_Huawei Cloud