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

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

1. 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:

   1	Enter password:

2. 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;

      

      • 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

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

   Ensure that the GaussDB(DWS) cluster and CDM cluster are in the same region and VPC.

2. Connect to a cluster by referring to Using the gsql CLI Client to Connect to a Cluster.
3. Create the target databases test01 and test02 in Checking Data Before Migration with the same name and database code as the original MySQL database.

   1 2	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

1. Log in to the Huawei Cloud console.
2. Choose Migration > Cloud Data Migration.
3. 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

4. Click Buy Now, confirm all the parameters, and click Submit.
5. 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.
6. Select an available EIP and click OK. If no EIP is available, switch to the EIP page to purchase an EIP.

Creating a Connection

1. 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.

2. Create a MySQL connection.
   1. On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Links > Create Link.
   2. Select MySQL and click Next. (If the RDS is deployed on the cloud, select RDS for MySQL.)
   3. Enter the connection information according to Table 3, and click Test. If the test is successful, click Save.
      

      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

3. Create a GaussDB(DWS) link.
   1. On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Links > Create Link.
   2. Select Data Warehouse Service and click Next.
   3. 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

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

Creating and Migrating a Job

1. On the Cluster Management page, click Job Management in the Operation column of the cluster and choose Entire DB Migration > Create Job.
2. 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
   

3. Select all tables, click , and click Next.
4. Retain the default settings and click Save and Run.
5. Check the job running status. If the status is Succeeded, the migration is successful.
   Figure 9 Viewing the job running status
   

6. Repeat 1 to 5 to migrate all tables in the test02 database.
   

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

Verifying Data Consistency After Migration

1. 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;

2. Query the tables in the test01 database.

   1	select * from pg_tables where schemaname= 'public';

   Figure 10 Query the tables in the test01 database.
   

3. 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
   

4. Perform sampling check to verify table data.

   1	select * from persons where city = 'Beijing' order by id_p;

   Figure 13 Verifying table data
   

5. Repeat 2 to 4 to check whether the data in other databases and tables is correct.