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 to subscribe to incremental logs in network-isolated environments 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 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 binlogs.

• OceanBase Binlog Service: 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 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 log proxy 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 /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 library 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 OceanBase source database and view the synchronized data in the MySQL target 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 adding columns are supported.