Posted at

FDWでPostgreSQLからMySQLのテーブルを操作する

More than 1 year has passed since last update.


やりたいこと

RaspberryPi上のMySQLのデータを、VirtualBox上のPostgreSQLから操作したい。

どうやらFDWという機能を使う事で実現できる模様。


環境


  • macOS High Sierra 10.13.1


    • Ubuntu17.04(Virtual Box)


      • PostgreSQL9.6





  • Raspberry Pi Zero W


    • MySQL14.14




ネットワーク環境

FDW (3).png

iot_dbがデータベース。sht31はテーブルです。


FDWとは

Foreign Data Wrapperの略。

ローカルPostgreSQLから他のリモートPostgreSQLを操作する事ができる機能です。

今回のようにPostgreSQLからMySQLを接続する時は標準機能ではできませんので、 mysql_fdw をインストールする必要があります。


FDWを使うまでの手順


  1. PostgreSQL: mysql_fdwのインストール

  2. PostgreSQL: 外部サーバの作成

  3. PostgreSQL: ユーザーマッピング

  4. PostgreSQL: 外部テーブルの作成

  5. MySQL: bind-address設定

  6. MySQL: 権限追加


mysql_fdwのインストール

PostgreSQLが稼働している、Ubuntuでの作業です。


mysql_fdwのソースを入手

mysql_fdwのページはここです。

適当な作業フォルダ内に移動して、mysql_fdwをgitから持って来ましょう。

git clone https://github.com/EnterpriseDB/mysql_fdw.git

MySQL Foreign Data Wrapper for PostgreSQL - 1. Installationを参考に作業を進めていきます。


コンパイル

私の環境ではコンパイルのための必要な環境が整っていなかったので、コンパイル前準備を行いました。

次のコマンドでコンパイルとインストールを行います。

make USE_PGXS=1

make USE_PGXS=1 install

makeコマンド実行時にエラーが二つ発生しました。

- makeがない

- postgresql-server-dev-X.Yがない


PostgreSQLにmysql_fdwを組み込む

psqlで次の CREATE EXTENSION mysql_fdw; を実行します。このコマンドは1回だけで良いです。


mysql_fdwをインストール後の初回設定

postgres@ubuntu:~$ psql

psql (9.6.5)
Type "help" for help.

postgres=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION



PostgreSQLでFDW設定

MySQL Foreign Data Wrapper for PostgreSQL - Usageを参考にしながら設定をしていきます。

FDW設定に必要な手順は次の通りです。


  1. 外部サーバの作成

  2. ユーザーマッピング

  3. 外部テーブルの作成


外部サーバの作成


手順1.外部サーバの作成

CREATE SERVER mpi_mysql FOREIGN DATA WRAPPER mysql_fdw OPTIONS (

host '192.168.3.12',
port '3306'
);


  • mpi_mysql: PostgreSQLで接続するMySQLサーバーの名称

  • host: MySQLが稼働しているサーバのIPアドレス

  • port: MySQLが待ち受けしているポート番号

RaspberryPiに与えているIPアドレスが変わる事もあるでしょう。

その場合はALTERクエリを使えば変更可能です。


ユーザーマッピング


手順2.外部サーバのユーザマップの作成

CREATE USER MAPPING FOR public SERVER mpi_mysql OPTIONS (

password 'password',
username 'iot'
);



  • public: PostgreSQL側のスキーマ名

  • mpi_mysql: CREATE SERVERで決めたMySQLサーバー名

  • username: MySQLで設定されているユーザー名

  • password: MySQLで設定されているパスワード


外部テーブルの作成


手順3.外部テーブルの作成

CREATE FOREIGN TABLE sht31 (

temp numeric(5,3),
humi numeric(5,3),
date timestamp without time zone
)
SERVER mpi_mysql
OPTIONS (
dbname 'iot_db',
table_name 'sht31'
);


  • sht31: PostgreSQLで使用するテーブル名

  • temp..date: MySQL上の利用したいテーブルのスキーマ

  • mpi_mysql: CREATE SERVERで決めたMySQLサーバー名

  • dbname: MySQL上の利用したいDB名

  • table-name: MySQL上の利用したいテーブル名


MySQL側の設定

MySQLが稼働している、RaspberryPiでの作業です。


bind-address設定

bind-addressにMySQLが稼働しているサーバのアドレス、つまり自分自身のIPアドレスを設定します。


my.cnfの修正内容

diff --git a/my.cnf b/my.cnf

index c2390e8..cff5d9f 100644
--- a/my.cnf
+++ b/my.cnf
@@ -45,6 +45,7 @@ skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
+bind-address = 192.168.3.12
#
# * Fine Tuning
#

