PostgreSQL的mysql_fdw使用

为什么80%的码农都做不了架构师？>>>   

要迁移Mysql的数据到PostgreSQL上来，有很多的办法，比如一些工具navicat,jmyetl等，但通常是要收费，而且效率和稳定性不一定好，最保守的办法是自己写一些脚本，当然也有一些开源的工具，比如要介绍的mysql_fdw，使用时本地需要建mysql软件和postgresql数据库，下面的测试例子是mysql和pg装在一起的。

mysql_fdw是一款基于BSD协议的开源工具，目前还不是postgresql内置，最新版本是1.0.1。

一、环境
CentOS 6.3
PostgreSQL 9.3.4
Host 10.1.11.73

二、Mysql的数据准备

mysql> create database db_kenyon;
Query OK, 1 row affected (0.01 sec)

mysql> create table tbl_kenyon(id int,vname varchar(48));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl_kenyon values(1,'test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(2,'kenyon');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(null,'it\'s null');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(4,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tbl_kenyon;
+------+-----------+
| id   | vname     |
+------+-----------+
|    1 | test      |
|    2 | kenyon    |
| NULL | it's null |
|    4 | NULL      |
+------+-----------+
4 rows in set (0.00 sec)

mysql> grant select on db_kenyon.tbl_kenyon to 'usr_kenyon'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select host,user,password from mysql.user;
+-----------+------------+-------------------------------------------+
| host      | user       | password                                  |
+-----------+------------+-------------------------------------------+
| localhost | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| db1       | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | usr_kenyon | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------+-------------------------------------------+
5 rows in set (0.00 sec)

三、 安装使用 mysql_fdw

下载地址： http://pgxn.org/dist/mysql_fdw/
安装时使用postgres用户，参考README，有两步
make USE_PGXS=1
make USE_PGXS=1 install
安装完可能会有的异常，ERROR: could not load library "/home/postgres/lib/mysql_fdw.so": libmysqlclient.so.18: cannot open shared object file: No such file or directory 将mysql下的libmysqlclient.so.18文件拷贝到/home/postgres/lib下面或者做个软连接就可以了

1.创建extension扩展

postgres=# create extension mysql_fdw ;
CREATE EXTENSION

2.创建server

postgres=# CREATE SERVER mysql_svr  FOREIGN DATA WRAPPER mysql_fdw  OPTIONS (address '10.1.11.73', port '3306');
CREATE SERVER

3.创建一个或多个外部表(foreign table)

postgres=# CREATE FOREIGN TABLE pg_mysql_tbl1 (id integer,
    name text)
    SERVER mysql_svr
    OPTIONS (table 'db_kenyon.tbl_kenyon');
CREATE FOREIGN TABLE

postgres=# CREATE FOREIGN TABLE pg_mysql_tbl2 (
    id integer,
   vname text)
   SERVER mysql_svr
   OPTIONS (query 'SELECT id, vname FROM db_kenyon.tbl_kenyon WHERE id<>2;');
CREATE FOREIGN TABLE

4.创建PostgreSQL的fdw查询用户，pg_hba配置略

postgres=# create user u_select ENCRYPTED PASSWORD '123456';
CREATE ROLE

5.创建用户匹配关系(user mapping),用户为远程mysql的用户密码

CREATE USER MAPPING FOR u_select
    SERVER mysql_svr 
    OPTIONS (username 'usr_kenyon', password '123456');

6.查询数据，需要u_select登录，不然会报user mapping not found

postgres=> select * from pg_mysql_tbl1 ;
 id |   name    
----+-----------
  1 | test
  2 | kenyon
    | it's null
  4 | 
(4 rows)

postgres=> select * from pg_mysql_tbl2;
 id | vname 
----+-------
  1 | test
  4 | 
(2 rows)

四、删除扩展

postgres=# drop foreign table pg_mysql_tbl1;
DROP FOREIGN TABLE
postgres=# drop foreign table pg_mysql_tbl2;
DROP FOREIGN TABLE

postgres=# drop user mapping for u_select server mysql_svr ;
DROP USER MAPPING

postgres=# drop server mysql_svr ;
DROP SERVER

postgres=# drop extension mysql_fdw ;
DROP EXTENSION

五、总结
1.这个工具在ETL迁移Mysql到postgresql时比较有用，跨平台，跨数据库，而且简单方便
2.目前该工具还不是内置版本，引起的风险要注意，比如mysql和pg库端表字段后类型不一样，会产生乱码


六、参考：
1.http://pgxn.org/dist/mysql_fdw/
2.http://francs3.blog.163.com/blog/static/40576727201111211324599/?suggestedreading

转载于:https://my.oschina.net/Kenyon/blog/233160