mysql_fdw拡張子を使用してMySQLデータベースからデータを読み書きする - ApsaraDB RDS

このトピックでは、ApsaraDB RDS for PostgreSQLのmysql_fdw拡張機能を使用してMySQLデータベースからデータを読み書きする方法について説明します。 MySQLデータベースは、ApsaraDB RDS for MySQLインスタンス上のデータベースまたは自己管理型MySQLデータベースです。

前提条件

RDSインスタンスは、標準SSDまたは拡張SSD (ESSD) でPostgreSQL 10以降を実行します。
説明 RDSインスタンスがPostgreSQL 14を実行している場合、RDSインスタンスのマイナーエンジンバージョンは20221030以降である必要があります。マイナーエンジンバージョンを表示および更新する方法の詳細については、「マイナーエンジンバージョンの更新」をご参照ください。
RDSインスタンスが属するVPCのCIDRブロックがMySQLデータベースのIPアドレスホワイトリストに追加されます。これにより、RDSインスタンスはMySQLデータベースと通信できます。 CIDRブロックの例は、172.xx. xx.xx/16です。
説明 CIDRブロックは、RDSインスタンスの [データベース接続] ページで表示できます。

背景情報

PostgreSQL 9.6以降のバージョンは、並列コンピューティングをサポートしています。 PostgreSQL 11では、最大10億のデータレコードに対する結合クエリを数秒以内に完了できます。多数のユーザーがPostgreSQLを使用して、小規模なデータウェアハウスを構築し、同時並行性の高いアクセス要求を処理します。

mysql_fdw拡張機能は、RDSインスタンスとMySQLデータベース間で接続を確立し、データを同期できます。

手順

mysql_fdw拡張子を作成します。
```
postgres=> create extension mysql_fdw;
拡張の作成 
```
説明特権アカウントのみがステートメントを実行する権限を持っています。

MySQLサーバーを定義します。

postgres=> CREATE SERVER <MySQLサーバーの名前>
postgres-> 外国データラッパーmysql_fdw
postgres-> OPTIONS (ホスト '<MySQLサーバーのエンドポイント>' 、ポート '<MySQLサーバーのポート番号>');
サーバーの作成

説明 hostの値は、MySQLサーバーの内部エンドポイントである必要があります。 portの値は、MySQLサーバーの内部ポート番号である必要があります。

例：

postgres=> CREATE SERVER mysql_server
postgres-> 外国データラッパーmysql_fdw
postgres-> OPTIONS (ホスト 'rm -xxx.mysql.rds.aliyuncs.com '、ポート '3306');
サーバーの作成

MySQLサーバーをRDSインスタンスに作成されたアカウントにマップします。このアカウントを使用して、MySQLサーバー上にあるMySQLデータベースからデータを読み取り、MySQLデータベースにデータを書き込むことができます。

postgres=> CREATE USER MAPPING FOR <MySQLサーバーがマップされているアカウントのユーザー名>
SERVER <MySQLサーバーの名前>
オプション (ユーザー名 '<MySQLデータベースへの接続に使用されるアカウントのユーザー名>' 、パスワード '<前のアカウントのパスワード>');
ユーザーマッピング

を作成する

例：

postgres=> pgtestのユーザーマッピングの作成
サーバーmysql_server
オプション (ユーザー名 'mysqltest' 、パスワード 'Test1234!');
ユーザーマッピング

を作成する

前の手順でMySQLサーバーにマップしたアカウントを使用して、外部MySQLテーブルを作成します。
説明外部MySQLテーブルのフィールド名は、MySQLデータベースのテーブルのフィールド名と同じである必要があります。クエリするフィールドのみを作成するように選択できます。たとえば、MySQLデータベースのテーブルにID、NAME、およびAGEフィールドが含まれている場合、外部MySQLテーブルのIDフィールドとNAMEフィールドのみを作成できます。
```
postgres=> CREATE FOREIGN TABLE <外部MySQLテーブルの名前> (<フィールド1の名前> <フィールド1のデータ型> 、<フィールド2の名前> <フィールド2のデータ型>...) サーバー <MySQLサーバーの名前> オプション (dbname '<MySQLデータベースの名前>' 、table_name '<MySQLデータベース内のテーブルの名前>');
外国表
```
の作成
例：
```
postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) サーバmysql_serverオプション (dbname 'test123', table_name 'test');
外国表
```
の作成

次のステップ

外部MySQLテーブルを使用して、MySQLデータベースでの読み取りおよび書き込み操作のパフォーマンスを確認できます。

説明テーブルにプライマリキーが割り当てられている場合にのみ、MySQLデータベースのテーブルにデータを書き込むことができます。テーブルに主キーが割り当てられていない場合、次のエラーが返されます。

error: リモートテーブルの最初の列は、INSERT/UPDATE/DELETE操作に対して一意である必要があります。

postgres=> select * from ft_test;

postgres=> ft_test値 (2、'abc') に挿入します。挿入0 1

postgres=> select generate_series(3,100) 、'abc' に挿入します。挿入0 98
postgres=> ft_testからカウント (*) を選択します。  
 カウント
-------  
    99
(1行)

実行計画を表示して、MySQLデータベースのデータを照会するためにRDSインスタンスから送信されたリクエストがどのように実行されるかを確認します。

postgres=> ft_testから詳細な選択カウント (*) を説明します。  
                                  クエリ計画
-------------------------------------------------------------------------------  
 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  
         リモートクエリ: SELECT NULL FROM 'test123'.'test'
(6行)

postgres=> id=2であるft_testから詳細な選択idを説明します。  
                               クエリ計画
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id  
   Remote server startup cost: 25  
   リモートクエリ: SELECT 'id' FROM 'test123'.'test' WHERE (('id' = 2))
(4行)