背景環境
在使用異構資料庫構建資料平臺的過程中，異構資料庫之間的訪問一直是比較複雜的問題。我們使用PostgeSQL的過程中，遇到需要訪問MySQL實時資料的場景。可供我們選擇的方案包括
- 使用OGG等三方工具實現實時資料複製；
- 使用PostgreSQL的DBLink機制遠端訪問異構資料庫；
- 使用MySQL_FDW擴充套件遠端訪問異構資料庫；
FDW(Foreign Data Wrapper)是我們選用的開源方案，這個方案明顯的優點是使用統一的介面方式實現多種資料庫的遠端訪問，包括但不限於PostgreSQL, MySQL, MongoDB, HDFS 等等。
本次我們專注於MySQL _FDW的使用，軟體環境如下
CentOS 7 x64
PostgreSQL 11.1
MySQL_FDW 11
準備工作
源庫和目標庫的具體搭建過程不再描述。我們需要使用的DB和使用者資訊如下
- 源庫（MySQL 5.7）：DB名稱lhb，使用者名稱稱lhb
- 目標庫（PostgreSQL 11.1）：DB名稱demo，使用者名稱稱demo
安裝軟體
CentOS下通過yum可以直接安裝FDW的最新版本，這次測試的版本是11
yum install -y mysql_fdw_11
複製程式碼建立FDW並授權
非常重要：
- 因為許可權的原因，只有superuer才能建立FDW；
- 因為FDW是在DB內生效的，所以必須進入具體的DB操作；
### 進入PostgreSQL。 admin是我們建立的超級使用者， demo是我們建立的測試DB
psql --username=admin --dbname=demo --password
複製程式碼為了更好的進行演示，程式碼片段中我會把提示符和操作結果資訊也顯示出來。拷貝指令碼的時候要注意！
-- 確認自己的使用者和DB資訊
demo=# \c
You are now connected to database "demo" as user "admin".
-- 建立FDW，一定要在目標DB中操作，這裡是demo庫。
-- mysql_fdw 名稱是固定的
demo=# create extension mysql_fdw;
-- 給目標使用者demo授權
demo=# grant usage on foreign data wrapper mysql_fdw to demo;
-- 檢視一下已經建立的FDW資訊
demo=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
-----------+-------+-------------------+---------------------
mysql_fdw | admin | mysql_fdw_handler | mysql_fdw_validator
複製程式碼定義遠端伺服器
因為前面已經進行了授權，所以除非特別說明，下面的操作都使用普通使用者(demo)執行。
### demo是我們建立的普通使用者， demo是我們建立的測試DB
psql --username=demo --dbname=demo --password
複製程式碼-- server_lhb 是遠端伺服器的別名，隨便取
create server server_lhb foreign data wrapper mysql_fdw options (host '172.16.x.x',port '3306');
-- 檢視一下
demo=> \des
List of foreign servers
Name | Owner | Foreign-data wrapper
---------+-------+----------------------
server_lhb | demo | mysql_fdw
複製程式碼定義使用者對映
在目標庫定義使用者對映，即本地的使用者可以對映為源庫的指定使用者。
-- 本地的PUBLIC使用者對映為源庫的lhb使用者，xxx是源庫的密碼
create user mapping for public server server_lhb options (username 'lhb',password 'xxx');
-- 檢視一下
demo=> \deu+
List of user mappings
Server | User name | FDW options
---------+-----------+----------------------------------
server_lhb | public | (username 'lhb', password 'xxx')
複製程式碼使用外部表
在真正能訪問源庫資料之前，我們還需要把源庫的表結構同步到目標庫上。根據不同的場景會有不同的方法。
-- 建立一個新的schema來存放外部表
demo=> create schema src_lhb;
-- 檢視一下
demo=> \dn
List of schemas
Name | Owner
---------+----------
src_lhb | demo
public | postgres
複製程式碼明確定義外部表
當源表有很多欄位，但是我僅僅需求幾個的時候，我可以明確定義一個外部表來指定欄位。這種場景下，只有源表中的指定欄位發生變化才會影響查詢。
-- 建立一個account_ft表，對映為源庫lahuobao下的account表。這裡表名稱可以不同於源庫
create foreign table src_lhb.account_ft (
account_id int not null,
bank_card_no varchar(30)
)server server_lhb
options (dbname 'lahuobao', table_name 'account');
-- 檢視一下資料
demo=> select * from src_lhb.account_ft limit 3;
account_id | bank_card_no
------------+----------------------
1 |
2 | 6228481722089439218
3 | 62252546325498753698
(3 rows)
複製程式碼直接匯入外部表
更多的時候，我們僅需要直接使用源表的結構。這種場景下，通過直接匯入的方式即可批量建立外部表。
注意：如果源表的結構發生變化，大概率可能會造成查詢失敗。需要重新匯入一次表結構。
指定表
-- 僅匯入指定的表，lahuobao庫下的account，waybill兩張表到src_lhb 模式(schema)下
import foreign schema lahuobao limit to (account,waybill) from server server_lhb into src_lhb;
複製程式碼所有表
-- 一次性匯入指定DB下的所有表
import foreign schema lahuobao from server server_lhb into src_lhb;
複製程式碼檢視外部表資訊
-- 檢視一下已經有哪些外部表
demo=> select * from information_schema.foreign_tables;
foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
demo | src_lhb | account_ft | demo | server_lhb
demo | src_lhb | account | demo | server_lhb
demo | src_lhb | waybill | demo | server_lhb
(3 rows)
複製程式碼刪除外部表
-- 指定表名稱，刪除多個表
drop foreign table src_lhb.account_ft, src_lhb.account, src_lhb.waybill;
-- 或者構造sql語句批量刪除
select
'drop foreign table ' || t.table_schema || '.' || t.table_name || ';' as drop_sql
from information_schema.tables t
where t.table_type in ('FOREIGN')
and t.table_schema in ('src_lhb', 'public')
;
複製程式碼-- 或者直接刪除FDW擴充套件來刪除所有外部表(必須是owner，這裡就是admin使用者)
drop extension mysql_fdw cascade;
複製程式碼