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;