阿狗工具
FlinkSQL 14.5 CDC实现同步oracle11G 数据到mysql
2024-04-09 18:30:38 阅读数 2069
下载oracle
1.实现功能
oracle 11G ---> flink sql ----> mysql
2. 版本
| 组件 | 版本 |
|---|---|
| flink | flink-1.14.5-bin-scala_2.12.tgz |
| flink cdc | flink-sql-connector-oracle-cdc-2.2.1.jar |
| oracle | oracle 11G |
3. docker 安装oracle 11G
3.1 拉去 oracle 11G 镜像
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
3.2 查看镜像
[root@basenode ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
apache/apisix latest 8ba480e550e8 5 weeks ago 249MB
myjenkinsnew latest be1d616a2850 7 months ago 449MB
myspringbootdocker 1.6 3f4e689e54db 7 months ago 682MB
centosjava8 1.5 14fad190b338 8 months ago 801MB
nacos/nacos-server v2.0.4 ea54f31c46e4 8 months ago 1.07GB
jenkins/jenkins latest 55860ee0cd73 9 months ago 442MB
zookeeper 3.4.14 4b03fe5b3f64 17 months ago 260MB
kibana 7.12.0 7a6b1047dd48 19 months ago 1.05GB
elasticsearch 7.12.0 9337ed510a0c 19 months ago 830MB
redis 6.0.8 16ecd2772934 23 months ago 104MB
nacos/nacos-server 1.2.0 763941e566bb 2 years ago 732MB
registry.cn-hangzhou.aliyuncs.com/anoy/yapi latest 76d11bb7c386 2 years ago 170MB
centos/mysql-57-centos7 latest f83a2938370c 3 years ago 452MB
mobz/elasticsearch-head 5 b19a5c98e43b 5 years ago 824MB
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g latest 3fa112fd3642 6 years ago 6.85GB
3.3 创建容器
docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
3.4 查看镜像id
[root@basenode ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
efdac06ae182 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g "/bin/sh -c '/home/o…" About a minute ago Up About a minute 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp oracle11g
[root@basenode ~]#
3.5 进入镜像进行设置
docker exec -it efdac06ae182 bash
su root
# 输入密码：helowin
vi /etc/profile
# 在文件最后写上下面内容
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
# 保存后执行source /etc/profile 加载环境变量
3.6 创建软连接
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
3.7 切换oracle用户
su - oracle
# 密码 helowin
3.8 登录sqlplus
sqlplus /nolog
SQL> conn /as sysdba
3.9 连接oracle
密码是： helowin
连接oracle.png
4. 进入容器配置 oracle
参考资料 https://ververica.github.io/flink-cdc-connectors/release-2.2/content/connectors/oracle-cdc.html
docker exec -it efdac06ae182 bash
# 切换到oracle用户
su - oracle
# 创建数据需要的目录,需要提前创建，否则报错目录不存在
mkdir /home/oracle/oracle-data-test
sqlplus /nolog
# 以 DBA 身份连接到数据库
SQL> conn /as sysdba
4.1 进行配置相关的参数
-- 启用日志归档
alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest = '/home/oracle/oracle-data-test' scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
-- 检查日志归档是否开启
archive log list;
-- 为捕获的数据库启用补充日志记录,以便数据更改捕获更改的数据库行之前的状态,下面说明了如何在数据库级别进行配置。
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- 创建表空间
CREATE TABLESPACE logminer_tbs DATAFILE '/home/oracle/logminer_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
-- 创建用户family绑定表空间LOGMINER_TBS
CREATE USER family IDENTIFIED BY zyhcdc DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS;
-- 授予family用户dba的权限
grant connect,resource,dba to family;
-- 并授予权限
GRANT CREATE SESSION TO family;
GRANT SELECT ON V_$DATABASE to family;
GRANT FLASHBACK ANY TABLE TO family;
GRANT SELECT ANY TABLE TO family;
GRANT SELECT_CATALOG_ROLE TO family;
GRANT EXECUTE_CATALOG_ROLE TO family;
GRANT SELECT ANY TRANSACTION TO family;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO family;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO family;
GRANT CREATE TABLE TO family;
GRANT LOCK ANY TABLE TO family;
GRANT ALTER ANY TABLE TO family;
GRANT CREATE SEQUENCE TO family;
GRANT EXECUTE ON DBMS_LOGMNR TO family;
GRANT EXECUTE ON DBMS_LOGMNR_D TO family;
GRANT SELECT ON V_$LOG TO family;
GRANT SELECT ON V_$LOG_HISTORY TO family;
GRANT SELECT ON V_$LOGMNR_LOGS TO family;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO family;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO family;
GRANT SELECT ON V_$LOGFILE TO family;
GRANT SELECT ON V_$ARCHIVED_LOG TO family;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO family;
4.1.1 出现一个错误 需要重启启动 startup
[root@efdac06ae182 /]# su - oracle
[oracle@efdac06ae182 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 12 23:39:21 2022
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest_size = 10G
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 402655344 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.
SQL>
4.2Navcat连接oracle:
flinkoracle 配置连接.png
5.flink jar 包
5.1 对应下载flink cdc 包
https://ververica.github.io/flink-cdc-connectors/release-2.2/content/connectors/oracle-cdc.html
5.2 查看flink 包
[root@basenode lib]# ll
总用量 526884
-rw-r--r--. 1 root root 53820 10月 12 23:46 commons-cli-1.4.jar
-rw-r--r--. 1 502 games 85586 10月 12 23:46 flink-csv-1.14.5.jar
-rw-r--r--. 1 502 games 136098285 10月 12 23:47 flink-dist_2.12-1.14.5.jar
-rw-r--r--. 1 root root 7811484 10月 12 23:46 flink-doris-connector-1.14_2.12-1.0.3.jar
-rw-r--r--. 1 502 games 153142 10月 12 23:46 flink-json-1.14.5.jar
-rw-r--r--. 1 root root 59604787 10月 12 23:46 flink-shaded-hadoop-3-uber-3.1.1.7.2.9.0-173-9.0.jar
-rw-r--r--. 1 502 games 7709731 10月 12 23:46 flink-shaded-zookeeper-3.4.14.jar
-rw-r--r--. 1 root root 48743942 10月 12 23:47 flink-sql-connector-hive-3.1.2_2.12-1.14.5.jar
-rw-r--r--. 1 root root 3704679 10月 12 23:46 flink-sql-connector-kafka_2.12-1.14.5.jar
-rw-r--r--. 1 root root 22096298 10月 12 23:46 flink-sql-connector-mysql-cdc-2.2.1.jar
-rw-r--r--. 1 root root 25431450 10月 12 23:52 flink-sql-connector-oracle-cdc-2.2.1.jar
-rw-r--r--. 1 root root 5593716 10月 12 23:46 flink-sql-parquet_2.12-1.14.5.jar
-rw-r--r--. 1 root root 242013 10月 12 23:46 flink-statebackend-rocksdb_2.12-1.14.5.jar
-rw-r--r--. 1 502 games 39666418 10月 12 23:46 flink-table_2.12-1.14.5.jar
-rw-r--r--. 1 root root 40623961 10月 12 23:46 hive-exec-3.1.2.jar
-rw-r--r--. 1 root root 51706756 10月 12 23:46 hudi-flink1.14-bundle_2.12-0.11.1.jar
-rw-r--r--. 1 root root 40525420 10月 12 23:46 hudi-hadoop-mr-bundle-0.11.1.jar
-rw-r--r--. 1 root root 35202334 10月 12 23:46 hudi-hive-sync-bundle-0.11.1.jar
-rw-r--r--. 1 root root 7220435 10月 12 23:46 jvm-profiler-1.0.0.jar
-rw-r--r--. 1 root root 4614888 10月 12 23:46 kafka-clients-2.8.1.jar
-rw-r--r--. 1 502 games 208006 10月 12 23:46 log4j-1.2-api-2.17.1.jar
-rw-r--r--. 1 502 games 301872 10月 12 23:46 log4j-api-2.17.1.jar
-rw-r--r--. 1 502 games 1790452 10月 12 23:46 log4j-core-2.17.1.jar
-rw-r--r--. 1 502 games 24279 10月 12 23:46 log4j-slf4j-impl-2.17.1.jar
[root@basenode lib]#
5.2 启动flink 执行sql
Flink SQL> SET sql-client.execution.result-mode = tableau;
[INFO] Session property has been set.
Flink SQL> CREATE TABLE student_info(
> SID INT NOT NULL,
> SNAME STRING,
> SEX STRING,
> PRIMARY KEY(SID) NOT ENFORCED
> ) WITH (
> 'connector' = 'oracle-cdc',
> 'hostname' = '192.168.1.180',
> 'port' = '1521',
> 'username' = 'family',
> 'password' = 'zyhcdc',
> 'database-name' = 'helowin',
> 'schema-name' = 'FAMILY',
> 'table-name' = 'STUDENT_INFO',
> 'debezium.log.mining.continuous.mine'='true',
> 'debezium.log.mining.strategy'='online_catalog',
> 'debezium.database.tablename.case.insensitive'='false',
> 'scan.startup.mode' = 'initial');
[INFO] Execute statement succeed.
Flink SQL> select * from student_info;
+----+-------------+--------------------------------+--------------------------------+
| op | SID | SNAME | SEX |
+----+-------------+--------------------------------+--------------------------------+
| +I | 1 | flink | nv |
| +I | 2 | flinksql | na |
6. 查看结果
flink-oracle -cdc 执行效果.png