全部產品
Search
文件中心

ApsaraDB RDS:讀寫MySQL資料(mysql_fdw)

更新時間:Nov 27, 2024

RDS PostgreSQL提供mysql_fdw外掛程式,可以讀寫RDS MySQL執行個體或自建MySQL資料庫裡的資料。

前提條件

  • 執行個體為RDS PostgreSQL 10或以上版本的雲端硬碟執行個體。

    說明

    RDS PostgreSQL 14需要核心小版本至少為20221030,如何查看和升級核心小版本,請參見升級核心小版本

  • 將RDS PostgreSQL的專用網路網段(例如172.xx.xx.xx/16)添加到MySQL執行個體的白名單中,允許RDS PostgreSQL訪問。

    說明

    您可以在RDS PostgreSQL執行個體的資料庫連接中查看專用網路網段。查看VPC網段

背景資訊

PostgreSQL從9.6開始就支援並行計算,到11的時候並行計算效能得到巨大提升,10億資料量的join查詢可以實現秒級完成。所以很多使用者會使用PostgreSQL作為小的資料倉儲使用,同時又能提供高並發訪問。

使用mysql_fdw外掛程式能夠將PostgreSQL和MySQL串連,同步MySQL資料進行資料分析。

操作步驟

  1. 建立mysql_fdw外掛程式。

    postgres=> create extension mysql_fdw;  
    CREATE EXTENSION  
    說明

    僅高許可權帳號可以執行此命令。

  2. 建立MySQL伺服器定義。

    postgres=> CREATE SERVER <server名稱>  
    postgres->      FOREIGN DATA WRAPPER mysql_fdw
    postgres->      OPTIONS (host '<串連地址>', port '<串連連接埠>');  
    CREATE SERVER  
    說明

    伺服器定義中的host必須配置為MySQL的內網地址,port必須配置為MySQL的內網連接埠。

    樣本

    postgres=> CREATE SERVER mysql_server  
    postgres->      FOREIGN DATA WRAPPER mysql_fdw
    postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
    CREATE SERVER  
  3. 建立使用者映射,將MySQL伺服器定義映射到PostgreSQL的某個使用者上,將來使用這個使用者訪問MySQL的資料。

    postgres=> CREATE USER MAPPING FOR <PostgreSQL使用者名稱>   
    SERVER <server名稱>  
    OPTIONS (username '<MySQL使用者名稱>', password '<MySQL使用者對應密碼>');  
    CREATE USER MAPPING  

    樣本

    postgres=> CREATE USER MAPPING FOR pgtest 
    SERVER mysql_server  
    OPTIONS (username 'mysqltest', password 'Test1234!');  
    CREATE USER MAPPING  
  4. 使用上一步驟的PostgreSQL使用者建立MySQL的外部表格。

    說明

    外部表格的欄位名要與MySQL資料庫中表的欄位名相同,同時可以僅建立您想要查詢的欄位。例如MySQL資料庫中的表有3個欄位ID、NAME、AGE,您可以僅建立其中2個欄位ID、NAME。

    postgres=> CREATE FOREIGN TABLE <表名> (<欄位名> <資料類型>,<欄位名> <資料類型>...) server <server名稱> options (dbname '<MySQL資料庫名>', table_name '<MySQL表名>');  
    CREATE FOREIGN TABLE  

    樣本

    postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');  
    CREATE FOREIGN TABLE  

測試讀寫

您可以通過外部表格讀寫MySQL資料。

說明

MySQL對應的表必須有主鍵才可以寫入資料,否則會報如下錯誤:

ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation.
postgres=> select * from ft_test ;  

postgres=> insert into ft_test values (2,'abc');  
INSERT 0 1  

postgres=> insert into ft_test select generate_series(3,100),'abc';  
INSERT 0 98  
postgres=> select count(*) from ft_test ;  
 count   
-------  
    99  
(1 row)  

檢查執行計畫,即PostgreSQL查詢MySQL資料的請求在MySQL中是如何執行的。

postgres=> explain verbose select count(*) from ft_test ;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Aggregate  (cost=1027.50..1027.51 rows=1 width=8)  
   Output: count(*)  
   ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)  
         Output: id, info  
         Remote server startup cost: 25  
         Remote query: SELECT NULL FROM `test123`.`test`  
(6 rows)  

postgres=> explain verbose select id from ft_test where id=2;  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id  
   Remote server startup cost: 25  
   Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))  
(4 rows)