PostgreSQL插件：MySQL_fdw 安装使用

概述

postgresql的外部表插件MySQL_fdw 可以实现pg与MySQL的数据同步功能。是通过pg数据库的外部表功能实现，搭建也十分简单。

编译安装mysql_fdw

下载插件

https://github.com/EnterpriseDB/mysql_fdw
使用git，或者直接下载

编译安装

unzip  mysql_fdw-master.zip
cd  mysql_fdw-master

要有pg和mysql的config命令（下列需按实际安装目录）

export PATH=/usr/local/pgsql/bin/:$PATH
export PATH=/usr/mysql/bin/:$PATH

安装

make USE_PGXS=1
make USE_PGXS=1 install

如果出错，安装mysql客户端

yum  install  mysql-devel

配置mysql_fdw

创建extension

create extension mysql_fdw ;

查看插件

select * from pg_extension ;
\dx

创建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_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (HOST '192.168.1.111', PORT '3320');

填进相应的mysql数据库的信息

创建user mapping

CREATE USER MAPPING FOR PUBLIC SERVER mysql_server OPTIONS (username 'third_admin', password 'eisoo.com');

如果想修改

alter user MAPPING FOR public server mysql_server options ( set  password 'eisoo.com');

注：一些高版本的mysql需要修改密码策略
将mysql的密码策略改为mysql_native_password

alter user root@localhost identified with mysql_native_password by 'password';