PostgreSQL外部数据插件:mysql_fdw

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 概述PostgreSQL具有插件功能,通过不同的插件拓展,实现数据库本身不包含的功能,以满足用户的需求。mysql_fdw 就是一个强大的外部表功能,所谓外部表,就是在PG数据库中通过SQL访问外部数据源数据,就像访问本地数据库一样,下面就来测试一下使用mysql_fdw 来访问mysql中的数...

概述

PostgreSQL具有插件功能,通过不同的插件拓展,实现数据库本身不包含的功能,以满足用户的需求。mysql_fdw 就是一个强大的外部表功能,所谓外部表,就是在PG数据库中通过SQL访问外部数据源数据,就像访问本地数据库一样,下面就来测试一下使用mysql_fdw 来访问mysql中的数据

安装使用步骤

下载安装插件

可以直接下载rpm包进行安装,编译需要各种依赖,比较麻烦,下面链接,选择mysql_fdw 的包下载
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/

下载完成后安装:

[root@stephen ~]# rpm -ivh mysql_fdw_11-2.5.0-1.rhel7.x86_64.rpm 
Preparing... ################################# [100%]
Updating / installing...
   1:mysql_fdw_11-2.5.0-1.rhel7 ################################# [100%]

安装完成之后创建拓展:

postgres=# create extension mysql_fdw ;
CREATE EXTENSION
postgres=# \dx
                            List of installed extensions
   Name | Version | Schema | Description                    
-----------+---------+------------+--------------------------------------------------
 mysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL server
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

可以看到插件拓展就安装完成了

创建server

在PG端创建sever,外部服务定义了具体外部数据源的连接信息,如果外部源是数据库,通常会包含数据库的IP,端口号,数据库名称等信息,如下:

postgres=# CREATE SERVER mysql_server_test FOREIGN DATA WRAPPER mysql_fdw OPTIONS (HOST '172.16.21.167', PORT '3306');
CREATE SERVER

postgres=# \des
               List of foreign servers
       Name | Owner | Foreign-data wrapper 
-------------------+----------+----------------------
 mysql_server_test | postgres | mysql_fdw
(1 row)

创建用户映射

OPTIONS 是指外部拓展的选项,指定了访问外部数据标的本地用户和远程用户信息

postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_server_test OPTIONS (username 'root', password 'xxxxx');
CREATE USER MAPPING

如果想修改

alter user MAPPING FOR public server mysql_server options ( set password 'xxxxxx');

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

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

创建外部表

理论上外部表的字段只要包含于远程mysql表字段内就可以,但是一般的,都建议将外部表与远程表的字段一致。但是mysql和pg字段类型有时会有些许区别,需要确认好字段类型,如下:

postgres=# create foreign table test01(id int,name text,age int,wherefrom text ) server mysql_server_test options(dbname 'test',table_name 'test01');
CREATE FOREIGN TABLE

postgres=# \d
             List of relations
 Schema | Name | Type | Owner   
--------+--------+---------------+----------
 public | test01 | foreign table | postgres
(1 row)

可以看到表的类型是外部表,和普通的表有区别

创建完成后,时就可以在PG端访问这个表

postgres=# select * from test01 ;
 id | name | age | wherefrom 
----+----------+-----+-----------
  1 | dwqdw | 34 | dadsa
  2 | dwdsaqdw | 24 | dsadadsa
  3 | dwdsaqdw | 20 | dsadadsa
  4 | dwdsdadw | 20 | dsadaadsa
(4 rows)

此时mysql端发生的变化

mysql> insert into test01 values(122,'测试',20,'再次测试');
Query OK, 1 row affected (0.01 sec)

PG 再次查询时这边马上就能看到

postgres=# select * from test01 ;
 id | name | age | wherefrom 
-----+----------+-----+-----------
   1 | dwqdw | 34 | dadsa
   2 | dwdsaqdw | 24 | dsadadsa
   3 | dwdsaqdw | 20 | dsadadsa
   4 | dwdsdadw | 20 | dsadaadsa
 122 | 测试 | 20 | 再次测试
(5 rows)

反写数据

一般情况下,如果没有唯一键限制,反写数据就会报错

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

如果想反写到mysql,需要在mysql上添加表的限制

ALTER TABLE test01 ADD CONSTRAINT idx_id UNIQUE (id);(示例)

我这边是ID已经是主键唯一键了,所以直接能使用

postgres=# insert into test01 values(123,'反写测试',221,'再次测试');
INSERT 0 1

mysql> select * from test01;
+-----+--------------+------+--------------+
| id | name | age | wherefrom |
+-----+--------------+------+--------------+
| 1 | dwqdw | 34 | dadsa |
| 2 | dwdsaqdw | 24 | dsadadsa |
| 3 | dwdsaqdw | 20 | dsadadsa |
| 4 | dwdsdadw | 20 | dsadaadsa |
| 122 | 测试 | 20 | 再次测试 |
| 123 | 反写测试 | 221 | 再次测试 |
+-----+--------------+------+--------------+
6 rows in set (0.00 sec)

事实上现在不仅仅是支持insert语句,update与delete语句均支持,前提是提供给PG的mysql用户是有这些权限的。

物化数据

mysql_fdw 实现的一个关键特性就是支持持久连接的能力。查询执行后,不会删除与远程MySQL的连接。相反,它保留来自同一会话的下次查询连接。然而,在某些情况下,因为网络查询等原因不能及时查询数据,则可以考虑在本地实现数据保留。可以通过外部表创建物化视图,如下

postgres=# CREATE MATERIALIZED VIEW test_view as select * from test01;
SELECT 6
postgres=# \d
                 List of relations
 Schema | Name | Type | Owner   
--------+-----------+-------------------+----------
 public | test01 | foreign table | postgres
 public | test_view | materialized view | postgres
(2 rows)

postgres=# select * from test_view;
 id | name | age | wherefrom 
-----+----------+-----+-----------
   1 | dwqdw | 34 | dadsa
   2 | dwdsaqdw | 24 | dsadadsa
   3 | dwdsaqdw | 20 | dsadadsa
   4 | dwdsdadw | 20 | dsadaadsa
 122 | 测试 | 20 | 再次测试
 123 | 反写测试 | 221 | 再次测试
(6 rows)

可以将刷新任务放到定时任务中,定时去刷新视图

REFRESH MATERIALIZED VIEW test_view;

新特性

如今mysql_fdw 已经支持
Where子句下推:
最新版本将外部表where子句下推到外部服务器。外部表上的where条件将在外部服务器上执行,因此将有更少的行传递给PostgreSQL。这是一个性能功能。
列下推:
以前的版本是从目标外表中获取所有列。最新版本执行列下推,仅返回属于选择目标列表的列。这是一个性能功能。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
关系型数据库 MySQL Java
【MySQL+java+jpa】MySQL数据返回项目的感悟
【MySQL+java+jpa】MySQL数据返回项目的感悟
45 1
|
2月前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
209 0
|
2月前
|
SQL 前端开发 关系型数据库
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
53 0
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
|
2月前
|
关系型数据库 MySQL 数据库
mysql 里创建表并插入数据
【10月更文挑战第5天】
136 1
|
2月前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
51 3
|
27天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
131 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
27天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
58 14
|
29天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
53 9
|
2月前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
1月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
137 4