postgresql fdw mysql_mysql同步数据到PostgreSQL(使用mysql_fdw)

1、安装

下载地址：https://github.com/EnterpriseDB/mysql_fdw

修改环境变量：

export MYSQLHOME=/usr/local/mysql

export PGHOME=/home/pg12/pgsql12.4

export LD_LIBRARY_PATH=$PGHOME/lib:$MYSQLHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib

export PATH=$PGHOME/bin:$MYSQLHOME/bin:$PATH:.

解压安装：

[root@oracle ~]# unzip mysql_fdw-master.zip

[root@oracle ~]# cd mysql_fdw-master

[root@oracle ~]# make USE_PGXS=1

[root@oracle ~]# make USE_PGXS=1 install

在pg中创建mysql_fdw插件：

bill=# create extension mysql_fdw ;

CREATE EXTENSION

这一步可能会出现如下报错：

bill=# create extension mysql_fdw ;

ERROR: failed to load the mysql query:

libmysqlclient.so: cannot open shared object file: No such file or directory

HINT: export LD_LIBRARY_PATH to locate the library

如果是源码编译安装的MySQL可能会出现这个问题，需要设置LD_LIBRARY_PATH，设置完之后再重新启动pg数据库。

2、使用

mysql中测试表：

mysql> select * from t_mysql;

+------+---------------+

| id | info |

+------+---------------+

| 1 | this is mysql |

+------+---------------+

1 row in set (0.00 sec)

pg中创建对应的forein server和外部表：

bill=# CREATE SERVER mysql_server

bill-# FOREIGN DATA WRAPPER mysql_fdw

bill-# OPTIONS (host '127.0.0.1', port '3306');

CREATE SERVER

bill=# CREATE USER MAPPING FOR bill

bill-# SERVER mysql_server

bill-# OPTIONS (username 'root', password 'root.123');

CREATE USER MAPPING

bill=# CREATE FOREIGN TABLE mysql_foreign_table1(

bill(# id int,

bill(# info text)

bill-# SERVER mysql_server

bill-# OPTIONS (dbname 'bill', table_name 't_mysql');

CREATE FOREIGN TABLE

查看：

数据已经同步过来

bill=# select * from mysql_foreign_table1 ;

id | info

----+---------------

1 | this is mysql

(1 row)

我们还可以直接IMPORT整个库生成本地的外部表。

例如：

bill=# IMPORT FOREIGN SCHEMA bill FROM SERVER mysql_server into bill;

IMPORT FOREIGN SCHEMA

可以发现，mysql中所有表都import过来了：

bill=# \dE

List of relations

Schema | Name | Type | Owner

--------+----------------------+---------------+-------

bill | mysql_foreign_table1 | foreign table | bill

bill | t_mysql | foreign table | bill

bill | test | foreign table | bill

(3 rows)

写入数据：

我们可以直接在pg中写数据到mysql数据库，但是mysql的表必须有主键才可以：

bill=# insert into mysql_foreign_table1 values(2,'this is postgres');

ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

在mysql中添加主键：

mysql> alter table t_mysql add primary key(id);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

再次插入就可以成功了：

bill=# insert into mysql_foreign_table1 values(2,'this is postgres');

INSERT 0 1

我们还可以查看在pg中查询mysql中的表的执行计划会是什么样的：

bill=# explain verbose select count(*) from mysql_foreign_table1 ;

QUERY PLAN

------------------------------------------------------------------------------------------

Aggregate (cost=1012.50..1012.51 rows=1 width=8)

Output: count(*)

-> Foreign Scan on bill.mysql_foreign_table1 (cost=10.00..1010.00 rows=1000 width=0)

Output: id, info

Local server startup cost: 10

Remote query: SELECT NULL FROM `bill`.`t_mysql`

(6 rows)

bill=# explain verbose select * from mysql_foreign_table1 where id=1;

QUERY PLAN

-------------------------------------------------------------------------------------

Foreign Scan on bill.mysql_foreign_table1 (cost=10.00..1010.00 rows=1000 width=36)

Output: id, info

Local server startup cost: 10

Remote query: SELECT `id`, `info` FROM `bill`.`t_mysql` WHERE ((`id` = 1))

(4 rows)