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. Based on libobcdc (formerly known as liboblog), it provides real-time incremental link access and management capabilities in the form of a service. This enables applications to access OceanBase incremental logs. It can meet the log subscription requirements in network-isolated scenarios 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 users to easily implement real-time data synchronization and real-time data analysis.

Data transmission process:

ob_cluster -> obbinlog -> canal_client -> mysql

Component overview:

• 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 logs.

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

Procedure

Step 1: Install OceanBase Binlog Service

Follow 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. The format is [tenant].[database].[table]. 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 /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 mappings between the adapter and the database/tables (database mappings serve 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 OceanBase source database and view the synchronized data in the MySQL target database.

Limitations

• The source and target tables must have primary keys. Otherwise, if the source table does not have a primary key, the entire target table will be deleted when a record is deleted from the source table.

• DDL operations are supported for creating tables and adding columns.