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


# やりたいこと

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](https://qiita-image-store.s3.amazonaws.com/0/27090/8362f108-a280-ab65-97c6-5d461693ba86.png "FDW (3).png")


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

# FDWとは

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



# FDWを使うまでの手順

1. PostgreSQL: [mysql_fdwのインストール](#mysql_fdwのインストール)
1. PostgreSQL: [外部サーバの作成](#外部サーバの作成)
1. PostgreSQL: [ユーザーマッピング](#ユーザーマッピング)
1. PostgreSQL: [外部テーブルの作成](#外部テーブルの作成)
1. MySQL: [bind-address設定](#bind-address設定)
1. MySQL: [権限追加](#権限追加)



# mysql_fdwのインストール

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


## mysql_fdwのソースを入手

mysql_fdwのページは[ここ](https://github.com/EnterpriseDB/mysql_fdw)です。

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

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

[MySQL Foreign Data Wrapper for PostgreSQL - 1. Installation](https://github.com/EnterpriseDB/mysql_fdw#mysql-foreign-data-wrapper-for-postgresql)を参考に作業を進めていきます。






## コンパイル

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


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


```
make USE_PGXS=1
make USE_PGXS=1 install
```

makeコマンド実行時にエラーが二つ発生しました。
- [makeがない](#makeがない)
- [postgresql-server-dev-X.Yがない](#postgresql-server-dev-xyがない)

## PostgreSQLにmysql_fdwを組み込む

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

```psql: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](https://github.com/EnterpriseDB/mysql_fdw#usage)を参考にしながら設定をしていきます。

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

1. 外部サーバの作成
1. ユーザーマッピング
1. 外部テーブルの作成

## 外部サーバの作成

```sql:手順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クエリ](#後からipアドレスを変更したい場合)を使えば変更可能です。


## ユーザーマッピング

```sql:手順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で設定されているパスワード

## 外部テーブルの作成

```sql:手順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アドレスを設定します。

```diff: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](#errorfailed-to-connect-to-mysql)が出て結構ハマりました。ネットワーク構成図を書いて考えることは大事ですね。


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

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

```sql:
postgres=# select count(*) from sht31;
 count
-------
 26609
(1 row)
```

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





# 備忘録

## コンパイル前準備

mysql_fdwを使うためにはコンパイルする必要があります。
コンパイルのためには `pg_config`、 `mysql_config` の二つが必要です。
現在の環境にそれぞれがインストール済みなのか確認してみましょう。

```sh:pg_configの有無確認
msrx9@ubuntu:~/tmp/mysql_fdw$ locate pg_config
/usr/bin/pg_config
```


```sh:mysql_configの有無確認
msrx9@ubuntu:~/tmp/mysql_fdw$ locate mysql_config
# ヒットせず
```

pg_configはありましたが、mysql_configはないみたいです。
mysql_configと実行すると、次のようにどのパッケージに入っているかを教えてくれます。

```sh: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します。

```mysql_configのインストール
sudo apt-get update # これをしていないとパッケージが見つかりませんとエラーになりました
sudo apt install libmysqlclient-dev
```

```console: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がない


### エラーメッセージ

```makeエラー1
msrx9@ubuntu:~/tmp/mysql_fdw$ make USE_PGXS=1
プログラム 'make' は以下のパッケージで見つかりました:
 * make
 * make-guile
次の操作を試してください: sudo apt install <選択したパッケージ>
```

### 対処

```makeを実行した際に発生したエラー対処のために追加したパッケージ
sudo apt-get install build-essential
```


## postgresql-server-dev-X.Yがない
### エラーメッセージ

```エラー2のメッセージ
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.  中止.
```

### 対処

```makeを実行した際に発生したエラー対処のために追加したパッケージ
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

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

# 参考
- [Foreign Data Wrapper（FDW）の機能強化](https://thinkit.co.jp/article/9933)

- [[PostgreSQL][MySQL] mysql より psql の補完が好きなので MySQL のテーブルを PostgreSQL から操作してみたよ](http://suzuki.tdiary.net/20130127.html)

- [mysql_fdw](https://github.com/EnterpriseDB/mysql_fdw)

- [外部データラッパによるPostgreSQLの拡張](https://www.slideshare.net/babystarmonja/postgre-sql-11764943)

- [mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する](http://symfoware.blog68.fc2.com/blog-entry-1798.html)

- [[mysql]mySQLへ他ホストから接続できない](http://atatte55.blog62.fc2.com/blog-entry-204.html)

- [MySQLの外部接続の設定](https://qiita.com/irabu1/items/4c08fcbac384e05a90ac)

- [他のサーバに入れない。MySQLで他のサーバからのアクセスを許可する](http://d.hatena.ne.jp/editnuki/20110813/1313177077)