centos 6.8 + postgresql 9.6 + mysql_fdw

本次使用 mysql_fdw 监控 mysql数据库的一些信息。

rpm 安装

# yum install mysql_fdw_96.x86_64
# rpm -ivh /tmp/mysql-connector-c-devel-6.1.11-1.el6.x86_64.rpm
# ls -l /usr/pgsql-9.6/lib |grep -i mysql
# ls -l /usr/pgsql-9.6/share/extension |grep -i mysql

编译安装

安装 PostgreSQL 9.6.4，rpm或者编译

# rpm -qa |grep -i postgres
  postgresql96-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-pltcl-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-contrib-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-libs-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-server-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-devel-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-test-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-tcl-2.3.1-1.rhel6.x86_64
  postgresql96-plpython-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-debuginfo-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-tcl-debuginfo-2.3.1-1.rhel6.x86_64
  postgresql96-odbc-09.06.0410-1PGDG.rhel6.x86_64
  postgresql96-plperl-9.6.4-1PGDG.rhel6.x86_64
  postgresql96-docs-9.6.4-1PGDG.rhel6.x86_64

编译 mysql_fdw

# cd /tmp
# unzip /tmp/mysql_fdw-2.1.2.zip
# cd /tmp/mysql_fdw-2.1.2

认真阅读 README.md

To compile the [MySQL][1] foreign data wrapper, MySQL’s C client library is needed. This library can be downloaded from the official [MySQL website][1].

都需要安装

# rpm -ivh /tmp/mysql-connector-c-devel-6.1.11-1.el6.x86_64.rpm

本地安装一个mysql软件

root 用户下 增加 PostgreSQL，MySQL 环境变量

export PGHOME=/usr/pgsql-9.6
export MYSQLHOME=/usr/mysql

export   LD_LIBRARY_PATH=\$MYSQLHOME/lib:\$PGHOME/lib:/lib64:/usr/lib64:/usr/lib64/mysql:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=\$MYSQLHOME/bin:\$PGHOME/bin:$PATH:. 

make、make install

 # USE_PGXS=1 make 
 # USE_PGXS=1 make install

无错误提示，成功安装后，检查如下文件

# ls -l /usr/pgsql-9.6/lib |grep -i mysql
# ls -l /usr/pgsql-9.6/share/extension |grep -i mysql

使用mysql_fdw

创建extension

create extension mysql_fdw ;

select * from pg_extension ;

创建extension时可能出现的错误
SQL Error [HV00L]: ERROR: failed to load the mysql query:
libmysqlclient.so: cannot open shared object file: No such file or directory
建议：export LD_LIBRARY_PATH to locate the library
org.postgresql.util.PSQLException: ERROR: failed to load the mysql query:
libmysqlclient.so: cannot open shared object file: No such file or directory
建议：export LD_LIBRARY_PATH to locate the library

需要创建一个link

# ln -s /usr/lib64/mysql/libmysqlclient.so.16.0.0 /usr/pgsql-9.6/lib/libmysqlclient.so


创建server

CREATE SERVER mysql_eastmoney_server1  
FOREIGN DATA WRAPPER mysql_fdw  
OPTIONS (host 'x.x.x.x', port '3306')

创建postgresql的用户

如果已经有用户则跳过这一步

create user usr_mysql_eastmoney_server1 ENCRYPTED PASSWORD '12345';

创建user mapping

用户为远程mysql的用户密码

CREATE USER MAPPING FOR usr_mysql_eastmoney_server1
SERVER mysql_eastmoney_server1
OPTIONS (username 'root', password '12345');

创建外部表(foreign table)

CREATE FOREIGN TABLE t_mysql_eastmoney_server1_mysql_user (
   host varchar,
   "user" varchar,
   password varchar)
SERVER mysql_eastmoney_server1
OPTIONS ( dbname 'mysql', table_name 'user')
;

grant select,insert,update,delete on t_mysql_eastmoney_server1_mysql_user to usr_mysql_eastmoney_server1
;
select *
from t_tmp_mysql_mysql_user
;

参考
http://pgxn.org/dist/mysql_fdw/
https://github.com/EnterpriseDB/mysql_fdw

https://dev.mysql.com/downloads/
http://francs3.blog.163.com/blog/static/40576727201111211324599/?suggestedreading