やりたいこと
RaspberryPi上のMySQLのデータを、VirtualBox上のPostgreSQLから操作したい。
どうやらFDWという機能を使う事で実現できる模様。
環境
- macOS High Sierra 10.13.1
- Ubuntu17.04(Virtual Box)
- PostgreSQL9.6
- Ubuntu17.04(Virtual Box)
- 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コマンド実行時にエラーが二つ発生しました。
PostgreSQLにmysql_fdwを組み込む
psqlで次の CREATE EXTENSION mysql_fdw; を実行します。このコマンドは1回だけで良いです。
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設定に必要な手順は次の通りです。
- 外部サーバの作成
- ユーザーマッピング
- 外部テーブルの作成
外部サーバの作成
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クエリを使えば変更可能です。
ユーザーマッピング
CREATE USER MAPPING FOR public SERVER mpi_mysql OPTIONS (
password 'password',
username 'iot'
);
- public: PostgreSQL側のスキーマ名
- mpi_mysql:
CREATE SERVERで決めたMySQLサーバー名 - username: MySQLで設定されているユーザー名
- password: MySQLで設定されているパスワード
外部テーブルの作成
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アドレスを設定します。
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 の二つが必要です。
現在の環境にそれぞれがインストール済みなのか確認してみましょう。
msrx9@ubuntu:~/tmp/mysql_fdw$ locate pg_config
/usr/bin/pg_config
msrx9@ubuntu:~/tmp/mysql_fdw$ locate mysql_config
# ヒットせず
pg_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
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からは直接アクセスできないと言う事です。