mysql fdw_postgresql的mysql_fdw扩展安装及使用

step 1：install mysql_fdw

yum search mysql_fdw10 得知存在mysqlfdw10.x86_64包

yum install mysql_fdw_10

在安装过程中，发现不能使用mysql-community，必须要用mariadb才行。

卸载mysql-community，安装mariadb

step 2: create extension mysql_fdw in postgresql

在postgresql下安装mysql_fdw

su postgres

psql

#create entension mysql_fdw时出错，提示找不到libmysqlclient.so

查找路径find / -name libmysqlclient* 然后用一个最高版本18.0.0创建软连接

ln -s /usr/lib64/libmysqlclient.so.18.0.0 /usr/pgsql-10/lib/libmysqlclient.so

然后在create extension mysql_fdw;即可成功 \dx可以看到

step 3: create server

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

select * from pg_foreign_server;

step 4: create user and user mapping

create user user_mysql_zp_server encrypted password 'bjznhhrt';

create user mapping for user_mysql_zp_server

server mysql_zp_server

options (username 'root', password 'bjznhhrt');

step 5: 创建外部表(foreign table)

CREATE FOREIGN TABLE t_mysql_zp_server_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_zp_server_mysql_user to usr_mysql_zp_server;

select * from t_tmp_mysql_mysql_user;