Use CloudCanal to migrate data from OceanBase Database to a MySQL database

CloudCanal is a data migration and synchronization tool that helps enterprises quickly build a high-quality data channel. CloudCanal supports two modes: SaaS and private. The core members of the development team are database veterans from major database companies. They built database kernels, large distributed databases, and cloud products, and they know well about databases, distributed systems, and the business and service modes of cloud products.

This topic describes how to use CloudCanal Community Edition V2.2.6.9 to migrate data from the MySQL mode of OceanBase Database to a MySQL database.

Prerequisites

1. You have installed and deployed CloudCanal Community Edition. For more information, see Install and deploy CloudCanal on Linux and macOS.

2. OceanBase Binlog Service has been installed and deployed. For more information, see Create a binlog cluster.

Procedure

1. Add a data source.
2. Create a task.
3. View the task status.

Step 1: Add a data source

1. Log in to CloudCanal.

2. Go to the data source management page and click Add Data Source.

3. On the Add Data Source page, specify the information about the data source.

   • Deployment Type: Select Self-managed Database or Alibaba Cloud.

     • Alibaba Cloud: the database instance purchased on Alibaba Cloud.
     • Self-managed Database: the database instance that you deployed on your own.

   • Database Type: the data source type.

     Add OceanBase and MySQL as the source and destination databases for synchronization.

     • Select Self-managed Database for Deployment Type and OceanBase for Database Type to add the OceanBase database instance that you deployed.

       Settings of the OceanBase data source are as follows:

       • Network Address: the IP address for connecting to the OceanBase database directly or by using OceanBase Database Proxy (ODP).
       • oblogproxy host: the IP address of OceanBase Binlog Service. This parameter is required when an OceanBase database serves as the source database for incremental synchronization. It is optional when an OceanBase database serves as the destination database. For more information about OceanBase Binlog Service, see OceanBase Binlog Service.
       • OceanBaseRpcPort: the RPC port of the OceanBase database. The default value is 2882.
       • Authentication Method: the authentication method. Valid values include Account and Password, Account without Password, and No Account and Password. The default value is Account and Password.
       • Account: the username for logging on to the OceanBase database. If you directly connect to the OceanBase database, the username is in the Username@Tenant name format. If you connect to the OceanBase database using ODP, the username is in the Username@Tenant name#Cluster name format.
       • Password: the password corresponding to the username for logging on to the OceanBase database.
       • Description: the description of the database. Specify a name that is easy to remember and identify, such as transaction database, user database, or test database. This field is optional.

     • Select Self-managed Database for Deployment Type and MySQL for Database Type to add the MySQL database instance that you deployed.

       Settings of the MySQL data source are as follows:

       • Network Address: the IP address of the MySQL database.
       • Authentication Method: the authentication method. Valid values include Account and Password, Account without Password, and No Account and Password. By default, Account and Password is selected.
       • Account: the username for logging on to the MySQL database.
       • Password: the password corresponding to the username for logging on to the MySQL database.
       • Description: the description of the database. Specify a name that is easy to remember and identify, such as transaction database, user database, or test database. This field is optional.

4. View the two added data sources.

Step 2: Create a task

After you add data sources, perform the following steps to perform full migration, incremental synchronization, or schema migration.

1. Choose Task Management > Create Task.

2. Set the source and destination databases.

   • Select the task running cluster. The task will be scheduled to a server that is bound to the cluster. After CloudCanal Community Edition is deployed, a default running cluster is created. Select Default Cluster in the drop-down list as the task running cluster.

   • Select the source OceanBase instance and the destination MySQL instance, and then click Test Connection.

   • Select the source and destination databases for migration, synchronization, or verification to specify the database mapping.

   • Click Next.

3. Complete the feature settings.

   Select Incremental Synchronization. At the first synchronization, CloudCanal checks the table and performs full synchronization. Afterward, CloudCanal consumes binlogs to synchronize only incremental data.

   • Task types include:

     • Full Migration: applies mainly to data migration. You can select this type for full data migration and provisional incremental synchronization.
     • Incremental Synchronization: applies to data synchronization. This is the default value. By default, Full Initialization is enabled for this task type. This task type applies to long-term incremental synchronization.
     • Data Verification: compares data in the source and destination databases to verify the accuracy of migrated data on a one-off or periodic basis. CloudCanal Community Edition does not support this feature.
     • Schema Migration: automatically creates the corresponding database and tables based on the selected database and tables.
     • Data Correction: compares the data in the source and destination databases and overrides the inconsistent data in the destination database with that in the source database. CloudCanal Community Edition does not support this feature.

   • Task Specifications: Use the default specifications, namely the Balanced specifications with 2G memory.

   • Click Next.

4. Perform table and action filtering.

   • Select the table to be synchronized. Make sure that the UPDATE and DELETE operations performed on the destination database are consistent with those performed on the source database. In addition, make sure that the tables in the source database have a PRIMARY KEY or UNIQUE KEY constraint.

   • Click Next.

5. Perform data processing.

   • You can perform the following data processing operations: Add Data Filter, Upload Custom Code, and Batch Operation.

     • Add Data Filter: On the data processing page, you can add a data filter in the Actions column of the corresponding table on the left.
     • Upload Custom Code: Real-time custom code processing allows you to write custom data processing logic in Java. After the custom processing logic is uploaded to CloudCanal, it is automatically applied when a data synchronization task is performed for full synchronization or incremental synchronization. Then, the data is synchronized to the destination data source.
     • Batch Operation: The Batch Add Data Filter and Batch Trim Column options are available.

   • Click Next.

6. Confirm the task information.

   Confirm the task information and then click Create Task.

Step 3: View the task status

After the incremental synchronization task is created, Schema Migration, Full Migration, and Incremental Synchronization are automatically performed.

Return to the task management page of CloudCanal and click Refresh in the upper-right corner to view the real-time task status.