Synchronize data from OceanBase Database to a MySQL database using Canal

This topic describes how to use Canal and OceanBase Binlog Service to synchronize data from OceanBase Database to a MySQL database.

Implementation logic of OceanBase CDC

OceanBase Binlog Service is an incremental log proxy service of OceanBase Database. It uses libobcdc (formerly known as liboblog) to provide real-time incremental link access and management as a service, enabling applications to access OceanBase incremental logs. It addresses the requirement for subscribing to incremental logs in network-isolated environments and supports multiple link access methods.

Canal parses MySQL's Binlog logs to extract incremental data from the database and send it to subscribers for consumption and processing. This enables you to easily synchronize and analyze data in real time.

The data link is as follows:

ob_cluster -> obbinlog -> canal_client -> mysql

The components are described as follows:

• OceanBase Database: the business database that generates Binlog logs.

• OceanBase Database Proxy: the ODP (also known as OBProxy) of the business database. It provides connection services to clients subscribing to Binlog.

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

Procedure

Step 1: Install OceanBase Binlog Service

Follow the instructions in Create a Binlog cluster to install and deploy OceanBase Binlog Service.

Step 2: Install 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 directory /Canal_Home/canal-for-ob.

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

3. Modify the configuration file canal.properties.

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

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

4. Configure a Canal instance.

   Delete the instance.properties file from the /Canal_Home/canal-for-ob/conf/example directory and rename the ob-instance.properties file to instance.properties.

   Here is an example of the instance.properties file:

   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
   
   # OceanBase LogProxy 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
   
   # Whether to remove the tenant prefix from database names. By default, the log proxy outputs logs in the [tenant].[db] format.
   canal.instance.oceanbase.tenant=mysql001
   canal.instance.parser.excludeTenantInDbName=true
   
   # Log filtering. Specify the filter in the [tenant].[database].[table] format. Regular expressions are supported.
   canal.instance.filter.regex=mysql001.*.*
   

5. Start 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 directory /Canal_Home/canal-adapter-for-ob.

   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 mappings between the adapter and the database and table (database mappings are used as an example).

   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. View the data synchronization status.

   Write data to the source OceanBase database and view the synchronized data in the target MySQL database.

Limitations

• The source table must have a primary key. Otherwise, if the source table is deleted, the entire target table will be deleted during synchronization.

• DDL operations such as creating tables and columns are supported.