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.

CDC implementation logic

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

Canal parses MySQL binlog logs to extract incremental data from the database and send it to subscribers for consumption and processing. This enables you to easily implement real-time data synchronization and real-time data analysis.

Data transmission process:

ob_cluster -> obbinlog -> canal_client -> mysql

Component introduction:

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

• OceanBase Binlog Tool: 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 the instructions in Create a Binlog cluster 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 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 the 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, LogProxy outputs logs with database names 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 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 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 database and 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 source OceanBase database and view the synchronized data in the target MySQL 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.