Use ChunJun to migrate data from OceanBase Database to a MySQL database|V4.1.0| docs|Distributed Database

Use ChunJun to migrate data from OceanBase Database to a MySQL database

Last Updated：2023-09-06 07:12:37 Updated

ChunJun, formerly known as FlinkX, is a data integration framework based on Flink, which is stable, easy to use, efficient, and integrated with streaming and batch processing APIs. It can realize data synchronization and computation between various heterogeneous data sources based on the Flink real-time computation engine. The change data capture (CDC) components libobcdc and oblogproxy of OceanBase pull incremental commit logs of OceanBase Database. chunjun-connector-oceanbasecdc is integrated with oblogclient to connect to oblogproxy to obtain log data. This enables you to migrate incremental data from OceanBase Database.

This topic describes how to use ChunJun in local mode to migrate data from OceanBase Database in MySQL mode to a MySQL database.

Scenario

Migrate data from the test_tbl1 table in OceanBase Database to the tbl1 table in a MySQL database. The database information is as follows:

Information of OceanBase Database (source database)	Example value
Cluster name	test4000
Host URL	10.10.10.2
Port number	2881
oblogproxy IP address	10.10.10.3
oblogproxy port number	2983
Tenant name (MySQL mode)	mysql001
Username	root
Password of the user	******
Schema name	test_data
Table name	test_tbl1

Information of the MySQL database (destination database)	Example value
Host URL	10.10.10.1
Port number	3306
Username	root
Password of the user	******
Schema name	test_ob_to_mysql
Table name	tbl1

Prerequisites

You have installed JDK 1.8 and configured the JAVA_HOME environment variable.
You have installed and deployed oblogproxy.

Procedure

Step 1: Prepare the ChunJun environment

Download and decompress the ChunJun package.

Download chunjun-dist.tar.gz.

wget https://github.com/DTStack/chunjun/releases/download/v1.12.6/chunjun-dist.tar.gz

Decompress the package to the /chunjun_Home/chunjun directory.

mkdir /ChunJun_Home/chunjun && tar zxvf chunjun-dist.tar.gz -C /ChunJun_Home/chunjun

Configure the ChunJun environment variable.

export ChunJun_HOME=/ChunJun_Home/chunjun-dist

Step 2: Configure the JSON file

Configure the JSON file based on the environment information to enable migration of data from the test_tbl1 table in OceanBase Database to the tbl1 table in the MySQL database.

Example:

[root@xxx /]
$cd /ChunJun_Home/chunjun-dist/chunjun-examples/json

[root@xxx /ChunJun_Home/chunjun-dist/chunjun-examples/json]
$mkdir test_data

[root@xxx /ChunJun_Home/chunjun-dist/chunjun-examples/json]
$cd mkdir test_data

[root@xxx /ChunJun_Home/chunjun-dist/chunjun-examples/json/test_data]
$vi test_ob_to_mysql.json

{
  "job": {
    "setting": {
      "errorLimit": {
        "record": 0,
        "percentage": 0.02},
      "speed": {"bytes": 0,
      "channel": 1}
    },
    "content": [
      {
        "reader": {
          "name": "oceanbasecdcreader",
          "table": {
            "tableName": "test_tbl1"},
          "parameter": {
            "logProxyHost": "10.10.10.3",
            "logProxyPort": 2983,
            "obReaderConfig": {
              "rsList": "10.10.10.2:2882:2881",
              "username": "root",
              "password": "******"
            },
            "cat": "insert,delete,update",
            "column": [
              "*"
            ]
          }
        },
        "writer": {
          "name": "mysqlwriter",
          "parameter": {
            "column": [
              "*"
            ],
            "connection": [
              {
                "jdbcUrl": "jdbc:mysql://10.10.10.1:3306/test_ob_to_mysql",
                "table": [
                  "tbl1"
                ]
              }
            ],
            "username": "root",
            "password": "******"
          }
        }
      }
    ]
  }
}

Step 3: Run the JSON configuration file

Start a JVM process to execute the ChunJun task.

Go to the /ChunJun_Home/chunjun-dist directory and run the following command:

[root@xxx /]
$cd /ChunJun_Home/chunjun-dist

[admin@xxx /ChunJun_Home/chunjun-dist]
$sh bin/chunjun-local.sh -job chunjun-examples/json/test_data/test_ob_to_mysql.json

          #                               #
          #                               #
          #
  #####   ######   #     #  # ####     ####   #     #  # ####
 #        #     #  #     #  ##    #       #   #     #  ##    #
 #        #     #  #     #  #     #       #   #     #  #     #
 #        #     #  #    ##  #     #       #   #    ##  #     #
  #####   #     #   #### #  #     #       #    #### #  #     #
                                          #


Reference site: https://dtstack.github.io/chunjun

chunjun is starting ...
CHUNJUN_HOME is auto set /ChunJun_Home/chunjun-dist/
FLINK_HOME is empty!
HADOOP_HOME is empty!
...
<ellipsis>
...
*********************************************
numWrite                  |  3
last_write_num_0          |  0
conversionErrors          |  0
writeDuration             |  20149
duplicateErrors           |  0
numRead                   |  3
snapshotWrite             |  0
otherErrors               |  0
readDuration              |  95
byteRead                  |  389
last_write_location_0     |  0
byteWrite                 |  389
nullErrors                |  0
nErrors                   |  0
*********************************************

2023-01-06 20:34:47,448 - 50317 INFO  [main] com.dtstack.chunjun.Main:program Flink_Job execution success

Step 4: Modify the data in OceanBase Database to generate incremental data.

Log on to OceanBase Database in MySQL mode and insert a data record into the test_tbl1 table.

[admin@xxx /home/admin]
$obclient -h10.10.10.2 -P2881 -uroot@mysql001 -p -Dtest_data
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221709678
Server version: OceanBase 4.0.0.0 (r101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322) (Built Dec  7 2022 16:22:15)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [test_data]> INSERT INTO test_tbl1 VALUES(4,'Macao',853);
Query OK, 1 row affected

Step 5: View the data migration status

Log on to the MySQL database to view the data migration result.

[admin@xxx /home/admin]
$mysql -h10.10.10.1 -P3306 -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 189769
Server version: 5.7.26-log MySQL Community Server (GPL)

<ellipsis>

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use test_ob_to_mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test_ob_to_mysql]> SELECT * FROM tbl1;
+------+-----------+------+
| col1 | col2      | col3 |
+------+-----------+------+
|    1 | China     |   86 |
|    2 | Taiwan    |  886 |
|    3 | Hong Kong |  852 |
|    4 | Macao     |  853 |
+------+-----------+------+
4 rows in set

More information

For more information about how to get started with ChunJun, see Quick start.
For more information about how to configure the JSON file for OceanBase Database that serves as the source database, see OceanBase CDC Source.
For more script examples, see the files in the /chunjun-dist/chunjun-examples folder of the ChunJun project.