Migrate data from a MySQL database to OceanBase 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 and cloud product experts who have built large-scale distributed systems.

This topic will guide you on how to use CloudCanal Community Edition v2.2.5.36 to synchronize data from a MySQL database to a MySQL database in OceanBase Database.

Prerequisites

You have installed CloudCanal Community Edition, applied for a free license, and activated it. For more information, see Get started (Linux/MacOS).

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 Alibaba Cloud database instance that you purchased.
     • Self-managed database: a database instance that you deployed yourself.

   • Database Type: the type of the data source.

     You can add MySQL and OceanBase databases as the source and target databases respectively:

     • To add MySQL as the source database, select the MySQL database from the Self-managed database section and specify the details of the MySQL database instance that you deployed.

       Set up the MySQL data source:

       • Network Address: the IP address of the MySQL database.
       • Authentication: the authentication method. The options are Account and Password, Certificate, and No Authentication. The default value is Account and Password.
       • Account: the username for connecting to the MySQL database.
       • Password: the password corresponding to the username.
       • Description: Optional field. It is recommended to use a memorable name for easy identification when in use, such as transaction database, user database, test database, etc.

       

     • To add OceanBase as the source database, select the OceanBase database from the Self-managed database section and specify the details of the OceanBase database instance that you deployed.

       Set up 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 the OceanBase Binlog service. If OceanBase Database is used as the source database for incremental synchronization, this parameter is mandatory; if it is used as the target database, this parameter can be left empty. For more information about the OceanBase Binlog service, see OceanBase Binlog service.
       • OceanBaseRpcPort: the OceanBase RPC port, which is 2882 by default.
       • Authentication: the authentication method. The options are Account and Password, Certificate, and No Authentication. The default value is Account and Password.
       • Account: the username for connecting to OceanBase Database. In a direct connection, the username format is username@tenant name; in an ODP connection, the username format is username@tenant name#cluster name.
       • Password: the password corresponding to the username. Description: Optional field. Note a name that is easy to remember for identification when in use, such as transaction database, user database, test database, etc.

       

4. View the two data sources that you 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 the source MySQL database and the target OceanBase database. Then click Test Connection.

   • Select the databases to migrate, synchronize, or verify. Specify the database mappings.

   • Click Next.

   

3. Configure features.

   Select the Incremental Synchronization feature. The system will first perform full data synchronization and then consume the incremental data from the binlog.

   • The task types are described as follows:

     • Full Migration: Primarily for full data migration and short-term incremental synchronization.
     • Incremental Synchronization: The default option. It includes full initialization by default. Primarily for long-term incremental synchronization.
     • Data Verification: Accurately verifies the migrated data by comparing the data in the source and target databases. It can be verified once or multiple times at scheduled intervals. 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: Automatically corrects inconsistent data in the target database with data in the source database. The Community Edition does not support this feature.

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

   • Click Next after the configuration is complete.

   

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 table must have a primary key or a unique constraint.

   • Click Next after the configuration is complete.

   

5. Configure data processing.

   • Select the columns to synchronize from the tables.

   • Click Next after the configuration is complete.

   

6. Create the task.

   After confirming that the configuration is correct, click Create.

   

View the task status

After the task is created, the system will perform schema migration, full data migration, and incremental synchronization. The progress bar changes accordingly. Go to the CloudCanal task management console, refresh the page, and view the real-time status of the task. The status changes from schema migration to data initialization and then to data synchronization.

References

For more information about CloudCanal, see CloudCanal documentation.