やりたいこと

RaspberryPi上のMySQLのデータを、VirtualBox上のPostgreSQLから操作したい。
どうやらFDWという機能を使う事で実現できる模様。

環境

macOS High Sierra 10.13.1
- Ubuntu17.04(Virtual Box)
  - PostgreSQL9.6
Raspberry Pi Zero W
- MySQL14.14

ネットワーク環境

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

FDWとは

Foreign Data Wrapperの略。
ローカルPostgreSQLから他のリモートPostgreSQLを操作する事ができる機能です。
今回のようにPostgreSQLからMySQLを接続する時は標準機能ではできませんので、 mysql_fdw をインストールする必要があります。

FDWを使うまでの手順

PostgreSQL: mysql_fdwのインストール
PostgreSQL: 外部サーバの作成
PostgreSQL: ユーザーマッピング
PostgreSQL: 外部テーブルの作成
MySQL: bind-address設定
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.外部サーバの作成

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_config、 mysql_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からは直接アクセスできないと言う事です。

参考