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 transmission channels. It is available in SaaS and private deployment modes. The development team behind CloudCanal comprises core members from major internet companies with extensive experience in database kernel development, large-scale distributed system construction, and cloud product development.

This topic describes 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

Follow Get started (Linux/MacOS) to install and deploy CloudCanal Community Edition, apply for a free license, and activate it.

Migrate data

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 Alibaba Cloud database.

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

   • 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 have 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: an optional field for remarking the database, such as transaction database, user database, test database, to facilitate identification when used.

       

     • 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 have deployed.

       Set up the OceanBase data source:

       • Network Address: the IP address for connecting to OceanBase Database, which can be the direct connection IP address or the IP address of an ODP.
       • oblogproxy host: the IP address of the OceanBase Binlog service. If OceanBase Database is used as the source database for incremental synchronization, this parameter cannot be empty; if it is used as the target database, this parameter can be empty. For more information about the OceanBase Binlog service, see OceanBase Binlog service.
       • OceanBaseRpcPort: the port number of OceanBase RPC, the default value is 2882.
       • 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 direct connection format: username@tenant name; in ODP connection format: username@tenant name#cluster name.
       • Password: the password corresponding to the username.
       • Description: an optional field for remarking the database, such as transaction database, user database, test database, to facilitate identification when used.

       

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 selected cluster. After the Community Edition is deployed, a default task run cluster will be provided.

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

   • Select the databases for migration, synchronization, or verification and configure 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 corresponding binlog for incremental data synchronization.

   • The task types are described as follows:

     • Full Migration: Primarily for full data migration and short-term incremental synchronization tasks.
     • Incremental Synchronization: The default option. It includes full initialization by default. Primarily for long-term incremental synchronization tasks.
     • Data Verification: Verifies the accuracy of data migration by comparing the data in the source and target databases. It supports one-time or periodic verification. 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 with a memory size of 2 GB.

   • Click Next after the configuration is completed.

   

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

   • Click Next after the configuration is completed.

   

5. Configure data processing.

   • Select the columns to synchronize from the tables.

   • Click Next after the configuration is completed.

   

6. Create the task.

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

   

Check the status of a task

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

References

For more information about CloudCanal, see CloudCanal Documentation.