Migrate data from a MySQL OceanBase database to a MySQL database using CloudCanal

CloudCanal is a data migration and synchronization tool that helps enterprises quickly build high-quality data channels. It is available in SaaS and private deployment modes. The development team behind CloudCanal consists of seasoned professionals from big companies. With backgrounds in database kernel development, large-scale distributed systems, and cloud product development, they understand databases, distributed systems, and cloud product business and services.

This topic describes how to use CloudCanal Community Edition v2.2.6.9 to migrate data from a MySQL OceanBase database to a MySQL database.

Prerequisites

1. Install and deploy CloudCanal Community Edition.

2. Follow Install OceanBase Binlog Service to install and deploy OceanBase Binlog Service.

Procedure

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

Add data sources

1. Log in to the CloudCanal console.

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

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

   • Deployment Type: indicates whether the data source is a Self-managed database or an Alibaba Cloud database.

     • Alibaba Cloud: an instance of a database that you have purchased on Alibaba Cloud.
     • Self-managed database: an instance of a database that you have deployed yourself.

   • Database Type: the type of the data source.

     You can add the OceanBase and MySQL data sources as the source and target databases for synchronization respectively:

     • Select OceanBase from the self-managed database option to add the OceanBase database instance that you have deployed.

       Specify the following parameters for the OceanBase data source:

       • Network Address: the IP address for connecting to OceanBase Database, which can be direct connection or connection through ODP.
       • oblogproxy host: the IP address of OceanBase Binlog Service. If you use OceanBase Database as the source database for incremental synchronization, this parameter is required. If you use OceanBase Database as the target database, this parameter can be left empty. For more information about OceanBase Binlog Service, see OceanBase Binlog Service.
       • OceanBaseRpcPort: the port of OceanBase Rpc. The default value is 2882.
       • Authentication: the authentication mode. It can be No Password, With Password, or Username/Password. The default value is Username/Password.
       • Username: the username for connecting to OceanBase Database. In direct connection, the username format is username@tenant name; in ODP connection, the username format is username@tenant name#cluster name.
       • Password: the password corresponding to the username.
       • Description: an optional field for remarking. This field helps you identify the database when you use it, such as transaction database, user database, test database.

     • Select MySQL from the self-managed database option to add the MySQL database instance that you have deployed.

       Specify the following parameters for the MySQL data source:

       • Network Address: the IP address of the MySQL database.
       • Authentication: the authentication mode. It can be No Password, With Password, or Username/Password. The default value is Username/Password.
       • Username: the username for connecting to the MySQL database.
       • Password: the password corresponding to the username.
       • Description: an optional field for remarking. This field helps you identify the database when you use it, such as transaction database, user database, test database.

4. View the added data sources.

Create a task

After the data source is added, follow these steps to migrate data in full, perform incremental synchronization, and migrate schemas.

1. Go to the Task Management page and click Create Task.

2. Configure the source and target databases.

   • Select Task Run Cluster. The task will be scheduled to run on a server in the cluster. After the Community Edition is deployed, a default run cluster will be provided.

   • Select the source OceanBase database and the target MySQL database, and click Test Connection.

   • Select the Databases to migrate, synchronize, or verify and configure the database mappings.

   • Click Next.

3. Configure features.

   Select Incremental Synchronization. The system will first perform full data synchronization by querying tables, and then perform incremental data synchronization by consuming binlog.

   • The task types are described as follows:

     • Full migration: Primarily for full data migration and short-term incremental synchronization.
     • Incremental synchronization: the default task type. It includes full initialization by default. Primarily for long-term incremental synchronization.
     • Data verification: verifies the accuracy of data migration by comparing the data in the source and target databases. This feature is not supported in the Community Edition.
     • Schema migration: automatically creates corresponding databases and tables based on the selected databases and tables.
     • Data correction: automatically corrects inconsistent data in the target database to be consistent with the source database. This feature is not supported in the Community Edition.

   • The default task specification, which comprises the Balanced configuration and 2 GB of memory, is sufficient for most scenarios.

   • Click Next.

4. Filter tables and actions.

   • Select the tables to synchronize. To ensure consistency of the UPDATE and DELETE operations between the target and source databases, the source database table must have a primary key or unique constraint.

   • Click Next.

5. Configure data processing.

   • You can add data filtering conditions, upload custom code, and perform batch operations.

     • Data filtering conditions: Add data filtering conditions in the Actions column on the left side of the data processing page.
     • Upload custom code: Custom code allows you to use the Java language to write real-time data processing logic for each data row. After the code is uploaded to the CloudCanal platform, the custom processing logic will be automatically applied to full and incremental synchronization data during data synchronization task execution, and then the processed data will be transmitted to the target data source.
     • Batch operations: You can add data filtering conditions in batches and trim columns in batches.

   • Click Next.

6. Verify the task configuration.

   After the configuration is verified, click Create Task.

View the task status

After an incremental synchronization task is created, it will automatically perform Schema Migration, Full Data Migration, and Incremental Synchronization.

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