postgres中mysql_fdw 扩展的使用

保持热爱奔赴山海

发布于 2019-09-17 15:09:02

1.1K0

发布于 2019-09-17 15:09:02

文章被收录于专栏：饮水机管理员饮水机管理员

mysql_fdw 的作用：用来在PG中快速访问MySQL中的数据。

参考连接： https://www.percona.com/blog/2018/08/24/postgresql-accessing-mysql-as-a-data-source-using-mysql_fdw/

实验版本：

CentOS 7.5

Postgrsql 11.5 编译安装版

SQL 5.7 rpm版

演示环境，MySQL 和 PG部署在同一台机器上，IP地址 192.168.2.4。

1、MySQL中账号授权

在 192.168.2.4 这个 MySQL服务器上创建相关账号和授权 (测试的时候权限放的比较大，实际生产上要严格控制权限)
create user dts@'%' identified by 'dts';
grant select,update,insert,delete on zabbix.* to dts@'%';

2、编译 mysql_fdw 扩展

# 载入环境变量
export PATH=/usr/local/pgsql-11.5/bin:$PATH

# 如果mysql是编译安装的，这里可能还需要载入mysql的环境变量,类似这样 export PATH=/usr/local/mysql:$PATH（我们mysql使用rpm安装，默认的PATH路径可发现，这里就i不用export了）

# 开始编译扩展
cd /home/postgres/
git clone https://github.com/EnterpriseDB/mysql_fdw.git 
cd mysql_fdw
make USE_PGXS=1
make USE_PGXS=1 install


chown postgres.postgres /usr/local/pgsql-11.5/lib/mysql_fdw.so   # 我这里用root账号编译的，需要改下最终的pg文件夹下的 .so 文件的权限

然后，修改pg的配置文件，加入 mysql_fdw这个功能

shared_preload_libraries = 'mysql_fdw'

然后，重启下PG

登录进PG

\c testdb 
testdb=# \d
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | inno_order | table | repuser
(1 row)


# 下面是参考官方github上面的教程实践的：
testdb=# CREATE EXTENSION mysql_fdw;

testdb=# CREATE SERVER mysql_server_db10 FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.2.4', port '3306');

testdb=# CREATE USER MAPPING FOR postgres SERVER mysql_server_db10 OPTIONS (username 'dts', password 'dts');

testdb=# CREATE FOREIGN TABLE screens (
  "screenid" bigint  NOT NULL,
  "name" varchar(255) NOT NULL,
  "hsize" int NOT NULL ,
  "vsize" int NOT NULL ,
  "templateid" bigint DEFAULT NULL,
  "userid" bigint  DEFAULT NULL,
  "private" int NOT NULL )
SERVER mysql_server_db10 OPTIONS (dbname 'zabbix', table_name 'screens');
# 注意： PG中有些字段类型与MySQL不一样，需要在PG上建表的时候注意修改适配下。


testdb=# select * from screens limit 5  ;
 screenid |         name         | hsize | vsize | templateid | userid | private 
----------+----------------------+-------+-------+------------+--------+---------
        3 | System performance   |     2 |     3 |      10001 |        |       0
        4 | Zabbix server health |     2 |     3 |      10047 |        |       0
        5 | System performance   |     2 |     2 |      10076 |        |       0
        6 | System performance   |     2 |     2 |      10077 |        |       0
        7 | System performance   |     2 |     2 |      10075 |        |       0
(5 rows)

实验测了下， mysql_fdw 对于 select update delete insert 都支持，基本上性能还能接受的。

### 其它命令：

删除外部表：

drop foreign table screens;

删除扩展

postgres=# drop foreign table screens;
postgres=# drop user mapping for postgres server mysql_server_db10 ;
postgres=# drop server mysql_server_db10 ;
DROP SERVER
postgres=# drop extension mysql_fdw ;
DROP EXTENSION

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-08-23 ，如有侵权请联系 cloudcommunity@tencent.com 删除

打包