?搜索
flink cdc多種數(shù)據(jù)源安裝、配置與驗(yàn)證
文章目錄
- 1. 前言
- 2. 數(shù)據(jù)源安裝與配置
- 2.1 MySQL
- 2.1.1 安裝
- 2.1.2 CDC 配置
- 2.2 Postgresql
- 2.2.1 安裝
- 2.2.2 CDC 配置
- 2.3 Oracle
- 2.3.1 安裝
- 2.3.2 CDC 配置
- 2.4 SQLServer
- 2.4.1 安裝
- 2.4.2 CDC 配置
- 3. 驗(yàn)證
- 3.1 Flink版本與CDC版本的對(duì)應(yīng)關(guān)系
- 3.2 下載相關(guān)包
- 3.3 添加cdc jar 至lib目錄
- 3.4 驗(yàn)證
本文目錄結(jié)構(gòu)：
|___ 1. 前言
|___ 2. 數(shù)據(jù)源安裝與配置
|______ 2.1 MySQL
|_________ 2.1.1 安裝
|_________ 2.1.2 CDC 配置
|______ 2.2 Postgresql
|_________ 2.2.1 安裝
|_________ 2.2.2 CDC 配置
|______ 2.3 Oracle
|_________2.3.1 安裝
|_________2.3.2 CDC 配置
|_______2.4 SQLServer
|_________2.4.1 安裝
|_________2.4.2 CDC 配置
|___ 3. 驗(yàn)證
|_______3.1 Flink版本與CDC版本的對(duì)應(yīng)關(guān)系
|_______3.2 下載相關(guān)包
|_______3.3 添加cdc jar 至lib目錄
|_______3.4 驗(yàn)證
1. 前言
關(guān)于如何使用和配置flink cdc功能，其實(shí)在官方文檔（CDC Connectors for Apache Flink? — CDC Connectors for Apache Flink? documentation）有相關(guān)的教程了，如下：
但是講解的不是很詳細(xì)，比如數(shù)據(jù)源怎么安裝？怎么配置？都沒(méi)有很詳細(xì)的描述每一步驟，因此博主前面發(fā)布多篇文章以此來(lái)記錄flink cdc相關(guān)數(shù)據(jù)源以及其配置相關(guān)的文章，有興趣的同學(xué)可以參考下：
- 《docker下安裝oracle11g（一次安裝成功）》
- 《Docker下安裝SqlServer2019》
- 《flink postgresql cdc實(shí)時(shí)同步（含pg安裝配置等）》
- 《flink oracle cdc實(shí)時(shí)同步（超詳細(xì)）》
- 《flink sqlserver cdc實(shí)時(shí)同步（含sqlserver安裝配置等）》
本文主要就是記錄在docker下安裝和配置各種數(shù)據(jù)源，以實(shí)現(xiàn)flink cdc的功能，包含如下常見(jiàn)的數(shù)據(jù)源：
| 數(shù)據(jù)源 | 版本 |
|---|---|
| MySQL | 8.0.25 |
| Postgresql | 10.6 |
| Oracle | 11g |
| SqlServer | 2019 |
2. 數(shù)據(jù)源安裝與配置
2.1 MySQL
版本：8.0.25
2.1.1 安裝
Step1: 拉取mysql鏡像：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull mysql:8.0.25
</code></span></span></span></span>Step2: 創(chuàng)建并運(yùn)行 MySQL 容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-d</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30025</span>:3306 <span style="color:#ee9900">--name</span> mysql8.0.25 <span style="color:#ee9900">-e</span> <span style="color:#ee9900">MYSQL_ROOT_PASSWORD</span><span style="color:#6272a4">=</span>root mysql:8.0.25
</code></span></span></span></span>- 1
2.1.2?CDC?配置
Step1：進(jìn)入正在運(yùn)行的mysql容器：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> mysql8.0.25 mysql <span style="color:#ee9900">-uroot</span> <span style="color:#ee9900">-proot</span>
</code></span></span></span></span>- 1
Step2：配置 CDC
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 啟用二進(jìn)制日志</span>
mysql<span style="color:#6272a4">></span> <span style="color:#ff79c6">SET</span> <span style="color:#ff79c6">GLOBAL</span> log_bin <span style="color:#6272a4">=</span> <span style="color:#ff79c6">ON</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 設(shè)置二進(jìn)制日志格式為行級(jí)別</span>
mysql<span style="color:#6272a4">></span> <span style="color:#ff79c6">SET</span> <span style="color:#ff79c6">GLOBAL</span> binlog_format <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'ROW'</span><span style="color:#999999">;</span>
</code></span></span></span></span>Step3（非必要）：如果配置沒(méi)生效，重啟容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> restart mysql8.0.25
</code></span></span></span></span>- 1
2.2 Postgresql
版本：PostgreSQL 10.6 (Debian 10.6-1.pgdg90+1)
2.2.1 安裝
Step1： 拉取 PostgreSQL 10.6 版本的鏡像：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull postgres:10.6
</code></span></span></span></span>- 1
Step2：創(chuàng)建并啟動(dòng)?PostgreSQL?容器，在這里，我們將把容器的端口 5432 映射到主機(jī)的端口 30028，賬號(hào)密碼設(shè)置為postgres，并將?pgoutput?插件加載到?PostgreSQL?實(shí)例中：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-d</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30028</span>:5432 <span style="color:#ee9900">--name</span> postgres-10.6 <span style="color:#ee9900">-e</span> <span style="color:#ee9900">POSTGRES_PASSWORD</span><span style="color:#6272a4">=</span>postgres postgres:10.6 <span style="color:#ee9900">-c</span> <span style="color:#f1fa8c">'shared_preload_libraries=pgoutput'</span>
</code></span></span></span></span>- 1
Step3： 查看容器是否創(chuàng)建成功：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#8be9fd">ps</span> <span style="color:#6272a4">|</span> <span style="color:#8be9fd">grep</span> postgres-10.6
</code></span></span></span></span>- 1
2.2.2 CDC 配置
Step1：docker進(jìn)去Postgresql數(shù)據(jù)的容器：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> postgres-10.6 <span style="color:#8be9fd">bash</span>
</code></span></span></span></span>- 1
Step2：編輯postgresql.conf配置文件：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">vi</span> /var/lib/postgresql/data/postgresql.conf
</code></span></span></span></span>- 1
配置內(nèi)容如下：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-py"><span style="color:#6272a4"># 更改wal日志方式為logical（方式有：minimal、replica 、logical ）</span>
wal_level <span style="color:#6272a4">=</span> logical
<span style="color:#6272a4"># 更改solts最大數(shù)量（默認(rèn)值為10），flink-cdc默認(rèn)一張表占用一個(gè)slots</span>
max_replication_slots <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">20</span>
<span style="color:#6272a4"># 更改wal發(fā)送最大進(jìn)程數(shù)（默認(rèn)值為10），這個(gè)值和上面的solts設(shè)置一樣</span>
max_wal_senders <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">20</span>
<span style="color:#6272a4"># 中斷那些停止活動(dòng)超過(guò)指定毫秒數(shù)的復(fù)制連接，可以適當(dāng)設(shè)置大一點(diǎn)（默認(rèn)60s，0表示禁用）</span>
wal_sender_timeout <span style="color:#6272a4">=</span> 180s
</code></span></span></span></span>Step3：重啟容器：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> restart postgres-10.6
</code></span></span></span></span>- 1
連接數(shù)據(jù)庫(kù)，如果查詢一下語(yǔ)句，返回logical表示修改成功：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#ff79c6">SHOW</span> wal_level<span style="color:#999999">;</span>
</code></span></span></span></span>- 1
Step4：新建用戶并賦權(quán)。使用創(chuàng)建容器時(shí)的賬號(hào)密碼（postgres/postgres）登錄Postgresql數(shù)據(jù)庫(kù)。
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建數(shù)據(jù)庫(kù) test_db</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">DATABASE</span> test_db<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 連接到新創(chuàng)建的數(shù)據(jù)庫(kù) test_db</span>
\c test_db
<span style="color:#6272a4">-- 創(chuàng)建 t_user 表</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#f1fa8c">"public"</span><span style="color:#999999">.</span><span style="color:#f1fa8c">"t_user"</span> <span style="color:#999999">(</span>
<span style="color:#f1fa8c">"id"</span> int8 <span style="color:#6272a4">NOT</span> <span style="color:#8be9fd">NULL</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">"name"</span> <span style="color:#ff79c6">varchar</span><span style="color:#999999">(</span><span style="color:#f1fa8c">255</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">"age"</span> int2<span style="color:#999999">,</span>
<span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span> <span style="color:#999999">(</span><span style="color:#f1fa8c">"id"</span><span style="color:#999999">)</span>
<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- pg新建用戶</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">USER</span> test1 <span style="color:#ff79c6">WITH</span> PASSWORD <span style="color:#f1fa8c">'test123'</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 給用戶復(fù)制流權(quán)限</span>
<span style="color:#ff79c6">ALTER</span> ROLE test1 <span style="color:#ff79c6">replication</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 給用戶登錄數(shù)據(jù)庫(kù)權(quán)限</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CONNECT</span> <span style="color:#ff79c6">ON</span> <span style="color:#ff79c6">DATABASE</span> test_db <span style="color:#ff79c6">to</span> test1<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 把當(dāng)前庫(kù)public下所有表查詢權(quán)限賦給用戶</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">ALL</span> <span style="color:#ff79c6">PRIVILEGES</span> <span style="color:#ff79c6">ON</span> <span style="color:#ff79c6">ALL</span> <span style="color:#ff79c6">TABLES</span> <span style="color:#6272a4">IN</span> <span style="color:#ff79c6">SCHEMA</span> <span style="color:#ff79c6">public</span> <span style="color:#ff79c6">TO</span> test1<span style="color:#999999">;</span>
</code></span></span></span></span>Step4：發(fā)布表：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 設(shè)置發(fā)布為true</span>
<span style="color:#ff79c6">update</span> pg_publication <span style="color:#ff79c6">set</span> puballtables<span style="color:#6272a4">=</span><span style="color:#8be9fd">true</span> <span style="color:#ff79c6">where</span> pubname <span style="color:#6272a4">is</span> <span style="color:#6272a4">not</span> <span style="color:#8be9fd">null</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 把所有表進(jìn)行發(fā)布</span>
<span style="color:#ff79c6">CREATE</span> PUBLICATION dbz_publication <span style="color:#ff79c6">FOR</span> <span style="color:#ff79c6">ALL</span> <span style="color:#ff79c6">TABLES</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 查詢哪些表已經(jīng)發(fā)布</span>
<span style="color:#ff79c6">select</span> <span style="color:#6272a4">*</span> <span style="color:#ff79c6">from</span> pg_publication_tables<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 更改復(fù)制標(biāo)識(shí)包含更新和刪除之前值（目的是為了確保表 t_user 在實(shí)時(shí)同步過(guò)程中能夠正確地捕獲并同步更新和刪除的數(shù)據(jù)變化。如果不執(zhí)行這兩條語(yǔ)句，那么 t_user 表的復(fù)制標(biāo)識(shí)可能默認(rèn)為 NOTHING，這可能導(dǎo)致實(shí)時(shí)同步時(shí)丟失更新和刪除的數(shù)據(jù)行信息，從而影響同步的準(zhǔn)確性）</span>
<span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">TABLE</span> t_user REPLICA <span style="color:#ff79c6">IDENTITY</span> <span style="color:#ff79c6">FULL</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 查看復(fù)制標(biāo)識(shí)（為f標(biāo)識(shí)說(shuō)明設(shè)置成功，f（表示 full），否則為 n（表示 nothing），即復(fù)制標(biāo)識(shí)未設(shè)置）</span>
<span style="color:#ff79c6">select</span> relreplident <span style="color:#ff79c6">from</span> pg_class <span style="color:#ff79c6">where</span> relname<span style="color:#6272a4">=</span><span style="color:#f1fa8c">'t_user'</span><span style="color:#999999">;</span>
</code></span></span></span></span>2.3 Oracle
版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2.3.1 安裝
Step1：拉取 oracle 11g 鏡像（有6g，要等較長(zhǎng)的時(shí)間）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
</code></span></span></span></span>- 1
Step2：執(zhí)行以下命令以創(chuàng)建并運(yùn)行 Oracle 11g 容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-d</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30026</span>:1521 <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">8081</span>:8080 <span style="color:#999999">\</span>
<span style="color:#ee9900">--name</span> oracle_11g <span style="color:#999999">\</span>
<span style="color:#ee9900">-e</span> <span style="color:#ee9900">ORACLE_HOME</span><span style="color:#6272a4">=</span>/home/oracle/app/oracle/product/11.2.0/dbhome_2 <span style="color:#999999">\</span>
<span style="color:#ee9900">-e</span> <span style="color:#ee9900">ORACLE_SID</span><span style="color:#6272a4">=</span>helowin <span style="color:#999999">\</span>
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
</code></span></span></span></span>Step3：查看容器是否啟動(dòng)
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#8be9fd">ps</span> -a<span style="color:#6272a4">|</span><span style="color:#8be9fd">grep</span> oracle_11g
</code></span></span></span></span>Step4：進(jìn)入容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> oracle_11g <span style="color:#8be9fd">bash</span>
</code></span></span></span></span>**Step5：**設(shè)置賬號(hào)密碼
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#6272a4"># 1. 切換至root用戶(默認(rèn)是oracle用戶)，密碼為helowin</span>
<span style="color:#8be9fd">su</span> root
<span style="color:#6272a4"># 2. 創(chuàng)建軟鏈接</span>
<span style="color:#8be9fd">ln</span> <span style="color:#ee9900">-s</span> <span style="color:#ee9900">$ORACLE_HOME</span>/bin/sqlplus /usr/bin
<span style="color:#6272a4"># 3.切換回oracle用戶</span>
<span style="color:#8be9fd">su</span> oracle
<span style="color:#6272a4"># 4. 登錄sql plus</span>
sqlplus /nolog
conn /as sysdba
<span style="color:#6272a4">## 4.1 修改system用戶密碼為system</span>
alter user system identified by system<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.2 修改sys用戶密碼為system</span>
alter user sys identified by system<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.3 新增一個(gè)測(cè)試用戶（用戶名：test，密碼：test123）;</span>
create user <span style="color:#f1fa8c">test</span> identified by test123<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.4 將dba權(quán)限給內(nèi)部管理員賬號(hào)和密碼</span>
grant connect,resource,dba to <span style="color:#f1fa8c">test</span><span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.5 修改密碼策略規(guī)則為：密碼永不過(guò)期</span>
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.6 修改數(shù)據(jù)庫(kù)最大連接數(shù)；</span>
alter system <span style="color:#f1fa8c">set</span> <span style="color:#ee9900">processes</span><span style="color:#6272a4">=</span><span style="color:#f1fa8c">1000</span> <span style="color:#ee9900">scope</span><span style="color:#6272a4">=</span>spfile<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.7 最后重啟數(shù)據(jù)庫(kù)；</span>
<span style="color:#8be9fd">shutdown</span> immediate<span style="color:#999999">;</span>
startup<span style="color:#999999">;</span>
<span style="color:#6272a4"># 5.退出</span>
<span style="color:#f1fa8c">exit</span>
</code></span></span></span></span>2.3.2 CDC 配置
Step1：進(jìn)入容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> oracle_11g <span style="color:#8be9fd">bash</span>
</code></span></span></span></span>Step2：以DBA的權(quán)限登錄數(shù)據(jù)庫(kù)
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> sys<span style="color:#6272a4">/</span>system <span style="color:#ff79c6">AS</span> SYSDBA
</code></span></span></span></span>Step3：?jiǎn)⒂萌罩練w檔
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 設(shè)置數(shù)據(jù)庫(kù)恢復(fù)文件目標(biāo)大小為10G</span>
<span style="color:#ff79c6">alter</span> system <span style="color:#ff79c6">set</span> db_recovery_file_dest_size <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">10</span>G<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 設(shè)置數(shù)據(jù)庫(kù)恢復(fù)文件目標(biāo)路徑</span>
<span style="color:#ff79c6">alter</span> system <span style="color:#ff79c6">set</span> db_recovery_file_dest <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'/home/oracle/app/oracle/product/11.2.0'</span> scope<span style="color:#6272a4">=</span>spfile<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 立即關(guān)閉數(shù)據(jù)庫(kù)</span>
<span style="color:#ff79c6">shutdown</span> immediate<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 以mount模式啟動(dòng)數(shù)據(jù)庫(kù)</span>
startup mount<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 啟用數(shù)據(jù)庫(kù)歸檔日志模式</span>
<span style="color:#ff79c6">alter</span> <span style="color:#ff79c6">database</span> archivelog<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 打開(kāi)數(shù)據(jù)庫(kù)，允許用戶訪問(wèn)</span>
<span style="color:#ff79c6">alter</span> <span style="color:#ff79c6">database</span> <span style="color:#ff79c6">open</span><span style="color:#999999">;</span>
</code></span></span></span></span>Step4：查看日志歸檔是否啟用（如果顯示“Archive Mode”表示已經(jīng)啟用）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">archive log list<span style="color:#999999">;</span>
</code></span></span></span></span>- 1
Step5：創(chuàng)建表空間
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 以DBA的權(quán)限登錄數(shù)據(jù)庫(kù)</span>
sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> sys<span style="color:#6272a4">/</span>system <span style="color:#ff79c6">AS</span> SYSDBA
<span style="color:#6272a4">-- 創(chuàng)建一個(gè)名為"logminer_tbs"的表空間</span>
<span style="color:#6272a4">-- 指定表空間的數(shù)據(jù)文件路徑為"/home/oracle/app/oracle/product/11.2.0/logminer_tbs.dbf"，其中"/home/oracle/app/oracle/product/11.2.0"是數(shù)據(jù)文件存儲(chǔ)的目錄，"logminer_tbs.dbf"是數(shù)據(jù)文件的文件名</span>
<span style="color:#6272a4">-- 設(shè)置表空間的初始大小為25MB</span>
<span style="color:#6272a4">-- 如果數(shù)據(jù)文件已經(jīng)存在且可重用，將其重用，否則創(chuàng)建一個(gè)新的數(shù)據(jù)文件</span>
<span style="color:#6272a4">-- 啟用表空間的自動(dòng)擴(kuò)展功能，即當(dāng)表空間空間不足時(shí)，自動(dòng)增加數(shù)據(jù)文件的大小</span>
<span style="color:#6272a4">-- 設(shè)置表空間的最大允許大小為無(wú)限，即表空間可以無(wú)限制地自動(dòng)擴(kuò)展</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLESPACE</span> logminer_tbs DATAFILE <span style="color:#f1fa8c">'/home/oracle/app/oracle/product/11.2.0/logminer_tbs.dbf'</span> SIZE <span style="color:#f1fa8c">25</span>M REUSE AUTOEXTEND <span style="color:#ff79c6">ON</span> MAXSIZE UNLIMITED<span style="color:#999999">;</span>
</code></span></span></span></span>Step6：創(chuàng)建用戶并賦權(quán)
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建一個(gè)名為"flinkuser"的用戶，密碼為"flinkpw"，將其默認(rèn)表空間設(shè)置為"LOGMINER_TBS"，并在該表空間上設(shè)置無(wú)限配額。</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">USER</span> flinkuser IDENTIFIED <span style="color:#ff79c6">BY</span> flinkpw <span style="color:#ff79c6">DEFAULT</span> <span style="color:#ff79c6">TABLESPACE</span> LOGMINER_TBS QUOTA UNLIMITED <span style="color:#ff79c6">ON</span> LOGMINER_TBS<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶創(chuàng)建會(huì)話，即允許該用戶連接到數(shù)據(jù)庫(kù)。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">SESSION</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- （不支持Oracle 11g）允許"flinkuser"用戶在多租戶數(shù)據(jù)庫(kù)（CDB）中設(shè)置容器。</span>
<span style="color:#6272a4">-- GRANT SET CONTAINER TO flinkuser;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$DATABASE視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)實(shí)例的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$<span style="color:#ff79c6">DATABASE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶執(zhí)行任何表的閃回操作。</span>
<span style="color:#ff79c6">GRANT</span> FLASHBACK <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢?nèi)魏伪淼臄?shù)據(jù)。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶擁有SELECT_CATALOG_ROLE角色，該角色允許查詢數(shù)據(jù)字典和元數(shù)據(jù)。</span>
<span style="color:#ff79c6">GRANT</span> SELECT_CATALOG_ROLE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶擁有EXECUTE_CATALOG_ROLE角色，該角色允許執(zhí)行一些數(shù)據(jù)字典中的過(guò)程和函數(shù)。</span>
<span style="color:#ff79c6">GRANT</span> EXECUTE_CATALOG_ROLE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢?nèi)魏问聞?wù)。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TRANSACTION</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- （不支持Oracle 11g）允許"flinkuser"用戶進(jìn)行數(shù)據(jù)變更追蹤（LogMiner）。</span>
<span style="color:#6272a4">-- GRANT LOGMINING TO flinkuser;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶創(chuàng)建表。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶鎖定任何表。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">LOCK</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶修改任何表。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶創(chuàng)建序列。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CREATE</span> SEQUENCE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶執(zhí)行DBMS_LOGMNR包中的過(guò)程。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">EXECUTE</span> <span style="color:#ff79c6">ON</span> DBMS_LOGMNR <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶執(zhí)行DBMS_LOGMNR_D包中的過(guò)程。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">EXECUTE</span> <span style="color:#ff79c6">ON</span> DBMS_LOGMNR_D <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOG視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOG <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOG_HISTORY視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)歷史日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOG_HISTORY <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGMNR_LOGS視圖，該視圖包含有關(guān)LogMiner日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGMNR_LOGS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGMNR_CONTENTS視圖，該視圖包含LogMiner日志文件的內(nèi)容。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGMNR_CONTENTS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGMNR_PARAMETERS視圖，該視圖包含有關(guān)LogMiner的參數(shù)信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGMNR_PARAMETERS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGFILE視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGFILE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$ARCHIVED_LOG視圖，該視圖包含已歸檔的數(shù)據(jù)庫(kù)日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$ARCHIVED_LOG <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$ARCHIVE_DEST_STATUS視圖，該視圖包含有關(guān)歸檔目標(biāo)狀態(tài)的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$ARCHIVE_DEST_STATUS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
</code></span></span></span></span>Step7：數(shù)據(jù)庫(kù)和表啟用增量日志
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 切換至flinkuser用戶</span>
sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> flinkuser<span style="color:#6272a4">/</span>flinkpw
<span style="color:#6272a4">-- 創(chuàng)建customers表</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> customers <span style="color:#999999">(</span>
customer_id NUMBER <span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span><span style="color:#999999">,</span>
customer_name VARCHAR2<span style="color:#999999">(</span><span style="color:#f1fa8c">50</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
email VARCHAR2<span style="color:#999999">(</span><span style="color:#f1fa8c">100</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
phone VARCHAR2<span style="color:#999999">(</span><span style="color:#f1fa8c">20</span><span style="color:#999999">)</span>
<span style="color:#999999">)</span> <span style="color:#ff79c6">TABLESPACE</span> LOGMINER_TBS<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 查看LOGMINER_TBS表空間下的所有表</span>
<span style="color:#ff79c6">select</span> tablespace_name<span style="color:#999999">,</span> table_name <span style="color:#ff79c6">from</span> user_tables
<span style="color:#ff79c6">where</span> tablespace_name <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'LOGMINER_TBS'</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 以DBA的權(quán)限登錄數(shù)據(jù)庫(kù)</span>
sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> sys<span style="color:#6272a4">/</span>system <span style="color:#ff79c6">AS</span> SYSDBA
<span style="color:#6272a4">-- 為L(zhǎng)OGMINER_TBS表空間下的customers表啟用增強(qiáng)日志記錄</span>
<span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">TABLE</span> FLINKUSER<span style="color:#999999">.</span>CUSTOMERS <span style="color:#ff79c6">ADD</span> SUPPLEMENTAL LOG <span style="color:#ff79c6">DATA</span> <span style="color:#999999">(</span><span style="color:#ff79c6">ALL</span><span style="color:#999999">)</span> <span style="color:#ff79c6">COLUMNS</span>
<span style="color:#6272a4">-- 為數(shù)據(jù)庫(kù)啟用增強(qiáng)日志記錄：</span>
<span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">DATABASE</span> <span style="color:#ff79c6">ADD</span> SUPPLEMENTAL LOG <span style="color:#ff79c6">DATA</span><span style="color:#999999">;</span>
</code></span></span></span></span>2.4 SQLServer
版本：Microsoft SQL Server 2019 (RTM-CU21) (KB5025808) - 15.0.4316.3 (X64)
2.4.1 安裝
Step1：拉取SQL Server 2019 鏡像
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull mcr.microsoft.com/mssql/server:2019-latest
</code></span></span></span></span>Step2：運(yùn)行 SQL Server 容器（密碼必須是8個(gè)字符，并包含字母、數(shù)字和特殊字符，如：abc@123456 ，下面映射主機(jī)端口為30027）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-e</span> <span style="color:#f1fa8c">'ACCEPT_EULA=Y'</span> <span style="color:#ee9900">-e</span> <span style="color:#f1fa8c">'SA_PASSWORD=abc@123456'</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30027</span>:1433 <span style="color:#ee9900">--name</span> sql_server_2019 <span style="color:#ee9900">-d</span> mcr.microsoft.com/mssql/server:2019-latest
</code></span></span></span></span>Step3：驗(yàn)證 SQL Server 容器是否正在運(yùn)行
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code>docker ps -a|grep sql_server_2019
</code></span></span></span></span>2.4.2 CDC 配置
Step1：開(kāi)啟SQLServer代理
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#6272a4">## 使用root用戶登錄容器</span>
<span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> <span style="color:#ee9900">--user</span> root sql_server_2019 <span style="color:#8be9fd">bash</span>
<span style="color:#6272a4">## 進(jìn)入容器后，執(zhí)行命令啟用Agent</span>
/opt/mssql/bin/mssql-conf <span style="color:#f1fa8c">set</span> sqlagent.enabled <span style="color:#8be9fd">true</span>
<span style="color:#6272a4">## 退出，重啟容器</span>
<span style="color:#f1fa8c">exit</span>
<span style="color:#8be9fd">docker</span> restart sql_server_2019
</code></span></span></span></span>Step2：創(chuàng)建’cdc_test’測(cè)試數(shù)據(jù)庫(kù)，并使用連接工具登錄該數(shù)據(jù)庫(kù)，使用以下 SQL 命令啟用 CDC 功能
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建數(shù)據(jù)庫(kù)</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">DATABASE</span> cdc_test<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 啟用CDC功能</span>
<span style="color:#ff79c6">EXEC</span> sys<span style="color:#999999">.</span>sp_cdc_enable_db<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 判斷當(dāng)前數(shù)據(jù)庫(kù)是否啟用了CDC（如果返回1，表示已啟用）</span>
<span style="color:#ff79c6">SELECT</span> is_cdc_enabled <span style="color:#ff79c6">FROM</span> sys<span style="color:#999999">.</span><span style="color:#ff79c6">databases</span> <span style="color:#ff79c6">WHERE</span> name <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'cdc_test'</span><span style="color:#999999">;</span>
</code></span></span></span></span>Step3：選擇要進(jìn)行 CDC 跟蹤的表（這里使用orders表作為演示）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建示例表（orders）</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> orders <span style="color:#999999">(</span>
id <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
order_date <span style="color:#ff79c6">date</span><span style="color:#999999">,</span>
purchaser <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
quantity <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
product_id <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
<span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span> <span style="color:#999999">(</span><span style="color:#999999">[</span>id<span style="color:#999999">]</span><span style="color:#999999">)</span>
<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- schema_name 是表所屬的架構(gòu)（schema）的名稱。</span>
<span style="color:#6272a4">-- table_name 是要啟用 CDC 跟蹤的表的名稱。</span>
<span style="color:#6272a4">-- cdc_role 是 CDC 使用的角色的名稱。如果沒(méi)有指定角色名稱，系統(tǒng)將創(chuàng)建一個(gè)默認(rèn)角色。</span>
<span style="color:#ff79c6">EXEC</span> sys<span style="color:#999999">.</span>sp_cdc_enable_table
<span style="color:#ee9900">@source_schema</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'dbo'</span><span style="color:#999999">,</span>
<span style="color:#ee9900">@source_name</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'orders'</span><span style="color:#999999">,</span>
<span style="color:#ee9900">@role_name</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'cdc_role'</span><span style="color:#999999">;</span>
</code></span></span></span></span>3. 驗(yàn)證
如果要驗(yàn)證flink cdc的功能，需要先下載flink的安裝包，然后下載相應(yīng)的cdc jar包并依賴，最后使用安裝包里面的sql-client寫相關(guān)的flink sql即可驗(yàn)證。
3.1 Flink版本與CDC版本的對(duì)應(yīng)關(guān)系
下載Flink安裝包以及jar包前，必須確定Flink CDC與Flink版本關(guān)系：
| Flink CDC 版本 | Flink 版本 |
|---|---|
| 1.0.0 | 1.11.* |
| 1.1.0 | 1.11.* |
| 1.2.0 | 1.12.* |
| 1.3.0 | 1.12.* |
| 1.4.0 | 1.13.* |
| 2.0.* | 1.13.* |
| 2.1.* | 1.13.* |
| 2.2.* | 1.13.*,?1.14.* |
| 2.3.* | 1.13.*,?1.14.*,?1.15.*,?1.16.0 |
| 2.4.* | 1.13.*,?1.14.*,?1.15.*,?1.16.*,?1.17.0 |
本文以?Flink1.13.6?+?Flink CDC 2.2.0?版本為例子演示。
3.2 下載相關(guān)包
flink 安裝包下載，下載地址：Downloads | Apache Flink
下載cdc相關(guān)的jar，根據(jù)自己的需求，下載相關(guān)的cdc jar：Central Repository: com/ververica
3.3 添加cdc jar 至lib目錄
把需要驗(yàn)證的cdc jar放到flink安裝包解壓之后的lib目錄（<FLINK_HOME>/lib/）:
3.4 驗(yàn)證
使用下面的命令啟動(dòng) Flink 集群：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell">./bin/start-cluster.sh
</code></span></span></span></span>啟動(dòng)成功，可以訪問(wèn)?http://localhost:8081?訪問(wèn)到 Flink Web UI：
使用下面的命令啟動(dòng) Flink SQL CLI ：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell">./bin/sql-client.sh
</code></span></span></span></span>展示如下頁(yè)面，表示啟動(dòng)flink客戶端成功：
執(zhí)行如下FlinkSQL：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> t_source_sqlserver <span style="color:#999999">(</span>
id <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
order_date <span style="color:#ff79c6">DATE</span><span style="color:#999999">,</span>
purchaser <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
quantity <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
product_id <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
<span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span> <span style="color:#999999">(</span>id<span style="color:#999999">)</span> <span style="color:#6272a4">NOT</span> ENFORCED
<span style="color:#999999">)</span> <span style="color:#ff79c6">WITH</span> <span style="color:#999999">(</span>
<span style="color:#f1fa8c">'connector'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'sqlserver-cdc'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'hostname'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'10.194.183.120'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'port'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'30027'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'username'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'sa'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'password'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'abc@123456'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'database-name'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'cdc_test'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'schema-name'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'dbo'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'table-name'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'orders'</span>
<span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span></span></span></span>可以看到執(zhí)行成功了：
執(zhí)行select 語(yǔ)句，以便實(shí)時(shí)查看該表的數(shù)據(jù)變動(dòng)：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#ff79c6">select</span> <span style="color:#6272a4">*</span> <span style="color:#ff79c6">from</span> t_source_sqlserver<span style="color:#999999">;</span>
</code></span></span></span></span>從下圖，可以看出，只要修改左邊的數(shù)據(jù)，會(huì)在控制臺(tái)實(shí)時(shí)顯示新增刪除的數(shù)據(jù)。
同時(shí)，也能在Flink web頁(yè)面看到任務(wù)正在運(yùn)行：
文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-703092.html
最后，可以通過(guò)如下命令關(guān)閉掉Flink啟動(dòng)的集群：文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-703092.html
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell">./stop-cluster.sh
</code></span></span></span></span>文章目錄
- 1. 前言
- 2. 數(shù)據(jù)源安裝與配置
- 2.1 MySQL
- 2.1.1 安裝
- 2.1.2 CDC 配置
- 2.2 Postgresql
- 2.2.1 安裝
- 2.2.2 CDC 配置
- 2.3 Oracle
- 2.3.1 安裝
- 2.3.2 CDC 配置
- 2.4 SQLServer
- 2.4.1 安裝
- 2.4.2 CDC 配置
- 3. 驗(yàn)證
- 3.1 Flink版本與CDC版本的對(duì)應(yīng)關(guān)系
- 3.2 下載相關(guān)包
- 3.3 添加cdc jar 至lib目錄
- 3.4 驗(yàn)證
本文目錄結(jié)構(gòu)：
|___ 1. 前言
|___ 2. 數(shù)據(jù)源安裝與配置
|______ 2.1 MySQL
|_________ 2.1.1 安裝
|_________ 2.1.2 CDC 配置
|______ 2.2 Postgresql
|_________ 2.2.1 安裝
|_________ 2.2.2 CDC 配置
|______ 2.3 Oracle
|_________2.3.1 安裝
|_________2.3.2 CDC 配置
|_______2.4 SQLServer
|_________2.4.1 安裝
|_________2.4.2 CDC 配置
|___ 3. 驗(yàn)證
|_______3.1 Flink版本與CDC版本的對(duì)應(yīng)關(guān)系
|_______3.2 下載相關(guān)包
|_______3.3 添加cdc jar 至lib目錄
|_______3.4 驗(yàn)證
1. 前言
關(guān)于如何使用和配置flink cdc功能，其實(shí)在官方文檔（CDC Connectors for Apache Flink? — CDC Connectors for Apache Flink? documentation）有相關(guān)的教程了，如下：
但是講解的不是很詳細(xì)，比如數(shù)據(jù)源怎么安裝？怎么配置？都沒(méi)有很詳細(xì)的描述每一步驟，因此博主前面發(fā)布多篇文章以此來(lái)記錄flink cdc相關(guān)數(shù)據(jù)源以及其配置相關(guān)的文章，有興趣的同學(xué)可以參考下：
- 《docker下安裝oracle11g（一次安裝成功）》
- 《Docker下安裝SqlServer2019》
- 《flink postgresql cdc實(shí)時(shí)同步（含pg安裝配置等）》
- 《flink oracle cdc實(shí)時(shí)同步（超詳細(xì)）》
- 《flink sqlserver cdc實(shí)時(shí)同步（含sqlserver安裝配置等）》
本文主要就是記錄在docker下安裝和配置各種數(shù)據(jù)源，以實(shí)現(xiàn)flink cdc的功能，包含如下常見(jiàn)的數(shù)據(jù)源：
| 數(shù)據(jù)源 | 版本 |
|---|---|
| MySQL | 8.0.25 |
| Postgresql | 10.6 |
| Oracle | 11g |
| SqlServer | 2019 |
2. 數(shù)據(jù)源安裝與配置
2.1 MySQL
版本：8.0.25
2.1.1 安裝
Step1: 拉取mysql鏡像：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull mysql:8.0.25
</code></span></span></span></span>Step2: 創(chuàng)建并運(yùn)行 MySQL 容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-d</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30025</span>:3306 <span style="color:#ee9900">--name</span> mysql8.0.25 <span style="color:#ee9900">-e</span> <span style="color:#ee9900">MYSQL_ROOT_PASSWORD</span><span style="color:#6272a4">=</span>root mysql:8.0.25
</code></span></span></span></span>- 1
2.1.2?CDC?配置
Step1：進(jìn)入正在運(yùn)行的mysql容器：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> mysql8.0.25 mysql <span style="color:#ee9900">-uroot</span> <span style="color:#ee9900">-proot</span>
</code></span></span></span></span>- 1
Step2：配置 CDC
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 啟用二進(jìn)制日志</span>
mysql<span style="color:#6272a4">></span> <span style="color:#ff79c6">SET</span> <span style="color:#ff79c6">GLOBAL</span> log_bin <span style="color:#6272a4">=</span> <span style="color:#ff79c6">ON</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 設(shè)置二進(jìn)制日志格式為行級(jí)別</span>
mysql<span style="color:#6272a4">></span> <span style="color:#ff79c6">SET</span> <span style="color:#ff79c6">GLOBAL</span> binlog_format <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'ROW'</span><span style="color:#999999">;</span>
</code></span></span></span></span>Step3（非必要）：如果配置沒(méi)生效，重啟容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> restart mysql8.0.25
</code></span></span></span></span>- 1
2.2 Postgresql
版本：PostgreSQL 10.6 (Debian 10.6-1.pgdg90+1)
2.2.1 安裝
Step1： 拉取 PostgreSQL 10.6 版本的鏡像：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull postgres:10.6
</code></span></span></span></span>- 1
Step2：創(chuàng)建并啟動(dòng)?PostgreSQL?容器，在這里，我們將把容器的端口 5432 映射到主機(jī)的端口 30028，賬號(hào)密碼設(shè)置為postgres，并將?pgoutput?插件加載到?PostgreSQL?實(shí)例中：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-d</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30028</span>:5432 <span style="color:#ee9900">--name</span> postgres-10.6 <span style="color:#ee9900">-e</span> <span style="color:#ee9900">POSTGRES_PASSWORD</span><span style="color:#6272a4">=</span>postgres postgres:10.6 <span style="color:#ee9900">-c</span> <span style="color:#f1fa8c">'shared_preload_libraries=pgoutput'</span>
</code></span></span></span></span>- 1
Step3： 查看容器是否創(chuàng)建成功：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#8be9fd">ps</span> <span style="color:#6272a4">|</span> <span style="color:#8be9fd">grep</span> postgres-10.6
</code></span></span></span></span>- 1
2.2.2 CDC 配置
Step1：docker進(jìn)去Postgresql數(shù)據(jù)的容器：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> postgres-10.6 <span style="color:#8be9fd">bash</span>
</code></span></span></span></span>- 1
Step2：編輯postgresql.conf配置文件：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">vi</span> /var/lib/postgresql/data/postgresql.conf
</code></span></span></span></span>- 1
配置內(nèi)容如下：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-py"><span style="color:#6272a4"># 更改wal日志方式為logical（方式有：minimal、replica 、logical ）</span>
wal_level <span style="color:#6272a4">=</span> logical
<span style="color:#6272a4"># 更改solts最大數(shù)量（默認(rèn)值為10），flink-cdc默認(rèn)一張表占用一個(gè)slots</span>
max_replication_slots <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">20</span>
<span style="color:#6272a4"># 更改wal發(fā)送最大進(jìn)程數(shù)（默認(rèn)值為10），這個(gè)值和上面的solts設(shè)置一樣</span>
max_wal_senders <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">20</span>
<span style="color:#6272a4"># 中斷那些停止活動(dòng)超過(guò)指定毫秒數(shù)的復(fù)制連接，可以適當(dāng)設(shè)置大一點(diǎn)（默認(rèn)60s，0表示禁用）</span>
wal_sender_timeout <span style="color:#6272a4">=</span> 180s
</code></span></span></span></span>Step3：重啟容器：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> restart postgres-10.6
</code></span></span></span></span>- 1
連接數(shù)據(jù)庫(kù)，如果查詢一下語(yǔ)句，返回logical表示修改成功：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#ff79c6">SHOW</span> wal_level<span style="color:#999999">;</span>
</code></span></span></span></span>- 1
Step4：新建用戶并賦權(quán)。使用創(chuàng)建容器時(shí)的賬號(hào)密碼（postgres/postgres）登錄Postgresql數(shù)據(jù)庫(kù)。
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建數(shù)據(jù)庫(kù) test_db</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">DATABASE</span> test_db<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 連接到新創(chuàng)建的數(shù)據(jù)庫(kù) test_db</span>
\c test_db
<span style="color:#6272a4">-- 創(chuàng)建 t_user 表</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#f1fa8c">"public"</span><span style="color:#999999">.</span><span style="color:#f1fa8c">"t_user"</span> <span style="color:#999999">(</span>
<span style="color:#f1fa8c">"id"</span> int8 <span style="color:#6272a4">NOT</span> <span style="color:#8be9fd">NULL</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">"name"</span> <span style="color:#ff79c6">varchar</span><span style="color:#999999">(</span><span style="color:#f1fa8c">255</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">"age"</span> int2<span style="color:#999999">,</span>
<span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span> <span style="color:#999999">(</span><span style="color:#f1fa8c">"id"</span><span style="color:#999999">)</span>
<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- pg新建用戶</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">USER</span> test1 <span style="color:#ff79c6">WITH</span> PASSWORD <span style="color:#f1fa8c">'test123'</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 給用戶復(fù)制流權(quán)限</span>
<span style="color:#ff79c6">ALTER</span> ROLE test1 <span style="color:#ff79c6">replication</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 給用戶登錄數(shù)據(jù)庫(kù)權(quán)限</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CONNECT</span> <span style="color:#ff79c6">ON</span> <span style="color:#ff79c6">DATABASE</span> test_db <span style="color:#ff79c6">to</span> test1<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 把當(dāng)前庫(kù)public下所有表查詢權(quán)限賦給用戶</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">ALL</span> <span style="color:#ff79c6">PRIVILEGES</span> <span style="color:#ff79c6">ON</span> <span style="color:#ff79c6">ALL</span> <span style="color:#ff79c6">TABLES</span> <span style="color:#6272a4">IN</span> <span style="color:#ff79c6">SCHEMA</span> <span style="color:#ff79c6">public</span> <span style="color:#ff79c6">TO</span> test1<span style="color:#999999">;</span>
</code></span></span></span></span>Step4：發(fā)布表：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 設(shè)置發(fā)布為true</span>
<span style="color:#ff79c6">update</span> pg_publication <span style="color:#ff79c6">set</span> puballtables<span style="color:#6272a4">=</span><span style="color:#8be9fd">true</span> <span style="color:#ff79c6">where</span> pubname <span style="color:#6272a4">is</span> <span style="color:#6272a4">not</span> <span style="color:#8be9fd">null</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 把所有表進(jìn)行發(fā)布</span>
<span style="color:#ff79c6">CREATE</span> PUBLICATION dbz_publication <span style="color:#ff79c6">FOR</span> <span style="color:#ff79c6">ALL</span> <span style="color:#ff79c6">TABLES</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 查詢哪些表已經(jīng)發(fā)布</span>
<span style="color:#ff79c6">select</span> <span style="color:#6272a4">*</span> <span style="color:#ff79c6">from</span> pg_publication_tables<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 更改復(fù)制標(biāo)識(shí)包含更新和刪除之前值（目的是為了確保表 t_user 在實(shí)時(shí)同步過(guò)程中能夠正確地捕獲并同步更新和刪除的數(shù)據(jù)變化。如果不執(zhí)行這兩條語(yǔ)句，那么 t_user 表的復(fù)制標(biāo)識(shí)可能默認(rèn)為 NOTHING，這可能導(dǎo)致實(shí)時(shí)同步時(shí)丟失更新和刪除的數(shù)據(jù)行信息，從而影響同步的準(zhǔn)確性）</span>
<span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">TABLE</span> t_user REPLICA <span style="color:#ff79c6">IDENTITY</span> <span style="color:#ff79c6">FULL</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 查看復(fù)制標(biāo)識(shí)（為f標(biāo)識(shí)說(shuō)明設(shè)置成功，f（表示 full），否則為 n（表示 nothing），即復(fù)制標(biāo)識(shí)未設(shè)置）</span>
<span style="color:#ff79c6">select</span> relreplident <span style="color:#ff79c6">from</span> pg_class <span style="color:#ff79c6">where</span> relname<span style="color:#6272a4">=</span><span style="color:#f1fa8c">'t_user'</span><span style="color:#999999">;</span>
</code></span></span></span></span>2.3 Oracle
版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2.3.1 安裝
Step1：拉取 oracle 11g 鏡像（有6g，要等較長(zhǎng)的時(shí)間）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
</code></span></span></span></span>- 1
Step2：執(zhí)行以下命令以創(chuàng)建并運(yùn)行 Oracle 11g 容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-d</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30026</span>:1521 <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">8081</span>:8080 <span style="color:#999999">\</span>
<span style="color:#ee9900">--name</span> oracle_11g <span style="color:#999999">\</span>
<span style="color:#ee9900">-e</span> <span style="color:#ee9900">ORACLE_HOME</span><span style="color:#6272a4">=</span>/home/oracle/app/oracle/product/11.2.0/dbhome_2 <span style="color:#999999">\</span>
<span style="color:#ee9900">-e</span> <span style="color:#ee9900">ORACLE_SID</span><span style="color:#6272a4">=</span>helowin <span style="color:#999999">\</span>
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
</code></span></span></span></span>Step3：查看容器是否啟動(dòng)
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#8be9fd">ps</span> -a<span style="color:#6272a4">|</span><span style="color:#8be9fd">grep</span> oracle_11g
</code></span></span></span></span>Step4：進(jìn)入容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> oracle_11g <span style="color:#8be9fd">bash</span>
</code></span></span></span></span>**Step5：**設(shè)置賬號(hào)密碼
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#6272a4"># 1. 切換至root用戶(默認(rèn)是oracle用戶)，密碼為helowin</span>
<span style="color:#8be9fd">su</span> root
<span style="color:#6272a4"># 2. 創(chuàng)建軟鏈接</span>
<span style="color:#8be9fd">ln</span> <span style="color:#ee9900">-s</span> <span style="color:#ee9900">$ORACLE_HOME</span>/bin/sqlplus /usr/bin
<span style="color:#6272a4"># 3.切換回oracle用戶</span>
<span style="color:#8be9fd">su</span> oracle
<span style="color:#6272a4"># 4. 登錄sql plus</span>
sqlplus /nolog
conn /as sysdba
<span style="color:#6272a4">## 4.1 修改system用戶密碼為system</span>
alter user system identified by system<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.2 修改sys用戶密碼為system</span>
alter user sys identified by system<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.3 新增一個(gè)測(cè)試用戶（用戶名：test，密碼：test123）;</span>
create user <span style="color:#f1fa8c">test</span> identified by test123<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.4 將dba權(quán)限給內(nèi)部管理員賬號(hào)和密碼</span>
grant connect,resource,dba to <span style="color:#f1fa8c">test</span><span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.5 修改密碼策略規(guī)則為：密碼永不過(guò)期</span>
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.6 修改數(shù)據(jù)庫(kù)最大連接數(shù)；</span>
alter system <span style="color:#f1fa8c">set</span> <span style="color:#ee9900">processes</span><span style="color:#6272a4">=</span><span style="color:#f1fa8c">1000</span> <span style="color:#ee9900">scope</span><span style="color:#6272a4">=</span>spfile<span style="color:#999999">;</span>
<span style="color:#6272a4">## 4.7 最后重啟數(shù)據(jù)庫(kù)；</span>
<span style="color:#8be9fd">shutdown</span> immediate<span style="color:#999999">;</span>
startup<span style="color:#999999">;</span>
<span style="color:#6272a4"># 5.退出</span>
<span style="color:#f1fa8c">exit</span>
</code></span></span></span></span>2.3.2 CDC 配置
Step1：進(jìn)入容器
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> oracle_11g <span style="color:#8be9fd">bash</span>
</code></span></span></span></span>Step2：以DBA的權(quán)限登錄數(shù)據(jù)庫(kù)
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> sys<span style="color:#6272a4">/</span>system <span style="color:#ff79c6">AS</span> SYSDBA
</code></span></span></span></span>Step3：?jiǎn)⒂萌罩練w檔
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 設(shè)置數(shù)據(jù)庫(kù)恢復(fù)文件目標(biāo)大小為10G</span>
<span style="color:#ff79c6">alter</span> system <span style="color:#ff79c6">set</span> db_recovery_file_dest_size <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">10</span>G<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 設(shè)置數(shù)據(jù)庫(kù)恢復(fù)文件目標(biāo)路徑</span>
<span style="color:#ff79c6">alter</span> system <span style="color:#ff79c6">set</span> db_recovery_file_dest <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'/home/oracle/app/oracle/product/11.2.0'</span> scope<span style="color:#6272a4">=</span>spfile<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 立即關(guān)閉數(shù)據(jù)庫(kù)</span>
<span style="color:#ff79c6">shutdown</span> immediate<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 以mount模式啟動(dòng)數(shù)據(jù)庫(kù)</span>
startup mount<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 啟用數(shù)據(jù)庫(kù)歸檔日志模式</span>
<span style="color:#ff79c6">alter</span> <span style="color:#ff79c6">database</span> archivelog<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 打開(kāi)數(shù)據(jù)庫(kù)，允許用戶訪問(wèn)</span>
<span style="color:#ff79c6">alter</span> <span style="color:#ff79c6">database</span> <span style="color:#ff79c6">open</span><span style="color:#999999">;</span>
</code></span></span></span></span>Step4：查看日志歸檔是否啟用（如果顯示“Archive Mode”表示已經(jīng)啟用）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql">archive log list<span style="color:#999999">;</span>
</code></span></span></span></span>- 1
Step5：創(chuàng)建表空間
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 以DBA的權(quán)限登錄數(shù)據(jù)庫(kù)</span>
sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> sys<span style="color:#6272a4">/</span>system <span style="color:#ff79c6">AS</span> SYSDBA
<span style="color:#6272a4">-- 創(chuàng)建一個(gè)名為"logminer_tbs"的表空間</span>
<span style="color:#6272a4">-- 指定表空間的數(shù)據(jù)文件路徑為"/home/oracle/app/oracle/product/11.2.0/logminer_tbs.dbf"，其中"/home/oracle/app/oracle/product/11.2.0"是數(shù)據(jù)文件存儲(chǔ)的目錄，"logminer_tbs.dbf"是數(shù)據(jù)文件的文件名</span>
<span style="color:#6272a4">-- 設(shè)置表空間的初始大小為25MB</span>
<span style="color:#6272a4">-- 如果數(shù)據(jù)文件已經(jīng)存在且可重用，將其重用，否則創(chuàng)建一個(gè)新的數(shù)據(jù)文件</span>
<span style="color:#6272a4">-- 啟用表空間的自動(dòng)擴(kuò)展功能，即當(dāng)表空間空間不足時(shí)，自動(dòng)增加數(shù)據(jù)文件的大小</span>
<span style="color:#6272a4">-- 設(shè)置表空間的最大允許大小為無(wú)限，即表空間可以無(wú)限制地自動(dòng)擴(kuò)展</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLESPACE</span> logminer_tbs DATAFILE <span style="color:#f1fa8c">'/home/oracle/app/oracle/product/11.2.0/logminer_tbs.dbf'</span> SIZE <span style="color:#f1fa8c">25</span>M REUSE AUTOEXTEND <span style="color:#ff79c6">ON</span> MAXSIZE UNLIMITED<span style="color:#999999">;</span>
</code></span></span></span></span>Step6：創(chuàng)建用戶并賦權(quán)
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建一個(gè)名為"flinkuser"的用戶，密碼為"flinkpw"，將其默認(rèn)表空間設(shè)置為"LOGMINER_TBS"，并在該表空間上設(shè)置無(wú)限配額。</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">USER</span> flinkuser IDENTIFIED <span style="color:#ff79c6">BY</span> flinkpw <span style="color:#ff79c6">DEFAULT</span> <span style="color:#ff79c6">TABLESPACE</span> LOGMINER_TBS QUOTA UNLIMITED <span style="color:#ff79c6">ON</span> LOGMINER_TBS<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶創(chuàng)建會(huì)話，即允許該用戶連接到數(shù)據(jù)庫(kù)。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">SESSION</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- （不支持Oracle 11g）允許"flinkuser"用戶在多租戶數(shù)據(jù)庫(kù)（CDB）中設(shè)置容器。</span>
<span style="color:#6272a4">-- GRANT SET CONTAINER TO flinkuser;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$DATABASE視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)實(shí)例的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$<span style="color:#ff79c6">DATABASE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶執(zhí)行任何表的閃回操作。</span>
<span style="color:#ff79c6">GRANT</span> FLASHBACK <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢?nèi)魏伪淼臄?shù)據(jù)。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶擁有SELECT_CATALOG_ROLE角色，該角色允許查詢數(shù)據(jù)字典和元數(shù)據(jù)。</span>
<span style="color:#ff79c6">GRANT</span> SELECT_CATALOG_ROLE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶擁有EXECUTE_CATALOG_ROLE角色，該角色允許執(zhí)行一些數(shù)據(jù)字典中的過(guò)程和函數(shù)。</span>
<span style="color:#ff79c6">GRANT</span> EXECUTE_CATALOG_ROLE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢?nèi)魏问聞?wù)。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TRANSACTION</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- （不支持Oracle 11g）允許"flinkuser"用戶進(jìn)行數(shù)據(jù)變更追蹤（LogMiner）。</span>
<span style="color:#6272a4">-- GRANT LOGMINING TO flinkuser;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶創(chuàng)建表。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶鎖定任何表。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">LOCK</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶修改任何表。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">ANY</span> <span style="color:#ff79c6">TABLE</span> <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶創(chuàng)建序列。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">CREATE</span> SEQUENCE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶執(zhí)行DBMS_LOGMNR包中的過(guò)程。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">EXECUTE</span> <span style="color:#ff79c6">ON</span> DBMS_LOGMNR <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶執(zhí)行DBMS_LOGMNR_D包中的過(guò)程。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">EXECUTE</span> <span style="color:#ff79c6">ON</span> DBMS_LOGMNR_D <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOG視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOG <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOG_HISTORY視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)歷史日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOG_HISTORY <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGMNR_LOGS視圖，該視圖包含有關(guān)LogMiner日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGMNR_LOGS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGMNR_CONTENTS視圖，該視圖包含LogMiner日志文件的內(nèi)容。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGMNR_CONTENTS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGMNR_PARAMETERS視圖，該視圖包含有關(guān)LogMiner的參數(shù)信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGMNR_PARAMETERS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$LOGFILE視圖，該視圖包含有關(guān)數(shù)據(jù)庫(kù)日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$LOGFILE <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$ARCHIVED_LOG視圖，該視圖包含已歸檔的數(shù)據(jù)庫(kù)日志文件的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$ARCHIVED_LOG <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 允許"flinkuser"用戶查詢V_$ARCHIVE_DEST_STATUS視圖，該視圖包含有關(guān)歸檔目標(biāo)狀態(tài)的信息。</span>
<span style="color:#ff79c6">GRANT</span> <span style="color:#ff79c6">SELECT</span> <span style="color:#ff79c6">ON</span> V_$ARCHIVE_DEST_STATUS <span style="color:#ff79c6">TO</span> flinkuser<span style="color:#999999">;</span>
</code></span></span></span></span>Step7：數(shù)據(jù)庫(kù)和表啟用增量日志
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 切換至flinkuser用戶</span>
sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> flinkuser<span style="color:#6272a4">/</span>flinkpw
<span style="color:#6272a4">-- 創(chuàng)建customers表</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> customers <span style="color:#999999">(</span>
customer_id NUMBER <span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span><span style="color:#999999">,</span>
customer_name VARCHAR2<span style="color:#999999">(</span><span style="color:#f1fa8c">50</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
email VARCHAR2<span style="color:#999999">(</span><span style="color:#f1fa8c">100</span><span style="color:#999999">)</span><span style="color:#999999">,</span>
phone VARCHAR2<span style="color:#999999">(</span><span style="color:#f1fa8c">20</span><span style="color:#999999">)</span>
<span style="color:#999999">)</span> <span style="color:#ff79c6">TABLESPACE</span> LOGMINER_TBS<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 查看LOGMINER_TBS表空間下的所有表</span>
<span style="color:#ff79c6">select</span> tablespace_name<span style="color:#999999">,</span> table_name <span style="color:#ff79c6">from</span> user_tables
<span style="color:#ff79c6">where</span> tablespace_name <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'LOGMINER_TBS'</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- 以DBA的權(quán)限登錄數(shù)據(jù)庫(kù)</span>
sqlplus <span style="color:#6272a4">/</span>nolog
<span style="color:#ff79c6">CONNECT</span> sys<span style="color:#6272a4">/</span>system <span style="color:#ff79c6">AS</span> SYSDBA
<span style="color:#6272a4">-- 為L(zhǎng)OGMINER_TBS表空間下的customers表啟用增強(qiáng)日志記錄</span>
<span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">TABLE</span> FLINKUSER<span style="color:#999999">.</span>CUSTOMERS <span style="color:#ff79c6">ADD</span> SUPPLEMENTAL LOG <span style="color:#ff79c6">DATA</span> <span style="color:#999999">(</span><span style="color:#ff79c6">ALL</span><span style="color:#999999">)</span> <span style="color:#ff79c6">COLUMNS</span>
<span style="color:#6272a4">-- 為數(shù)據(jù)庫(kù)啟用增強(qiáng)日志記錄：</span>
<span style="color:#ff79c6">ALTER</span> <span style="color:#ff79c6">DATABASE</span> <span style="color:#ff79c6">ADD</span> SUPPLEMENTAL LOG <span style="color:#ff79c6">DATA</span><span style="color:#999999">;</span>
</code></span></span></span></span>2.4 SQLServer
版本：Microsoft SQL Server 2019 (RTM-CU21) (KB5025808) - 15.0.4316.3 (X64)
2.4.1 安裝
Step1：拉取SQL Server 2019 鏡像
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> pull mcr.microsoft.com/mssql/server:2019-latest
</code></span></span></span></span>Step2：運(yùn)行 SQL Server 容器（密碼必須是8個(gè)字符，并包含字母、數(shù)字和特殊字符，如：abc@123456 ，下面映射主機(jī)端口為30027）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#8be9fd">docker</span> run <span style="color:#ee9900">-e</span> <span style="color:#f1fa8c">'ACCEPT_EULA=Y'</span> <span style="color:#ee9900">-e</span> <span style="color:#f1fa8c">'SA_PASSWORD=abc@123456'</span> <span style="color:#ee9900">-p</span> <span style="color:#f1fa8c">30027</span>:1433 <span style="color:#ee9900">--name</span> sql_server_2019 <span style="color:#ee9900">-d</span> mcr.microsoft.com/mssql/server:2019-latest
</code></span></span></span></span>Step3：驗(yàn)證 SQL Server 容器是否正在運(yùn)行
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code>docker ps -a|grep sql_server_2019
</code></span></span></span></span>2.4.2 CDC 配置
Step1：開(kāi)啟SQLServer代理
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell"><span style="color:#6272a4">## 使用root用戶登錄容器</span>
<span style="color:#8be9fd">docker</span> <span style="color:#f1fa8c">exec</span> <span style="color:#ee9900">-it</span> <span style="color:#ee9900">--user</span> root sql_server_2019 <span style="color:#8be9fd">bash</span>
<span style="color:#6272a4">## 進(jìn)入容器后，執(zhí)行命令啟用Agent</span>
/opt/mssql/bin/mssql-conf <span style="color:#f1fa8c">set</span> sqlagent.enabled <span style="color:#8be9fd">true</span>
<span style="color:#6272a4">## 退出，重啟容器</span>
<span style="color:#f1fa8c">exit</span>
<span style="color:#8be9fd">docker</span> restart sql_server_2019
</code></span></span></span></span>Step2：創(chuàng)建’cdc_test’測(cè)試數(shù)據(jù)庫(kù)，并使用連接工具登錄該數(shù)據(jù)庫(kù)，使用以下 SQL 命令啟用 CDC 功能
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建數(shù)據(jù)庫(kù)</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">DATABASE</span> cdc_test<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 啟用CDC功能</span>
<span style="color:#ff79c6">EXEC</span> sys<span style="color:#999999">.</span>sp_cdc_enable_db<span style="color:#999999">;</span>
<span style="color:#6272a4">-- 判斷當(dāng)前數(shù)據(jù)庫(kù)是否啟用了CDC（如果返回1，表示已啟用）</span>
<span style="color:#ff79c6">SELECT</span> is_cdc_enabled <span style="color:#ff79c6">FROM</span> sys<span style="color:#999999">.</span><span style="color:#ff79c6">databases</span> <span style="color:#ff79c6">WHERE</span> name <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'cdc_test'</span><span style="color:#999999">;</span>
</code></span></span></span></span>Step3：選擇要進(jìn)行 CDC 跟蹤的表（這里使用orders表作為演示）
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#6272a4">-- 創(chuàng)建示例表（orders）</span>
<span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> orders <span style="color:#999999">(</span>
id <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
order_date <span style="color:#ff79c6">date</span><span style="color:#999999">,</span>
purchaser <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
quantity <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
product_id <span style="color:#ff79c6">int</span><span style="color:#999999">,</span>
<span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span> <span style="color:#999999">(</span><span style="color:#999999">[</span>id<span style="color:#999999">]</span><span style="color:#999999">)</span>
<span style="color:#999999">)</span><span style="color:#999999">;</span>
<span style="color:#6272a4">-- schema_name 是表所屬的架構(gòu)（schema）的名稱。</span>
<span style="color:#6272a4">-- table_name 是要啟用 CDC 跟蹤的表的名稱。</span>
<span style="color:#6272a4">-- cdc_role 是 CDC 使用的角色的名稱。如果沒(méi)有指定角色名稱，系統(tǒng)將創(chuàng)建一個(gè)默認(rèn)角色。</span>
<span style="color:#ff79c6">EXEC</span> sys<span style="color:#999999">.</span>sp_cdc_enable_table
<span style="color:#ee9900">@source_schema</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'dbo'</span><span style="color:#999999">,</span>
<span style="color:#ee9900">@source_name</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'orders'</span><span style="color:#999999">,</span>
<span style="color:#ee9900">@role_name</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'cdc_role'</span><span style="color:#999999">;</span>
</code></span></span></span></span>3. 驗(yàn)證
如果要驗(yàn)證flink cdc的功能，需要先下載flink的安裝包，然后下載相應(yīng)的cdc jar包并依賴，最后使用安裝包里面的sql-client寫相關(guān)的flink sql即可驗(yàn)證。
3.1 Flink版本與CDC版本的對(duì)應(yīng)關(guān)系
下載Flink安裝包以及jar包前，必須確定Flink CDC與Flink版本關(guān)系：
| Flink CDC 版本 | Flink 版本 |
|---|---|
| 1.0.0 | 1.11.* |
| 1.1.0 | 1.11.* |
| 1.2.0 | 1.12.* |
| 1.3.0 | 1.12.* |
| 1.4.0 | 1.13.* |
| 2.0.* | 1.13.* |
| 2.1.* | 1.13.* |
| 2.2.* | 1.13.*,?1.14.* |
| 2.3.* | 1.13.*,?1.14.*,?1.15.*,?1.16.0 |
| 2.4.* | 1.13.*,?1.14.*,?1.15.*,?1.16.*,?1.17.0 |
本文以?Flink1.13.6?+?Flink CDC 2.2.0?版本為例子演示。
3.2 下載相關(guān)包
flink 安裝包下載，下載地址：Downloads | Apache Flink
下載cdc相關(guān)的jar，根據(jù)自己的需求，下載相關(guān)的cdc jar：Central Repository: com/ververica
3.3 添加cdc jar 至lib目錄
把需要驗(yàn)證的cdc jar放到flink安裝包解壓之后的lib目錄（<FLINK_HOME>/lib/）:
3.4 驗(yàn)證
使用下面的命令啟動(dòng) Flink 集群：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell">./bin/start-cluster.sh
</code></span></span></span></span>啟動(dòng)成功，可以訪問(wèn)?http://localhost:8081?訪問(wèn)到 Flink Web UI：
使用下面的命令啟動(dòng) Flink SQL CLI ：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell">./bin/sql-client.sh
</code></span></span></span></span>展示如下頁(yè)面，表示啟動(dòng)flink客戶端成功：
執(zhí)行如下FlinkSQL：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#ff79c6">CREATE</span> <span style="color:#ff79c6">TABLE</span> t_source_sqlserver <span style="color:#999999">(</span>
id <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
order_date <span style="color:#ff79c6">DATE</span><span style="color:#999999">,</span>
purchaser <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
quantity <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
product_id <span style="color:#ff79c6">INT</span><span style="color:#999999">,</span>
<span style="color:#ff79c6">PRIMARY</span> <span style="color:#ff79c6">KEY</span> <span style="color:#999999">(</span>id<span style="color:#999999">)</span> <span style="color:#6272a4">NOT</span> ENFORCED
<span style="color:#999999">)</span> <span style="color:#ff79c6">WITH</span> <span style="color:#999999">(</span>
<span style="color:#f1fa8c">'connector'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'sqlserver-cdc'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'hostname'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'10.194.183.120'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'port'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'30027'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'username'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'sa'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'password'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'abc@123456'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'database-name'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'cdc_test'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'schema-name'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'dbo'</span><span style="color:#999999">,</span>
<span style="color:#f1fa8c">'table-name'</span> <span style="color:#6272a4">=</span> <span style="color:#f1fa8c">'orders'</span>
<span style="color:#999999">)</span><span style="color:#999999">;</span>
</code></span></span></span></span>可以看到執(zhí)行成功了：
執(zhí)行select 語(yǔ)句，以便實(shí)時(shí)查看該表的數(shù)據(jù)變動(dòng)：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-sql"><span style="color:#ff79c6">select</span> <span style="color:#6272a4">*</span> <span style="color:#ff79c6">from</span> t_source_sqlserver<span style="color:#999999">;</span>
</code></span></span></span></span>從下圖，可以看出，只要修改左邊的數(shù)據(jù)，會(huì)在控制臺(tái)實(shí)時(shí)顯示新增刪除的數(shù)據(jù)。
同時(shí)，也能在Flink web頁(yè)面看到任務(wù)正在運(yùn)行：
最后，可以通過(guò)如下命令關(guān)閉掉Flink啟動(dòng)的集群：
<span style="color:rgba(0, 0, 0, 0.75)"><span style="background-color:#ffffff"><span style="color:#000000"><span style="background-color:#282a36"><code class="language-shell">./stop-cluster.sh
</code></span></span></span></span>到了這里，關(guān)于flink cdc多種數(shù)據(jù)源安裝、配置與驗(yàn)證 flink cdc多種數(shù)據(jù)源安裝、配置與驗(yàn)證的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
