centos 6.8 + pgsql 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 USE_PGXS=1
# make USE_PGXS=1 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的mysql_fdw用户

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
;

set role usr_mysql_eastmoney_server1
;

select *
from t_tmp_mysql_mysql_user
;

reset role
;

参考
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