MySQL データの読み書き (mysql_fdw) - ApsaraDB RDS - Alibaba Cloud ドキュメントセンター

RDS PostgreSQL には mysql_fdw 拡張が組み込まれており、RDS MySQL インスタンスやセルフマネージドの MySQL データベースのデータを読み書きできます。

前提条件

インスタンスは、クラウドディスクを使用する RDS PostgreSQL 10 以降である必要があります。
説明
- RDS PostgreSQL 14 の場合、マイナーエンジンバージョンは 20221030 以降である必要があります。
- RDS PostgreSQL 17 の場合、マイナーエンジンバージョンは 20241030 以降である必要があります。
マイナーエンジンバージョンを表示およびアップグレードするには、「マイナーエンジンバージョンのアップグレード」をご参照ください。
RDS PostgreSQL インスタンスの VPC CIDR ブロック (例： 172.xx.xx.xx/16) を MySQL インスタンスのホワイトリストに追加してください。

説明
RDS PostgreSQL インスタンスの [データベース接続] ページで、[ネットワークタイプ] の横にある VPC CIDR ブロック (例: 172.xx.xx.xx/16) を確認し、[内部ポート] (例: 5432) を控えます。

背景情報

PostgreSQL はバージョン 9.6 から並列コンピューティングをサポートしており、バージョン 11 ではパフォーマンスが大幅に向上し、10 億行のデータに対する結合クエリを数秒で完了できるようになりました。その結果、多くのユーザーが、高い同時実行性もサポートする小規模なデータウェアハウスとして PostgreSQL を使用しています。

mysql_fdw 拡張を使用して PostgreSQL を MySQL に接続し、MySQL からデータを同期して分析できます。

手順

mysql_fdw 拡張を作成します。
```
postgres=> create extension mysql_fdw;  
CREATE EXTENSION  
```
説明
このコマンドは、特権アカウントのみが実行できます。

MySQL のサーバー定義を作成します。

postgres=> CREATE SERVER <server_name>  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host '<endpoint>', port '<port>');  
CREATE SERVER

説明

サーバー定義で、host パラメーターを MySQL インスタンスの内部エンドポイントに、port パラメーターをその内部ポートに設定します。

例

postgres=> CREATE SERVER mysql_server  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
CREATE SERVER

ユーザーマッピングを作成して、サーバー定義を MySQL データベースにアクセスする PostgreSQL ユーザーにリンクします。

postgres=> CREATE USER MAPPING FOR <postgresql_username>   
SERVER <server_name>  
OPTIONS (username '<mysql_username>', password '<password_of_mysql_user>');  
CREATE USER MAPPING

例

postgres=> CREATE USER MAPPING FOR pgtest 
SERVER mysql_server  
OPTIONS (username 'mysqltest', password 'Test1234!');  
CREATE USER MAPPING

前の手順の PostgreSQL ユーザーを使用して、MySQL テーブルの外部テーブルを作成します。
説明
外部テーブルの列名は、MySQL テーブルの対応する列名と一致する必要があります。クエリする列のみを定義する必要があります。たとえば、MySQL テーブルに 3 つの列 (ID、NAME、AGE) が含まれている場合、ID 列と NAME 列のみを持つ外部テーブルを作成できます。
```
postgres=> CREATE FOREIGN TABLE <table_name> (<column_name> <data_type>,<column_name> <data_type>...) server <server_name> options (dbname '<mysql_database_name>', table_name '<mysql_table_name>');  
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)

実行計画をチェックして、外部テーブルに対するクエリがどのように 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=8)
         Output: id1, name1
         Remote server startup cost: 25  
         Remote query: SELECT NULL FROM `test123`.`test`  
(6 rows)  
postgres=> explain verbose select id1 from ft_test where id1=2;
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id1  
   Remote server startup cost: 25  
   Remote query: SELECT `id1` FROM `test123`.`test` WHERE ((`id1` = 2))  
(4 rows)