flinksql同步oracle数据到mysql

环境:
OS:Centos 7
oracle:19.3.0.0
mysql:5.7

说明:
a.只能同步dml,ddl无法同步

1.下载相应的jar包
cd /opt/flink-1.20.5/lib

#JDBC sink 3.3.0-1.20
wget https://maven.aliyun.com/repository/public/org/apache/flink/flink-connector-jdbc/3.3.0-1.20/flink-connector-jdbc-3.3.0-1.20.jar --ok

#MySQL驱动
wget https://maven.aliyun.com/repository/public/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

#ojdbc8 Oracle驱动
wget https://maven.aliyun.com/repository/public/com/oracle/database/jdbc/ojdbc8/19.21.0.0/ojdbc8-19.21.0.0.jar

wget https://repo1.maven.org/maven2/org/apache/flink/flink-sql-connector-oracle-cdc/3.6.0-1.20/flink-sql-connector-oracle-cdc-3.6.0-1.20.jar

下载完驱动后需要需要重启下集群
/opt/flink-1.20.5/bin/start-cluster.sh
/opt/flink-1.20.5/bin/stop-cluster.sh

 

2.准备同步配置文件
vi /opt/flink-1.20.5/flinksql/oracle2mysql.sql
内容如下:

SET 'pipeline.name' = 'oracle同步到mysql';
-- Oracle CDC源表 TB_TEST
CREATE TABLE oracle_source_tb_test (
    ID INT,
    NAME STRING,
    PRIMARY KEY (ID) NOT ENFORCED
) WITH (
    'connector' = 'oracle-cdc',
    'hostname' = '192.168.1.7',
    'port' = '1521',
    'username' = 'hxl',
    'password' = 'oracle',
    'database-name' = 'orcl',
    'schema-name' = 'HXL',
    'table-name' = 'TB_TEST',
    'scan.startup.mode' = 'initial',
    'scan.incremental.snapshot.chunk.size' = '4000'
);




CREATE TABLE oracle_source_tb_test01 (
    ID INT,
    NAME STRING,
    PRIMARY KEY (ID) NOT ENFORCED
) WITH (
    'connector' = 'oracle-cdc',
    'hostname' = '192.168.1.7',
    'port' = '1521',
    'username' = 'hxl',
    'password' = 'oracle',
    'database-name' = 'orcl',
    'schema-name' = 'HXL',
    'table-name' = 'TB_TEST01',
    'scan.startup.mode' = 'initial',
    'scan.incremental.snapshot.chunk.size' = '4000'
);


-- MySQL JDBC Sink 目标表 tb_test
CREATE TABLE mysql_sink_tb_test (
    ID INT,
    NAME STRING,
    PRIMARY KEY (ID) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://192.168.1.113:3306/db_admin?useSSL=false&serverTimezone=Asia/Shanghai',
    'table-name' = 'tb_test',
    'username' = 'hxl01',
    'password' = 'mysql',
    'driver' = 'com.mysql.cj.jdbc.Driver',
    'sink.buffer-flush.max-rows' = '1000',
    'sink.buffer-flush.interval' = '1s'
);


CREATE TABLE mysql_sink_tb_test01 (
    ID INT,
    NAME STRING,
    PRIMARY KEY (ID) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://192.168.1.113:3306/db_admin?useSSL=false&serverTimezone=Asia/Shanghai',
    'table-name' = 'tb_test01',
    'username' = 'hxl01',
    'password' = 'mysql',
    'driver' = 'com.mysql.cj.jdbc.Driver',
    'sink.buffer-flush.max-rows' = '1000',
    'sink.buffer-flush.interval' = '1s'
);


-- 同步语句：读取oracle变更写入mysql
INSERT INTO mysql_sink_tb_test
SELECT ID, NAME FROM oracle_source_tb_test;


INSERT INTO mysql_sink_tb_test01
SELECT ID, NAME FROM oracle_source_tb_test01;

 

这个同步多个表,每个表会启动一个job 每个job都会去做logminer 同步多表的话是比较消耗性能的

 

3.mysql端创建表
CREATE TABLE `tb_test` (
`id` int(11) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

4.oracle开启补充日志
connect / as sysdba
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

查看
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES YES

 

5.启用归档模式
需要重启数据库
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

6.启动同步任务
/opt/flink-1.20.5/bin/sql-client.sh -f /opt/flink-1.20.5/flinksql/oracle2mysql.sql