Migrate data from MySQL to OceanBase Database's MySQL-compatible mode by using Huawei DRS

Huawei Data Replication Service (DRS) is a data migration and synchronization service provided by Huawei Cloud. It supports data migration and synchronization between different types of databases and provides capabilities such as full migration, incremental migration, and object migration. You can create and configure a migration task in the Huawei Cloud console to migrate data from MySQL to OceanBase Database in MySQL-compatible mode.

Version compatibility

The following table lists the version compatibility requirements between Huawei DRS and OceanBase Database.

Prerequisites

Before you use Huawei DRS, make sure that:

• 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.
• You have a Huawei Cloud account and have subscribed to the DRS service. For more information about Huawei Cloud DRS, see Huawei Cloud DRS documentation.
• If you want to perform incremental migration, the Binlog service is enabled for the MySQL-compatible tenant. For more information, see OceanBase Binlog service.

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

  Note

  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 ODP connection, the format is user@tenant#cluster or cluster:tenant:user. For 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 the DRS migration task

1. Log in to the Huawei Cloud console and go to the Data Replication Service DRS service page.
2. Choose Real-time Migration Management and click Create Migration Task. Specify the source and target database types and other required information.
3. Configure the connection information of the source database (MySQL).
4. Configure the connection information of the target database (OceanBase Database).
5. Select Migration Type (Full Migration or Full + Incremental Migration) and Migration Objects.

Step 3: Run a pre-check

1. After you configure the migration task, start a Pre-check in the DRS console.
2. DRS checks the migration environment, including Network Connectivity, Database Parameters, and Disk Space.
3. If the pre-check fails, fix the issues as prompted and run the pre-check again until it passes.

Step 4: Start the migration task

1. After the pre-check passes, start the migration task in the DRS console.
2. After the task starts, you can view the migration progress and running status in the console.
3. After the migration is complete, verify data consistency between the source and target databases to ensure that the migration result is correct.

Result verification

After the migration task is complete, you can verify the result by using the following methods:

• View the migration task status in the DRS console and confirm that the status is displayed as Successful.
• Compare the data volume in the source and target databases to ensure data consistency.
• Check whether the migrated objects, such as tables, views, stored procedures, and functions, are complete.

FAQ

Q1: What do I do if a timeout error occurs during the pre-check?

Cause: The three-part naming format, such as root@tenant#cluster, is used. Huawei DRS cannot find the corresponding user record in the mysql.user table, which causes the query to time out.

Solution: Use the one-part naming format, such as root, instead of the three-part naming format.

Q2: What do I do if view creation fails during object migration?

Cause: OceanBase Database does not support SQL syntax in the format DEFINER='root'@'%', which causes view creation to fail.

Solution: Manually modify the view definition by changing DEFINER='root'@'%' to DEFINER='root'.

Q3: What do I do if an error indicating that the default_collation_for_utf8mb4 variable is missing occurs during incremental migration?

Cause: The default_collation_for_utf8mb4 variable is not implemented in some versions of OceanBase Database. Huawei DRS reports an error when it detects that the variable is missing.

Solution: Upgrade OceanBase Database to V4.2.5.1 or later. This version supports the default_collation_for_utf8mb4 variable.