OracleRealtimeMySQL

This article provides implementation ideas, configuration examples, and verification methods for Oracle real-time MySQL scenarios, which can be used as a reference for actual project configuration.

Real-time synchronization of the result table in Oracle to the target table to MySQL data

Case

Synchronize data in cdc_source to cdc_target in real time in Oracle

Premise

• Enable Oracle's logminer
• empower

Login to sysdba

GRANT SELECT ON SYS.V_$DATABASE TO ispong;
GRANT create session, alter session, execute_catalog_role, select any dictionary, select any transaction, select any table, create any table, create any index, unlimited tablespace to ispong;
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM v$database;

solution

Create a Flink SQL job type and add the following dependencies

• flink-sql-connector-oracle-cdc-3.2.1.jar download

CREATE TABLE from_table
(
    USERNAME STRING,
    AGE      INT,
    PRIMARY KEY (USERNAME) NOT ENFORCED
) WITH ( 'connector' = 'oracle-cdc',
      'hostname' = 'localhost',
      'port' = '15201',
      'username' = 'ispong',
      'password' = 'ispong123',
      'database-name' = 'helowin',
      'schema-name' = 'ispong',
      'table-name' = 'CDC_SOURCE',
      'scan.startup.mode' = 'latest-offset'
      );

CREATE TABLE target_table
(
    username STRING,
    age      INT,
    PRIMARY KEY (username) NOT ENFORCED
) WITH ( 'connector' = 'jdbc',
      'url' = 'jdbc:mysql://localhost:30306/isxcode_db?useSSL=false&allowPublicKeyRetrieval=true',
      'driver' = 'com.mysql.cj.jdbc.Driver',
      'table-name' = 'cdc_source',
      'username' = 'ispong',
      'password' = 'ispong123');

INSERT INTO target_table (username, age)
select USERNAME, AGE
from from_table;