Use Huawei Cloud CDM to migrate data from MySQL to OceanBase Database's MySQL-compatible mode

Huawei Cloud CDM (Cloud Data Migration) is a data migration service provided by Huawei Cloud. It supports data migration between different types of data sources. Huawei Cloud CDM can connect to OceanBase Database by using the MySQL connector to migrate data from MySQL to OceanBase Database's MySQL-compatible.

Version compatibility

Prerequisites

Before you use Huawei Cloud CDM, make sure that:

• You have purchased Huawei Cloud CDM. For more information, see Huawei Cloud CDM documentation.
• OceanBase Database is deployed and a MySQL-compatible user tenant is created. For more information about how to create a user tenant, see Create a tenant.
• The destination OceanBase database and tables have been created in advance.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment engineer to obtain the connection string. For example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the connection IP address. For an ODP connection, use the ODP address. For a direct connection, use the OBServer IP address.
• $port: the connection port. For an ODP connection, the default value is 2883. For a direct connection, the default value is 2881.
• $database_name: the database name.

  Notice

  The user used to connect to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.

• $user_name: the connection account. For an ODP connection, the format is user@tenant#cluster or cluster:tenant:user. For a direct connection, the format is user@tenant.
• $password: the account password.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Configure a data source

1. Log in to the Huawei Cloud CDM console and go to the cluster management page.
2. Click Create Data Source and select MySQL as the connector type.
3. Enter the basic information of the data source:
   • Data Source Name: Enter a custom name, such as ob_source.
   • Server Address: Enter the OceanBase connection IP address.

   Note

   When you use PrivateLink to connect to Huawei Cloud OceanBase, enter the endpoint IP address instead of the domain name.

   * **Port**: Enter the OceanBase connection port. The default value is `2881` for a direct connection or `2883` for an ODP connection. * **Database Name**: Enter the database name. * **Username**: Enter the connection account. * **Password**: Enter the account password.
4. In Advanced Properties, you can adjust the following parameters based on performance requirements:
   • Rows Submitted at a Time: The number of rows submitted to the destination after the specified number of rows are obtained from the source. You can adjust the default value to improve performance.
   • Rows Written at a Time: The number of rows written to the destination each time. You can adjust the default value to improve performance.
5. Click Test Connection. After the connection test succeeds, save the data source.

Step 3: Create a migration job

1. In the CDM console, click Create Job.
2. Select Table/File Migration as the job type.
3. Configure the source and destination:
   • Source Data Source: Select the created MySQL source data source.
   • Destination Data Source: Select the created OceanBase destination data source.
4. Configure the job parameters:
   • Select the databases and tables to be migrated.
   • In Job Configuration, you can increase the number of Concurrent Threads to improve migration performance.
5. Save the job configuration.

Step 4: Run the migration

1. In the job list, select the created migration job.
2. Click Start to start data migration.
3. Monitor the job execution status and wait until the migration is complete.

Result verification

1. Log in to the destination OceanBase database.

2. Execute the following SQL statement to verify whether the data is migrated successfully:

   SELECT COUNT(*) FROM $database_name.$table_name;
   

3. Compare the number of records in the source and destination databases to confirm data consistency.

FAQ

Q1: What do I do if a syntax error is reported during migration in local API mode?

Cause: The SQL syntax generated in local API mode is incompatible with OceanBase Database. For example, delimiter syntax such as fields terminated by X'0203' is not supported by OceanBase Database.

Solution: When you create a data source, select SQL Mode instead of local API mode.

Q2: What do I do if the data source passes the connection test but cannot be selected as the migration destination?

Cause: The issue may be caused by the CDM cluster version.

Solution: Try using a different CDM cluster version, or contact Huawei Cloud technical support for assistance.