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 comprises core members from big enterprises, with backgrounds in database kernel development, large-scale distributed systems, and cloud product development. They are well-versed in databases, distributed systems, and cloud products and services.

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

You have installed and deployed CloudCanal Community Edition, applied for a free license, and activated it.

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 Aliyun Database.

     • Aliyun Database: a database instance that you have purchased on Alibaba Cloud.
     • Self-built database: a database instance that you have 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, select the Self-built Database option and specify the information of the MySQL database instance that you want to add.

       Set up the MySQL data source:

       • Network Address: the IP address of the MySQL database.
       • Authentication: the authentication method. The options are Account Password, Certificate, and No Authentication. The default value is Account Password.
       • Account: the username for connecting to the MySQL database.
       • Password: the password corresponding to the username.
       • Description: the description of the data source. This parameter is optional. It is used to remark a name that is easy to remember for future reference. For example, you can use the transaction database, user database, and test database as the name.

     • To add OceanBase, select the Self-built Database option and specify the information of the OceanBase database instance that you want to add.

       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 the ODP.
       • oblogproxy host: the IP address of the OceanBase Binlog Service. This parameter is required when you synchronize data incrementally from OceanBase Database as the source and can be ignored when you synchronize data to OceanBase Database as the target. 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 method. The options are Account Password, Certificate, and No Authentication. The default value is Account Password.
       • Account: the username for connecting to OceanBase Database. In direct connection mode, the username format is username@tenant name; in ODP connection mode, the username format is username@tenant name#cluster name.
       • Password: the password corresponding to the username.
       • Description: the description of the data source. This parameter is optional. It is used to remark a name that is easy to remember for future reference. For example, you can use the transaction database, user database, and test database as the name.

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 the Cluster for Task Execution. The task will be scheduled to run on a server in the cluster. After the Community Edition is deployed, a default execution cluster will be provided.

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

   • Select the databases for migration, synchronization, or verification and configure the mappings between the databases.

   • Click Next.

3. Configure features.

   Select the Incremental Synchronization feature. The system will first synchronize all data by querying tables, and then synchronize incremental data by consuming binlog.

   • The task types are described as follows:

     • Full Migration: Data migration is the primary task. This mode is suitable for full data migration and short-term incremental synchronization.
     • Incremental Synchronization: The default mode. It includes full initialization by default. Data synchronization is the primary task. This mode is suitable for long-term incremental synchronization.
     • Data Verification: The accuracy of data migration is verified by comparing data in the source and target databases. Verification can be performed once or multiple times at scheduled intervals. The Community Edition does not support this feature.
     • Schema Migration: New databases and tables are automatically created in the target database based on the selected databases and tables.
     • Data Correction: The inconsistent data in the target database is automatically corrected to align with that in the source database. The Community Edition does not support this feature.

   • The default task specification is sufficient. It includes the Balanced configuration and 2 GB of memory.

   • Click Next.

4. Filter tables and actions.

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

   • Click Next.

5. Configure data processing.

   • Select the columns to synchronize from the tables.

   • Click Next.

6. Verify the task configuration.

   On the last page, verify the configuration and click Create.

Check the task status

After the task is created, it will migrate schemas, data, and then synchronize incremental data. The progress bar will change accordingly. Go to the CloudCanal task management console, refresh the page, and check the real-time status of the task from schema migration, data initialization, to data synchronization.