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 oblogproxy.

Implementation logic of OceanBase CDC

oblogproxy is a proxy service for managing incremental logs of OceanBase Database. Designed based on libobcdc (previously known as liboblog), oblogproxy provides links for applications to access and manage real-time incremental data logs of OceanBase Database. oblogproxy 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 -> oblogreader -> oblogmsg -> canal_server -> canal_client -> mysql

The components are described as follows:

• libobcdc is a fundamental component of OceanBase CDC. oblogproxy is derived from libobcdc and libobcdc depends on oblogmsg. libobcdc is a C++ dynamic library. It delivers incremental logs that are pulled from an OceanBase cluster in the transaction commitment sequence. The incremental logs are delivered to consumers by creating related objects based on the LogMessage protocol.

• ObLogReader is a C++ encapsulation of libobcdc and provides multiple serialized outputs for the LogMessage objects created based on libobcdc.

• As an incremental log proxy, oblogproxy does not forward incremental data, but only creates the corresponding oblogreader sub-process for a new connection request. Then, the oblogreader sub-process directly sends the incremental log records serialized by LogMessage to the client through this connection. oblogproxy is used to manage the lifecycle of all oblogreader sub-processes on the local server.

• logclient deserializes the byte stream in compliance with the LogMessage protocol and then generates LogMessage objects for consumers to call, for example, for interconnection with Canal.

Procedure

Step 1: Install oblogproxy

Install and deploy oblogproxy. For more information, see Install oblogproxy.

Step 2: Install the Canal server

1. Download the software package from 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 = obtest2
   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
   
   # Specify parameters of the OceanBase cluster.
   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
   
   # Specify parameters of oblogproxy.
   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 software package from 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.