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 is an open-source component that subscribes to and consumes the incremental data in MySQL Binlog. It parses incremental log data of a MySQL database and can be used for data synchronization.

Data link:

ob_cluster -> oblogreader -> oblogmsg -> canal_server -> canal_client -> mysql

Components:

• 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 libobcdc encapsulated in C++ and provides multiple serialized outputs for the created LogMessage objects 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.

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

   Example of modifying 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.

   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.