最近有个 MySql 项目需要转成 PG， 这段时间在做迁移联调， PostgreSQL 9.1 之后支持多种外部表，可以打通与多类数据库的连接，例如 oracle, mysql, nosql 等，同时还可以打通与文本文件的连接，可以在数据库里读文件内容，这里介绍下 PostgreSQL 打通与 Mysql 连接的方法，即 mysql_fdw 的应用。
硬件环境 笔记本虚拟机
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/mysqlexport 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= skytf- skytf- CREATE SERVER 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 数据库的数据了。
总结 利用 mysql_fdw 可以创建 PostgreSQL 与 Mysql 的连接，可以实现 PG 与 Mysql 的数据同步； 在 Mysql 转 PG 项目中，mysql_fdw 是非常有效的方法，具体迁移效率之后再做测试。 原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/237815.html
