What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard Data Warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

Show all

Help Center/ GaussDB(DWS)/ Best Practices/ Data Migration/ Using CDM to Migrate MySQL Data to a GaussDB(DWS) Cluster

Using CDM to Migrate MySQL Data to a GaussDB(DWS) Cluster

Updated on 2024-10-29 GMT+08:00

View PDF

This section describes how to use Cloud Data Migration (CDM) to migrate MySQL data to GaussDB(DWS) clusters in batches.

This section contains the following parts:

Checking Data Before Migration
Creating a GaussDB(DWS) Cluster
Creating a CDM cluster
Creating a Connection
Creating and Migrating a Job
Verifying Data Consistency After Migration

Scenario Description

Figure 1 Migration

CDM can migrate an entire cloud/on-premises MySQL database or a single table. The migration of an on-premises MySQL database is used as an example.

On-premises MySQL data migration:
CDM accesses the MySQL database through the public IP address. CDM and GaussDB(DWS) are in the same VPC. CDM establishes JDBC connections respectively with MySQL and GaussDB(DWS).
Cloud RDS MySQL data migration:
RDS, CDM, and GaussDB(DWS) are in the same VPC. CDM establishes JDBC connections respectively with MySQL and GaussDB(DWS). If cloud RDS and GaussDB(DWS) are not in the same VPC, CDM uses the EIP to access RDS.

Checking Data Before Migration

Connect to the MySQL DB instance and check the MySQL database status.

mysql -h <host>-P<port>-u <userName>-p--ssl-ca=<caDIR>

**Table 1** Parameter description
Parameter	Description
<host>	Address for connecting to the MySQL database.
<port>	Database port. By default, the value is 3306.
<userName>	MySQL administrator account. The default value is root.
<caDIR>	Path of the CA certificate. The file must be stored in the path where the command is executed.

Enter the password of the database account as prompted:

      
         Enter password:

Analyze the name and code of the databases to be migrated, and the name and attributes of the tables to be migrated.

For example, the destination MySQL databases to be migrated are test01, test02, and the encoding format. The test01 library contains the orders, persons, and persons_b tables and the persons_beijing view. The test02 library contains the persons_c table.
1. Query the database name.
  1
  
  show databases;
2. Query the database code.
  1 2
  
  use <databasename>; status;
  Figure 2 Query database code 1
  
  Figure 3 Query database code 2
3. Query database tables.
  1 2
  
  use <databasename>; show full tables;
  NOTICE:
  - The GaussDB(DWS) database is case-insensitive. If the original MySQL database contains table names that contain both uppercase and lowercase letters or only uppercase letters, for example, Table01 and TABLE01, you need to change the table names to lowercase letters before the migration. Otherwise, GaussDB(DWS) cannot identify the tables after migration.
  - You are advised to set the MySQL database to be case-insensitive by modifying lower_case_table_names to 1 in /etc/my.cnf and restarting the MySQL service.
  Figure 4 Querying database tables
  
  Figure 5 Querying database tables
4. Check the attributes of each table for comparison after the migration.
  1 2
  
  use <databasename>; desc <table name>;
  Figure 6 Viewing table properties

Creating a GaussDB(DWS) Cluster

For how to create a cluster, see Creating a Cluster. You can select the CN-Hong Kong region

NOTE:

Ensure that the GaussDB(DWS) cluster and CDM cluster are in the same region and VPC.
Connect to a cluster by referring to Using the gsql CLI Client to Connect to a Cluster.

Create the target databases test01 and test02 in Checking Data Before Migration with the same name and database code as the original MySQL database.

      
         create database test01 with encoding 'UTF-8' dbcompatibility 'mysql' template template0; 
create database test02 with encoding 'UTF-8' dbcompatibility 'mysql' template template0;

Creating a CDM cluster

Log in to the Huawei Cloud console.
Choose Migration > Cloud Data Migration.

Click Buy CDM Cluster and set the following parameters:

