Don't waste time with broken data syncs - do it right with Apache SeaTunnel

In this guide, we will show you a lightweight solution for migrating and synchronizing data from MySQL to OceanBase using Apache SeaTunnel (SeaTunnel). We will leverage its built-in engine Zeta engine, which supports full data synchronization, offline incremental synchronization, and Change Data Capture (CDC).

At Q2BSTUDIO, experts in development and technology services, we implement efficient solutions for data management and migration, ensuring each process is optimized for business needs.

Preparing the Execution Environment

Before starting, make sure your environment is ready.

Install Java

SeaTunnel requires Java 8 or higher. Java 8 is recommended, but later versions should also work.

Verify that Java is correctly configured by running:

java -version

Make sure JAVA_HOME is correctly configured.

Download and Install Apache SeaTunnel

wget https://dlcdn.apache.org/seatunnel/2.3.9/apache-seatunnel-2.3.9-bin.tar.gz
tar -zxvf apache-seatunnel-2.3.9-bin.tar.gz

Installing Connector Plugins

The SeaTunnel installation package only contains the framework core and the Zeta engine. To connect various data sources, you need to download and configure the required plugins.

Automatic Plugin Installation

Modify the configuration file to specify the necessary connectors:

connector-cdc-mysql
connector-jdbc
connector-fake
connector-console

Run the following command to install the plugins:

sh bin/install-plugin.sh 2.3.9

Verifying SeaTunnel Installation

To verify the correct installation of SeaTunnel, run a batch processing test:

./bin/seatunnel.sh --config ./config/v2.batch.config.template -m local

If everything is correctly configured, you will see output indicating the process was successful.

Full Data Synchronization

To verify full data synchronization, test tables are created in both MySQL and OceanBase.

Full Synchronization Configuration

It is recommended to manually migrate table schemas to avoid errors.

env {
  parallelism = 5
  job.mode = BATCH
}
source {
    Jdbc {
        url = jdbc:mysql://127.0.0.1:3306/mysql
        driver = com.mysql.cj.jdbc.Driver
        user = user
        password = password
        query = select * from database.table1
    }
}
sink {
    jdbc {
        url = jdbc:mysql://127.0.0.1:2883/mysql
        driver = com.mysql.cj.jdbc.Driver
        user = user
        password = password
        database = database
        table = table1
    }
}

Expected results:

Total Read Count: 100000
Total Write Count: 100000
Total Failed Count: 0

Incremental Synchronization

For incremental synchronization, queries can be used by filtering on columns such as id or updatetime.

source {
    Jdbc {
        query = SELECT * FROM database.table1 WHERE updatetime > '2024-01-01'
    }
}

The use of Apache DolphinScheduler is recommended to obtain dynamic values and automate data synchronization.

CDC (Change Data Capture) Synchronization

To enable CDC, verify that MySQL binlog is active.

Configuration example:

source {
  MySQL-CDC {
    base-url = jdbc:mysql://127.0.0.1:3306/mysql
    username = user
    password = password
    table-names = [database.table1, database.table2]
    startup.mode = initial
  }
}

Conclusion

This article detailed the configuration for data synchronization from MySQL to OceanBase with Apache SeaTunnel. At Q2BSTUDIO, we help companies optimize their data processing and management through scalable and efficient solutions.

If you need advice on data flows and integration tools, at Q2BSTUDIO we are ready to take your processes to the next level.