使用FDW从PostgreSQL操作MySQL的表

1 年 ago
逸, 科
3 minutes

我想做的事情

我想从VirtualBox上的PostgreSQL操作Raspberry Pi上的MySQL数据。
似乎可以通过使用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是什么？

外部数据包装器的缩写。
它是一种功能，允许从本地的PostgreSQL操作其他远程的PostgreSQL。
当在PostgreSQL中连接到MySQL等数据库时，标准功能无法实现，因此需要安装mysql_fdw。

使用FDW的步骤

    1. PostgreSQL: 安装mysql_fdw

PostgreSQL: 创建外部服务器

PostgreSQL: 用户映射

PostgreSQL: 创建外部表

MySQL: 设置bind-address

MySQL: 添加权限

安装mysql_fdw

在Ubuntu上运行的工作中使用了PostgreSQL。

获取mysql_fdw的源代码

mysql_fdw的页面在这里。

请进入适当的工作文件夹，并将mysql_fdw从Git上下载到本地。

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

根据MySQL外部数据包装器适用于PostgreSQL – 1. 安装指南，进行操作。

编译

由于我的环境没有满足编译所需的条件，所以我进行了编译前的准备工作。

请使用以下命令进行编译和安装。

make USE_PGXS=1
make USE_PGXS=1 install

在执行make命令时出现了两个错误：
– 缺少make命令
– 缺少postgresql-server-dev-X.Y

将mysql_fdw集成到PostgreSQL中。

在psql中执行以下命令 CREATE EXTENSION 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. 创建外部服务器

 

    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が待ち受けしているポート番号

树莓派可能会改变其分配的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的工作。

設定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:无法连接到MySQL的问题，让我相当困扰。画出网络拓扑图并进行思考是非常重要的。

尝试计算从PostgreSQL到MySQL的表数

在运行着PostgreSQL的Ubuntu上进行操作。

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

我成功地从Raspberry Pi上的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');

错误：无法连接到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)

拒绝访问。
原因是在执行grant查询时，设定了iot@192.168.56.101（Ubuntu的IP地址），而不是iot@192.168.3.2（Mac的IP地址）。

根据网络结构图，本次环境中Ubuntu作为Mac上的虚拟机与外部网络通过NAT连接。因此，即使指定Ubuntu的IP地址，也无法直接从RaspberryPi访问。

请参考

    • Foreign Data Wrapper（FDW）の機能強化

 

    • [PostgreSQL][MySQL] mysql より psql の補完が好きなので MySQL のテーブルを PostgreSQL から操作してみたよ

 

    • mysql_fdw

 

    • 外部データラッパによるPostgreSQLの拡張

 

    • mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する

 

    • [mysql]mySQLへ他ホストから接続できない

 

    • MySQLの外部接続の設定

 

    他のサーバに入れない。MySQLで他のサーバからのアクセスを許可する
✖
广告
将在 10 秒后关闭
bannerAds