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 database experts and engineers from leading internet and technology companies. They have in-depth knowledge and skills in database kernel, large-scale distributed systems, and cloud product development. CloudCanal not only understands databases but also grasps distributed architecture and cloud services and business models.

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. Follow Install CloudCanal (Linux/MacOS) to 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 AliCloud.

     • AliCloud: a database instance purchased on Alibaba Cloud.
     • Self-built database: a database instance that you have deployed.

   • Database Type: the type of the data source.

     Two data sources, OceanBase and MySQL, are added as the source and target databases for synchronization:

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

       Configure 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 synchronize data from OceanBase Database as the source, this parameter is required. If you use OceanBase Database as the target, this parameter can be left empty. For more information about OceanBase Binlog Service, see OceanBase Binlog Service.
       • OceanBaseRpcPort: the port number of OceanBase Rpc. The default value is 2882.
       • Authentication: the authentication mode. It can be Password, Token, and No Authentication. The default value is Password.
       • Username: the username for connecting to OceanBase Database. In direct connection, the format is username@tenant name; in ODP connection, the format is username@tenant name#cluster name.
       • Password: the password corresponding to the username.
       • Description: an optional field for remarking a name that is easy to remember, for identification when used. For example, transaction database, user database, test database.

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

       Configure the MySQL data source:

       • Network Address: the IP address of the MySQL database.
       • Authentication: the authentication mode. It can be Password, Token, and No Authentication. The default value is Password.
       • Username: the username for connecting to the MySQL database.
       • Password: the password corresponding to the username.
       • Description: an optional field for remarking a name that is easy to remember, for identification when used. For example, transaction database, user database, test database.

4. View the added data sources.

Create a task

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

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 the databases for migration, synchronization, or verification and configure the database mappings.

   • Click Next.

3. Configure the features.

   Select the Incremental Synchronization feature. 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 data migration and suitable for full data relocation and short-term incremental synchronization.
     • Incremental synchronization: The default option. It includes full initialization by default. Primarily for data synchronization and suitable for long-term incremental synchronization.
     • Data verification: Verifies the accuracy of data migration by comparing the data in the source and target databases. It can verify the data accuracy on a one-time or scheduled basis. 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: Compares the data in the source and target databases and automatically corrects the inconsistent data based on the source data. The Community Edition does not support this feature.

   • The default task specification is sufficient for most scenarios. You can modify the task specification if needed.

   • After the configuration is completed, 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.

   • After the configuration is completed, 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 on the left side of the data processing page in the corresponding table. Actions.
     • Upload custom code: Custom code allows real-time data processing. You can write custom data row processing logic in Java and upload it to CloudCanal. After the data synchronization task performs full data synchronization or incremental synchronization, the custom processing logic will be automatically applied to the synchronized data, and then the data will be transmitted to the target data source.
     • Batch operations: You can choose Add Data Filtering Conditions in Batch and Trim Columns in Batch.

   • After the configuration is completed, click Next.

6. Verify the task configuration.

   After the final check, 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.