概述

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

编译安装mysql_fdw

下载插件

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

编译安装

  1. unzip mysql_fdw-master.zip
  2. cd mysql_fdw-master

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

  1. export PATH=/usr/local/pgsql/bin/:$PATH
  2. export PATH=/usr/mysql/bin/:$PATH

安装

  1. make USE_PGXS=1
  2. make USE_PGXS=1 install

如果出错,安装mysql客户端

  1. yum install mysql-devel

配置mysql_fdw

创建extension

  1. create extension mysql_fdw ;

查看插件

  1. select * from pg_extension ;
  2. \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

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

注意自己的版本

创建server

  1. CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (HOST '192.168.1.111', PORT '3320');

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

创建user mapping

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

如果想修改

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

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

  1. alter user root@localhost identified with mysql_native_password by 'password';
0 评论  
添加一条新评论