postgres中mysql_fdw 扩展的使用

  • 2019 年 10 月 4 日
  • 笔记

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

VirMach 便宜 VPS

QNews

QNews