Migrate data from a MySQL OceanBase database to a MySQL database using CloudCanal

CloudCanal is a data migration and synchronization tool that helps enterprises build high-quality data channels quickly. It is available in SaaS and private deployment modes. The development team behind CloudCanal consists of seasoned professionals from big companies, with backgrounds in database kernel development, large-scale distributed systems, and cloud product management. CloudCanal understands databases, distributed systems, 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 the CloudCanal Community Edition.

2. Follow Deploy the OceanBase Binlog service to install and deploy the 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 Alibaba Cloud database.

     • Alibaba Cloud: the database instance that you have purchased on Alibaba Cloud.
     • Self-managed database: the database instance that you have deployed yourself.

   • Database Type: the type of the data source.

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

     • To add OceanBase Database, select the self-managed database and choose OceanBase from the drop-down list.

       Set up OceanBase Database:

       • 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 use OceanBase Database as the source database for incremental synchronization, this parameter is required. If you use OceanBase Database as the target database, this parameter can be left empty. 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. You can choose Account Password, Predefined Account Password, or No Account Password. The default value is Account 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: the description of the data source. This parameter is optional. It is used to remark a name for easy identification when used, such as transaction database, user database, and test database.

     • To add a MySQL database, select the self-managed database and choose MySQL from the drop-down list.

       Set up the MySQL database:

       • Network Address: the IP address of the MySQL database.
       • Authentication: the authentication mode. You can choose Account Password, Predefined Account Password, or No Account Password. 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 for easy identification when used, such as transaction database, user database, and 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 by following these steps.

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 Incremental Synchronization. The system will first perform full synchronization by querying tables, and then perform incremental synchronization by consuming binlog entries.

   • 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. 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 overwrites inconsistent data with the data in the source database. The Community Edition does not support this feature.

   • The default task specification is sufficient for most scenarios. You can leave the settings as they are.

   • 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 a unique constraint.

   • After the configuration is completed, click Next.

5. Configure data processing.

   • Add data filtering conditions, upload custom code, and perform batch operations.

     • Data filtering conditions: Add data filtering conditions in the Actions column on the left side of the data processing page.
     • Upload custom code: Custom code allows you to process data rows in real time using Java. After you upload the code to CloudCanal, the code will take effect automatically on all full and incremental synchronization tasks. Then the data will be sent to the destination data source.
     • Batch operations: There are options for batch adding data filter conditions and batch trimming columns.

   • After the configuration is completed, click Next.

6. Verify the task configuration.

   After you confirm that the task configuration is correct, click Create Task on the right side of the page.

View the task status

After an incremental synchronization task is created, it will default 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.