設定反映のため、MySQLを再起動します。

sudo /etc/init.d/mysql restart


権限追加

grant all privileges on *.* to iot@"192.168.3.2" IDENTIFIED BY 'password';


  • iot : sht31テーブルにアクセスできるアカウント

  • 192.168.3.2 : PostgreSQLが稼働しているサーバのIPアドレス

  • password : iotアカウントのパスワード

PostgreSQL(192.168.3.2)からiotアカウントでアクセスするための権限を追加します。

この後に実際にselectクエリ発行時にERROR:failed to connect to MySQLが出て結構ハマりました。ネットワーク構成図を書いて考えることは大事ですね。


PostgreSQLからMySQLのテーブルをカウントしてみる

PostgreSQLが稼働している、Ubuntuでの作業です。

postgres=# select count(*) from sht31;

count
-------
26609
(1 row)

無事、PostgreSQLからRaspberryPi上のMySQLのテーブルを読む事に成功しました!


備忘録


コンパイル前準備

mysql_fdwを使うためにはコンパイルする必要があります。

コンパイルのためには pg_configmysql_config の二つが必要です。

現在の環境にそれぞれがインストール済みなのか確認してみましょう。


pg_configの有無確認

msrx9@ubuntu:~/tmp/mysql_fdw$ locate pg_config

/usr/bin/pg_config


mysql_configの有無確認

msrx9@ubuntu:~/tmp/mysql_fdw$ locate mysql_config

# ヒットせず

pg_configはありましたが、mysql_configはないみたいです。

mysql_configと実行すると、次のようにどのパッケージに入っているかを教えてくれます。


mysql_config(がない時)の実行結果

msrx9@ubuntu:~/tmp/mysql_fdw$ mysql_config

プログラム 'mysql_config' は以下のパッケージで見つかりました:
* libmysqlclient-dev
* libmariadb-dev-compat
* libmariadbclient-dev
次の操作を試してください: sudo apt install <選択したパッケージ>

とりあえず libmysqlclient-dev をインストールすれば良さそうなので、apt-getします。

sudo apt-get update # これをしていないとパッケージが見つかりませんとエラーになりました

sudo apt install libmysqlclient-dev


mysql_configの存在確認

msrx9@ubuntu:~/tmp/mysql_fdw$ locate mysql_config

/usr/bin/mysql_config

無事にインストールされたようです。

では、それぞれがインストールされている /usr/bin/ にパスが通っているかを確認しましょう。

msrx9@ubuntu:~/tmp/mysql_fdw$ echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games

はい、両方とも$PATH内ですね。

これでコンパイルのための環境準備は完了です。


makeがない


エラーメッセージ

msrx9@ubuntu:~/tmp/mysql_fdw$ make USE_PGXS=1

プログラム 'make' は以下のパッケージで見つかりました:
* make
* make-guile
次の操作を試してください: sudo apt install <選択したパッケージ>


対処

sudo apt-get install build-essential


postgresql-server-dev-X.Yがない


エラーメッセージ

msrx9@ubuntu:~/tmp/mysql_fdw$ make USE_PGXS=1

You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
Makefile:52: *** PostgreSQL 9.3, 9.4, 9.5, 9.6 or 10beta is required to compile this extension. 中止.


対処

sudo apt-get install postgresql-server-dev-9.6


後からIPアドレスを変更したい場合

ALTERクエリを使えば後からIPアドレスなどを変更する事ができます。

ALTER SERVER mpi_mysql OPTIONS (SET host '192.168.1.29');


ERROR:failed to connect to MySQL

postgres=# select count(*) from sht31;

ERROR: failed to connect to MySQL: Can't connect to MySQL server on '192.168.3.12' (111)

MySQLに接続できないみたいです。

Ubuntuからmysqlコマンドで直接RaspberryPiのMySQLにアクセスして見ます。

postgres@ubuntu:~$ mysql -h 192.168.3.12 -u iot -p

Enter password:
ERROR 1045 (28000): Access denied for user 'iot'@'192.168.3.2' (using password: YES)

Access deninedとなりました。

理由が、grant クエリを実行する時に、 iot@192.168.3.2(MacのIPアドレス) ではなく iot@192.168.56.101(UbuntuのIPアドレス) で設定しまった事です。

今回の環境はネットワーク構成図の通り、UbuntuはMac上の仮想マシンとして外部ネットワークとはNATで接続されています。

ですので、UbuntuのIPアドレスを指定しても、RaspberryPiからは直接アクセスできないと言う事です。


参考