Use Canal to synchronize data from OceanBase Database to a MySQL database

This topic describes how to synchronize data from an OceanBase database to a MySQL database by using Canal and OceanBase Binlog Service.

Implementation logic of OceanBase CDC

OceanBase Binlog Service is a proxy service for managing incremental logs of OceanBase Database. Designed based on libobcdc (previously known as liboblog), OceanBase Binlog Service provides links for applications to access and manage real-time incremental data logs of OceanBase Database. OceanBase Binlog Service enables you to subscribe to incremental logs in isolated networks and supports multiple access methods.

Canal extracts incremental data from MySQL databases by parsing MySQL binlogs and sends it to subscribers for consumption and processing. This enables users to easily achieve real-time data synchronization, real-time analysis, and other requirements.

The data link is as follows:

ob_cluster -> obbinlog -> canal_client -> mysql

The components are described as follows:

• OceanBase Database: The business database that needs to generate Binlog logs.

• OceanBase Database Proxy: The ODP (also known as OBProxy) for the business database, which provides connection services for clients subscribing to Binlog.

• OceanBase Binlog Tool: The core component of the OceanBase Binlog Service, obbinlog (formerly known as oblogproxy), responsible for pulling clog and generating Binlog logs.

Procedure

Step 1: Install OceanBase Binlog Service

Follow the steps in Create a binlog cluste to install and deploy OceanBase Binlog Service.

Step 2: Install the Canal server

1. Download the software package.

   Download canal-for-ob.deployer.tar.gz.

   wget https://github.com/oceanbase/canal/releases/download/canal-for-ob-1.1.6-alpha/canal-for-ob.deployer.tar.gz
   

2. Decompress the package to the /Canal_Home/canal-for-ob directory.

   mkdir /Canal_Home/canal-for-ob && tar zxvf canal-for-ob.deployer.tar.gz  -C /Canal_Home/canal-for-ob
   

3. Modify the canal.properties configuration file.

   Modify /Canal_Home/canal-for-ob/conf/canal.properties.

   canal.serverMode = tcp
   canal.destinations = example
   canal.instance.global.spring.xml = classpath:spring/ob-default-instance.xml
   

4. Configure a Canal instance.

   Delete instance.properties from /Canal_Home/canal-for-ob/conf/example and then rename ob-instance.properties as instance.properties.

   The following example shows how to modify instance.properties:

   cd /Canal_Home/canal-for-ob/conf/example
   vi instance.properties
   
   # OceanBase cluster parameters
   canal.instance.oceanbase.rsList=10.10.10.1:2882:2881;10.10.10.2:2882:2881;10.10.10.3:2882:2881
   canal.instance.oceanbase.username=root@mysql001#test4000
   canal.instance.oceanbase.password=******
   canal.instance.oceanbase.startTimestamp=0
   
   # oblogproxy parameters
   canal.instance.oceanbase.logproxy.address=10.10.10.1:2983
   canal.instance.oceanbase.logproxy.sslEnabled=false
   canal.instance.oceanbase.logproxy.serverCert=../conf/${canal.instance.destination:}/ca.crt
   canal.instance.oceanbase.logproxy.clientCert=../conf/${canal.instance.destination:}/client.crt
   canal.instance.oceanbase.logproxy.clientKey=../conf/${canal.instance.destination:}/client.key
   
   # Specify whether to remove the tenant ID prefixed to the database name. The default database name is in the [tenant].[db] format in the log file exported by the oblogproxy.
   canal.instance.oceanbase.tenant=mysql001
   canal.instance.parser.excludeTenantInDbName=true
   
   # Filter the logs. Specify the logs in the [tenant].[database].[table] format. Regular expressions are supported.
   canal.instance.filter.regex=mysql001.*.*
   

5. Start the Canal server.

   cd /Canal_Home/canal-for-ob && sh bin/startup.sh
   

Step 3: Configure the RDB adapter

1. Download the package.

   Download canal-for-ob.adapter.tar.gz.

   wget https://github.com/oceanbase/canal/releases/download/canal-for-ob-1.1.6-alpha/canal-for-ob.adapter.tar.gz
   

2. Decompress the package to the /Canal_Home/canal-adapter-for-ob directory.

   mkdir /Canal_Home/canal-adapter-for-ob && tar zxvf canal-for-ob.adapter.tar.gz -C /Canal_Home/canal-adapter-for-ob
   

3. Modify the application.yml file.

   cd /Canal_Home/canal-adapter-for-ob/conf
   vi application.yml
   
   canalAdapters:
   - instance: example # canal instance Name or mq topic name
     groups:
     - groupId: g1
       outerAdapters:
       - name: logger
       - name: rdb
         key: mysql1
         properties:
           jdbc.driverClassName: com.mysql.jdbc.Driver
           jdbc.url: jdbc:mysql://10.10.10.1:3306/test_data?useUnicode=false
           jdbc.username: root
           jdbc.password: ******
   

4. Modify the database or table mapping on the adapter. The following command modifies the database mapping.

   cd /Canal_Home/canal-adapter-for-ob/conf/rdb
   vi application.yml
   
   ## Mirror schema synchronize config
   dataSourceKey: defaultDS
   destination: example
   groupId: g1
   outerAdapterKey: mysql1
   concurrent: true
   dbMapping:
   mirrorDb: true
   database: test_data
   

5. Start the Canal client.

   cd /Canal_Home/canal-adapter-for-ob && sh bin/startup.sh
   

6. Check data synchronization.

   Write data in the source OceanBase database and check data synchronization in the destination MySQL database.

Limitations

• The source table must have a primary key. Otherwise, if a record is deleted from the source table, the synchronized table in the destination is fully deleted.

• The CREATE TABLE and CREATE COLUMN DDL operations can be synchronized.