1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL 新特性之六 : mysql_fdw 实践

• 数据库

最近有个 MySql 项目需要转成 PG， 这段时间在做迁移联调， PostgreSQL 9.1 之后支持多种外部表，可以打通与多类数据库的连接，例如 oracle, mysql, nosql 等，同时还可以打通与文本文件的连接，可以在数据库里读文件内容，这里介绍下 PostgreSQL 打通与 Mysql 连接的方法，即 mysql_fdw 的应用。

硬件环境

笔记本虚拟机
PostgreSQL IP 192.168.1.26/1921
MysSQL IP 192.168.1.25/3306
备注：PostgreSQL 版本 9.1 or later ，PostgreSQL 和 Mysql 安装本文省略。

MySQL库准备

MySQL 库上创建数据库和表，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
mysql> create database skytf;  
Query OK, 1 row affected (0.02 sec)
mysql> use skytf;  
Database changed

mysql> create table test_1 (id integer,name varchar(32));  
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_1 values (1,'a');  
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_1 values (2,'b');  
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_1 values (3,'c');  
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_1;  
+------+------+  
| id | name |  
+------+------+  
| 1 | a |  
| 2 | b |  
| 3 | c |  
+------+------+  
3 rows in set (0.00 sec)
  
mysql> grant select on skytf.* to ['skytf'@'192.168.%.%'](mailto:'skytf'@'192.168.%.%') identified by 'skytf';  
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

配置 PostgreSQL

4.1 增加 PostgreSQL 环境变量

1
2
3
4
 
export PGHOME=/opt/pgsql9.1  
export MYSQLHOME=/opt/mysql
export LD_LIBRARY_PATH=$MYSQLHOME/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib  
export PATH=$MYSQLHOME/bin:$PGHOME/bin:/opt/pgbouncer1.4.2/bin:$PATH:.

备注： 修改好后, 执行 source .bash_profile。

mysql_fdw 部署

mysql_fdw 下载
下载地址： http://pgxn.org/dist/mysql_fdw/

之后解压到目录 /opt/soft_bak/mysql_fdw-1.0.0

编译并安装

1
2
3
 
# cd /opt/soft_bak/mysql_fdw-1.0.0 
# make USE_PGXS=1  
# make USE_PGXS=1 install

详细信息

1
2
3
4
5
6
7
8
9
10
11
 
[root@pgb mysql_fdw-1.0.0]# make USE_PGXS=1gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/mysql/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o mysql_fdw.o mysql_fdw.c  
mysql_fdw.c: In function ‘mysqlPlanForeignScan’:  
mysql_fdw.c:370: 警告：此函数中的 ‘rows’ 在使用前可能未初始化  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o mysql_fdw.so mysql_fdw.o -L/opt/pgsql9.1/lib -L/usr/lib -Wl,-rpath,'/opt/pgsql9.1/lib',--enable-new-dtags -rdynamic -L/opt/mysql/lib -lmysqlclient -lz -lcrypt -lnsl -lm -lmygcc
[root@pgb mysql_fdw-1.0.0]# make USE_PGXS=1 install  
/bin/mkdir -p '/opt/pgsql9.1/lib'  
/bin/mkdir -p '/opt/pgsql9.1/share/extension'  
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 mysql_fdw.so '/opt/pgsql9.1/lib/mysql_fdw.so'  
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mysql_fdw.control '/opt/pgsql9.1/share/extension/'  
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mysql_fdw--1.0.sql '/opt/pgsql9.1/share/extension/'  
[root@pgb mysql_fdw-1.0.0]# ll

备注：安装成功后会在目录 /opt/pgsql9.1/share/extension/ 产生 mysql_fdw.so , mysql_fdw.control , mysql_fdw–1.0.sql 文件。

创建 extension mysql_fdw

1
2
 
skytf=# CREATE EXTENSION mysql_fdw;  
CREATE EXTENSION

可能出现的ERROR

1
2
3
4
5
 
ERROR: could not open extension control file "/opt/pgsql9.1/share/extension/mysql_mdw.control": No such file or directory  
postgres=#
ERROR: could not stat file "/opt/pgsql9.1/share/extension/mysql_mdw--1.0.sql": No such file or directory  
postgres=# create extension mysql_mdw;
ERROR: could not load library "/opt/pgsql9.1/lib/mysql_fdw.so": libmysqlclient.so.16: cannot open shared object file: No such file or directory

备注：在创建 extension 时可能出现以上 ERROR， 这时只要将复制一份以上文件即可。

创建 server 赋权给普通用户

1
2
3
4
5
6
7
 
skytf=# CREATE SERVER mysql_svr_25  
skytf-# FOREIGN DATA WRAPPER mysql_fdw  
skytf-# OPTIONS (address '192.168.1.25', port '3306');  
CREATE SERVER

skytf=# grant usage on foreign server mysql_svr_25 to skytf;  
GRANT

备注：在赋权限后，普通用户 skytf 就可以使用 foreign server mysql_svr_25 创建外部表了。

查看 server

1
2
3
4
5
6
7
8
 
skytf=# /c skytf skytf  
You are now connected to database "skytf" as user "skytf".
  
skytf=> /des  
 List of foreign servers  
 Name | Owner | Foreign-data wrapper  
--------------+----------+----------------------  
mysql_svr_25 | postgres | mysql_fdw

创建匹配用户 ( mapping user )

1
2
3
4
 
skytf=# CREATE USER MAPPING FOR skytf  
skytf-# SERVER mysql_svr_25  
skytf-# OPTIONS (username 'skytf', password 'skytf');  
CREATE USER MAPPING

备注：如果不创建 mapping user ， 会出现 ERROR: user mapping not found for “skytf” 类似信息。

查看匹配用户

1
2
3
4
5
6
 
skytf=> /deu  
 List of user mappings  
 Server | User name  
--------------+-----------  
mysql_svr_25 | skytf  
(1 row)

创建外部表

1
2
3
4
5
6
 
skytf=> CREATE FOREIGN TABLE test_1 (  
skytf(> id integer,  
skytf(> name character varying(20)  
skytf(> ) SERVER mysql_svr_25  
skytf-> OPTIONS (database 'skytf', table 'test_1');  
CREATE FOREIGN TABLE

备注：到了这里就已经打通了 Mysql 和 PostgreSQL 的连接。

查询

1
2
3
4
5
6
7
 
skytf=> select * from test_1;  
id | name  
----+------  
 1 | a  
 2 | b  
 3 | c  
(3 rows)

备注：果然可以查到了 Mysql 数据库的数据了。

总结

  1. 利用 mysql_fdw 可以创建 PostgreSQL 与 Mysql 的连接，可以实现 PG 与 Mysql 的数据同步；
  2. 在 Mysql 转 PG 项目中，mysql_fdw 是非常有效的方法，具体迁移效率之后再做测试。

原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/237815.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论