**Table 2** CDM cluster parameters
Parameter	Value
Region	Select the CN-Hong Kong region, which is in the same location as GaussDB (DWS).
AZ	AZ1 (If the desired resources are sold out in the current AZ, change the AZ and try again.)
Name	CDM-demo
Instance Type	cdm.large (Select other flavors if the flavor is sold out.)
VPC	demo-vpc, which is in the same location as GaussDB (DWS).
Subnet	subnet-f377(10.1.0.0/24) (example)
Security Group	-
Enterprise Project	default

Click Buy Now, confirm all the parameters, and click Submit.
Go back to the Cluster Management page. Cluster creation takes about 5 minutes. After the cluster is created, click Bind EIP in the Operation column of the cluster.
Select an available EIP and click OK. If no EIP is available, switch to the EIP page to purchase an EIP.

Creating a Connection

When creating a MySQL connection for the first time, upload a driver.
1. Access the MySQL driver and download the 5.1.48 version.
  Figure 7 Downloading a driver
2. Download the package to the local host and decompress it to obtain mysql-connector-java-xxx.jar.
3. On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Links > Driver Management.
4. Click Upload on the right of MySQL, select mysql-connector-java-xxx.jar, and click Upload.

Create a MySQL connection.

On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Links > Create Link.
Select MySQL and click Next. (If the RDS is deployed on the cloud, select RDS for MySQL.)

Enter the connection information according to Table 3, and click Test. If the test is successful, click Save.

NOTE:

If the test fails, check whether CDM connects to the MySQL database using the public IP address. If the public IP address is used, bind the public IP address by referring to 5.

**Table 3** MySQL connection information
Parameter	Value
Name	MySQL
Database Server	192.168.1.100 (This is an example, enter the actual public IP address of the on-premises MySQL database. Ensure that the whitelist access permission has been enabled on the MySQL server.)
Port	3306
Database Name	test01
User	root
Password	Password of the user root.
Use Local API	No
Use Agent	No

Create a GaussDB(DWS) link.

On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Links > Create Link.
Select Data Warehouse Service and click Next.

Enter the connection information according to Table 4, and click Test. If the test is successful, click Save.

**Table 4** GaussDB(DWS) connection information
Parameter	Value
Name	DWS-test01
Database Server	Click Select and select the GaussDB(DWS) cluster to be connected from the cluster list. NOTE: The system automatically displays the GaussDB(DWS) clusters in the same region and VPC. If no GaussDB(DWS) cluster is available, manually enter the IP address of the GaussDB(DWS) cluster that has been connected to the network.
Port	8000
Database Name	test01 (Ensure that the corresponding database has been manually created on GaussDB(DWS) by referring to 3.)
Username	dbadmin
Password	Password of user dbadmin
Use Agent	No

Repeat 3.a to 3.c to create the DWS-test02 link.

Creating and Migrating a Job

On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Entire DB Migration > Create Job.
Set the following parameters and click Next.
- Job Name: MySQL-DWS-test01
- Source Job Configuration:
  - Source Link Name: MySQL
- Destination Job Configuration:
  - Destination Link Name: DWS-test01
  - Automatic Table Creation: The table is created when it does not exist.
  - isCompress: Yes
  - Orientation: COLUMN
  - Retain the default value for other settings.
Figure 8 Configuring a Job
Select all tables, click , and click Next.
Retain the default settings and click Save and Run.
Check the job running status. If the status is Succeeded, the migration is successful.

Figure 9 Viewing the job running status
Repeat 1 to 5 to migrate all tables in the test02 database.

NOTICE:

When creating a job, select test02 for the GaussDB(DWS) database of the target source.

Verifying Data Consistency After Migration

Use gsql to connect to the test01 cluster of GaussDB(DWS).

gsql -d test01 -h IP address of the host -p 8000 -U dbadmin -W Database user password -r;
Query the tables in the test01 database.
1

select * from pg_tables where schemaname= 'public';
Figure 10 Query the tables in the test01 database.
Check whether the data in each table is complete and whether the columns are complete.
1 2

select count(*) from table name; \d+ table name;
Figure 11 Querying table fields

Figure 12 Querying table data
Perform sampling check to verify table data.
1

select * from persons where city = 'Beijing' order by id_p;
Figure 13 Verifying table data
Repeat 2 to 4 to check whether the data in other databases and tables is correct.