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 experts from big enterprises, with backgrounds in database kernel development, large-scale distributed systems, and cloud product development. CloudCanal experts are well-versed in databases, distributed systems, and cloud products 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. You have installed and deployed CloudCanal Community Edition. For more information, see Get started (Linux/MacOS).

2. You have installed and deployed OceanBase Binlog Service. For more information, see Create a Binlog cluster.

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 following parameters.

   • Deployment Type: indicates whether the database is a Self-managed database or an ApsaraDB instance.

     • ApsaraDB: an Alibaba Cloud database instance that you have purchased.
     • Self-built database: a database instance that you have deployed yourself.

   • Database Type: the type of the data source.

     Two data sources, OceanBase and MySQL, are added as examples:

     • Select OceanBase from the self-built database option to add an 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 a direct connection or an ODP connection.
       • oblogproxy host: the IP address of OceanBase Binlog Service. If you synchronize data from OceanBase Database as the source database, this parameter is required. If you synchronize data to OceanBase Database as the target database, this parameter is optional. For more information about OceanBase Binlog Service, see OceanBase Binlog Service.
       • OceanBaseRpcPort: the OceanBase RPC port. 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 parameter. A comment that helps you remember the name and identify the database when you use it. For example, you can use the transaction database, user database, or test database.

       

     • Select MySQL from the self-built database option to add a 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 parameter. A comment that helps you remember the name and identify the database when you use it. For example, you can use the transaction database, user database, or test database.

       

4. View the two data sources that you have added.

   

Create a task

After the data source is added, perform full data migration, incremental synchronization, or schema migration.

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 OceanBase as the source database and MySQL as the target database, and click Test Connection.

   • Select Databases to Migrate, Synchronize, or Verify and configure the database mappings.

   • Click Next.

   

3. Configure the features.

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

   • The task types are described as follows:

     • Full Migration: Primarily for data migration. Suitable for full data relocation and short-term incremental synchronization.
     • Incremental Synchronization: Default option. It includes full initialization by default. Primarily for data synchronization. Suitable for long-term incremental synchronization.
     • Data Verification: Verifies the accuracy of data migration by comparing the data in the source and target databases. The Community Edition does not support this feature.
     • Schema Migration: Automatically creates corresponding databases and tables based on the selected databases and tables.
     • Data Correction: Verifies the data in the source and target databases. Automatically covers inconsistent data with that in the source database. The Community Edition does not support this feature.

   • The default task specification is sufficient for most cases. It uses the Balanced configuration and 2 GB of memory.

   • After the configuration is completed, click Next.

   

4. Filter tables and actions.

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

   • After the configuration is completed, click Next.

   

5. Configure data processing.

   • You can add Data Filter Conditions, upload Custom Code, and perform Batch Operations.

     • Data Filter Conditions: Add Data Filter Conditions in the Actions column on the left side of the data processing page.
     • Custom Code: Custom code allows you to use Java to write custom row processing logic. After you upload the code to CloudCanal, the code will be automatically applied to full and incremental synchronization data during data synchronization.
     • Batch Operations: You can use this option to add data filter conditions in batches or truncate columns in batches.

   • After the configuration is completed, click Next.

   

6. Verify the task configuration.

   After you confirm that the task configuration is correct, click Create Task.

   

Check the status of the task

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

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

References

For more information about CloudCanal, see CloudCanal Documentation.