Use Canal to synchronize data

Canal is an open-source tool for parsing database change logs. It is used in the subscription and consumption of incremental data of MySQL databases. Canal is widely used for real-time data synchronization, and data integration between heterogeneous systems.

This topic describes how to use the Canal server and Canal adapter to synchronize data between MySQL-compatible tenants of OceanBase Cloud.

Background information

• Canal server

  It connects to a MySQL database and simulates the behavior of a MySQL slave. Specifically, it parses the binlog files of the database to extract changed data from binlog and provides the data in a format that is easy to process to downstream systems for consumption.

• Canal adapter

  As an extension of Canal, the Canal adapter maps and forwards the data changes parsed by the Canal server to different types of downstream data storage systems, such as relational database management system (RDBMS), Elasticsearch, and HBase.

For more information, see the description of Canal at GitHub.

Prerequisites

Before data synchronization, make sure that the following conditions are met:

• You have activated the Binlog service for OceanBase Cloud in the MySQL compatible mode. For more information, see Procedure.
• You have installed Java 1.8 or later.
• You have installed the Canal server.
• You have installed the Canal adapter.
• You have created the required table schema in the target database of OceanBase Cloud, and the table schema is the same as that of the source database.

Procedure

Perform the following steps:

1. Activate the Binlog service in OceanBase Cloud. In the left-side navigation pane of the OceanBase Cloud console, choose Instances > Tenant Management and activate the Binlog service. For more information, see Activate the Binlog service.

2. Create a user for data synchronization and grant the required privileges to the user.

    CREATE USER 'Username'@'%' IDENTIFIED BY 'Password';
    GRANT SELECT ON *.* TO 'test';
   

3. Configure the Canal server. Open the instance.properties file in the conf/example/ directory. Specify the IP address, port number, username, and password of the source MySQL-compatible tenant of OceanBase Cloud, as well as the database and table to listen to. Start the Canal service and monitor its logs to ensure normal running. Here is a sample configuration file:

   vi conf/example/instance.properties
   

    # Configure the connection information about the data source of OceanBase Cloud in the MySQL compatible mode.
    canal.instance.master.address=IP address of the server of the source MySQL-compatible tenant of OceanBase Cloud:Port number of the server
    canal.instance.dbUsername=Username
    canal.instance.dbPassword=Password
   
    # Retain the default values of other parameters.
   

4. Start the Canal server.

   cd /Canal_Home/canal && sh bin/startup.sh
   

   Check the log file to verify that the Canal server is started.

   tail -f logs/canal/canal.log
   

5. Configure the Canal adapter. Go to the configuration directory conf of the Canal adapter, open the application.yml configuration file, and configure the attributes of the relational database (RDB) adapter.

   mode: tcp #tcp kafka rocketMQ rabbitMQ  
   dataSourceKey: defaultDS
   destination: example
   groupId: g1
   outerAdapters:
   - name: rdb
       key: mysql1
       properties:
       jdbc.driverClassName: com.mysql.cj.jdbc.Driver
       jdbc.url: jdbc:mysql://IP address of the target MySQL-compatible tenant:Port number of the target MySQL-compatible tenant/Name of the target database?useUnicode=true&characterEncoding=UTF-8&useSSL=false
       jdbc.username: the username of the target database.
       jdbc.password: the password of the target database.
   

   The Canal adapter supports data storage systems of different types. Each system type corresponds to an adapter. The RDB adapter is a part of the Canal adapter and is dedicated for data synchronization between relational databases. Here is a sample RDB mapping file:

   cat conf/rdb/mytest_user.yml
   

   
   dataSourceKey: defaultDS
   destination: example
   groupId: g1
   outerAdapterKey: mysql1
   concurrent: true
   dbMapping:
   database: the name of the source database.
   table: the name of the source table.
   targetTable: name of the target database.name of the target table
   targetPk:
       id: the primary key.
   mapAll: true
   

6. Start the Canal adapter.

   cd /Canal_Home/adapter && sh bin/startup.sh
   

   Check the log file to verify that the Canal adapter is started.

   tail -f logs/adapter/adapter.log
   

7. Verify the data synchronization. Check the table in the target database to verify that the source data is synchronized.