Migrate data from MySQL to OceanBase MySQL by using Huawei Cloud CDM

Huawei Cloud CDM (Cloud Data Migration) is a data migration service provided by Huawei Cloud. It supports data migration between various data sources. Huawei Cloud CDM allows you to connect to OceanBase Database by using a MySQL connector, thereby enabling data migration from MySQL to OceanBase MySQL.

Compatibility

Prerequisites

Before you use Huawei Cloud CDM, make sure that:

• You have purchased Huawei Cloud CDM. For more information, see Huawei Cloud CDM official documentation.
• You have deployed OceanBase Database and created a MySQL mode user tenant. For more information, see Create a tenant.
• The target OceanBase Database and table have been created.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment personnel 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 ODP connections, use the ODP IP address. For direct connections, use the OBServer IP address.
• $port: the connection port. For ODP connections, the default value is 2883. For direct connections, the default value is 2881.
• $database_name: the database name.

  Notice

  The user connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT permissions on the database. For more information about user permissions, see Permissions in MySQL mode.

• $user_name: the connection account. For ODP connections, the format is user@tenant#cluster or cluster:tenant:user. For direct connections, 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. Fill in the basic information of the data source:
   • Data Source Name: specify a name, such as ob_source.
   • Server Address: specify the IP address of OceanBase Database.

   Notice

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

   * **Port**: specify the port of OceanBase Database. The default value is `2881` for direct connections and `2883` for ODP connections. * **Database Name**: specify the database name. * **Username**: specify the connection account. * **Password**: specify the account password.
4. In Advanced Properties, you can adjust the following parameters based on performance requirements:
   • Number of Rows to Submit at a Time: the number of rows to submit to the destination after retrieving a specified number of rows from the source. You can adjust the default value to improve performance.
   • Number of Rows to Write at a Time: the number of rows to write to the destination at a time. You can adjust the default value to improve performance.
5. Click Test Connection, confirm that the connection is successful, and save the data source.

Step 3: Create a migration task

1. In the CDM console, click Create Job.
2. Select Table/File Migration as the task type.
3. Configure the source and destination:
   • Source Data Source: select the MySQL source data source that you have created.
   • Destination Data Source: select the OceanBase destination data source that you have created.
4. Configure the task parameters:
   • Select the database and table to migrate.
   • In Task Configuration, you can increase the number of Concurrent Threads to improve migration performance.
5. Save the task configuration.

Step 4: Execute the migration

1. In the task list, select the migration task that you have created.
2. Click Start to start the data migration.
3. Monitor the task execution status and wait for the migration to complete.

Verify the result

1. Log in to the destination OceanBase Database.

2. Execute the following SQL statement to verify whether the data migration is successful:

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

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

FAQ

Q1: What do I do if a syntax error occurs when I use the local API mode for data migration?

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

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

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

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

Solution: Try to use a different CDM cluster version, or contact Huawei Cloud Technical Support for assistance.