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 can meet the demand of subscribing to incremental logs in network-isolated scenarios and supports multiple link access methods.

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

Data transmission:

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 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 steps 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 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 logproxy outputs the logs with the database name in the format of [tenant].[db].
   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 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 and the 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 data synchronization 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.