PostgreSQL使用MySQL外表的步骤详解(mysql_fdw)

(编辑:jimmy 日期: 2024/12/24 浏览:2)

浅谈

postgres不知不觉已经升到了版本13,记得两年前还是版本10,当然这中间一直期望着哪天能在项目中使用postgresql,现在已实现哈~;

顺带说一下:使用postgresql的原因是它的生态完整,还有一个很重要的点儿是速度快这个在第10版的时 这么说也许还为时过早,

但是在13这一版本下一点儿也不为过,真的太快了,我简单的用500w的数据做聚合,在不建立索引(主键除外)的情况下 执行一个聚合操作,postgres

的速度是mysql的8倍,真的太快了~;好了,这一章节我就聊一聊我实际碰到的问题,就是:跨库查询,这里是用mysql_fdw实现的。

环境准备

  • 一个mysql实例(5.7或8均可)
  • 一个postgres实例(这里使用源码编译安装的13,建议13,11或12也可)
  • 一台linux(以下内容使用的是centos,其它系统也可参考哈)

以下内容仅仅为安装及使用mysql_fdw的教程,具体mysql及postgres怎么安装我就一并略去

准备libmysqlclient

注意:若mysql与postgresql在同一台linux机上,则无需安装mysql工具,请略过本段

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.22-linux-glibc2.17-x86_64-minimal.tar.xz
tar -xvJf mysql-8.0.22-linux-glibc2.17-x86_64-minimal.tar.xz
 chown -R mysql:mysql /usr/local/mysql/
cd mysql-8.0.22-linux-glibc2.17-x86_64-minimal
cp -r ./* /usr/local/mysql/

配置环境变量

配置文件

vi /etc/profile

添加mysql环境变量

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:/usr/local/mysql/bin
export LD_LIBRARY_PATH=MYSQL_HOME/lib:$LD_LIBRARY_PATH

添加postgres环境变量

export PG_HOME=/usr/local/pgsql
export LD_LIBRARY_PATH=$PG_HOME/lib:$MYSQL_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PG_HOME/bin:$MYSQL_HOME/bin:$PATH:.

刷新配置

source /etc/profile

下载并编译mysql_fdw

下载地址:

https://github.com/EnterpriseDB/mysql_fdw/releases

解压

tar -xzvf REL-2_5_5.tar.gz

进入

cd mysql_fdw-REL-2_5_5

编译

make USE_PGXS=1

安装

make USE_PGXS=1 install

重启postgres

安装mysql_fdw 并 配置完成环境变量必须重启postgresql,这个很重要

su postgres
 /usr/local/pgsql/bin/pg_ctl -D /mnt/postgres/data -l logfile stop
 /usr/local/pgsql/bin/pg_ctl -D /mnt/postgres/data -l logfile start
 psql [ or /usr/local/pgsql/bin/psql]

登录到postgres并配置mysql_server

\c YOUR_DB_NAME
CREATE EXTENSION mysql_fdw;
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'HOST', port '3306');
CREATE USER MAPPING FOR YOUR_DB_NAME SERVER mysql_server OPTIONS (username 'USERNAME', password 'PASSWORD');
GRANT USAGE ON FOREIGN SERVER mysql_server TO YOUR_DB_NAME;
GRANT ALL PRIVILEGES ON ods_tianmao_transaction TO YOUR_DB_NAME;

创建外表

创建的外表必须在mysql中有对应的表,否则无法使用(也不会在DB工具中显示)

样例

CREATE FOREIGN TABLE YOUR_TABLE_NAME(
 id numeric(22),
 date date ,
 name varchar(50),
 create_time timestamp 
)SERVER mysql_server OPTIONS (dbname 'YOUR_DB_NAME', table_name 'MYSQL_TABLE_NAME');

删除操作

删除扩展

DROP EXTENSION mysql_fdw CASCADE;

删除mysql_server

DROP SERVER [mysql_server] CASCADE;

删除外表

DROP FOREIGN TABLE [YOUR_FOREIGN_TABLE_NAME] CASCADE;

修改user mapping

ALTER USER MAPPING FOR YOUR_DB_USER SERVER mysql_server OPTIONS (SET password 'PASSWORD');
ALTER USER MAPPING FOR YOUR_DB_USER SERVER mysql_server OPTIONS (SET username 'USERNAME');

最后

想说的是postgresql的外表功能实在是太好用了,建立mysql外表后可直接在posgresql中执行增删改查等操作

更强大的是 还可以执行与postgresql表的连表查询,真香~,省去了应用配置数据源的麻烦。

一句话新闻

一文看懂荣耀MagicBook Pro 16
荣耀猎人回归!七大亮点看懂不只是轻薄本,更是游戏本的MagicBook Pro 16.
人们对于笔记本电脑有一个固有印象:要么轻薄但性能一般,要么性能强劲但笨重臃肿。然而,今年荣耀新推出的MagicBook Pro 16刷新了人们的认知——发布会上,荣耀宣布猎人游戏本正式回归,称其继承了荣耀 HUNTER 基因,并自信地为其打出“轻薄本,更是游戏本”的口号。
众所周知,寻求轻薄本的用户普遍更看重便携性、外观造型、静谧性和打字办公等用机体验,而寻求游戏本的用户则普遍更看重硬件配置、性能释放等硬核指标。把两个看似难以相干的产品融合到一起,我们不禁对它产生了强烈的好奇:作为代表荣耀猎人游戏本的跨界新物种,它究竟做了哪些平衡以兼顾不同人群的各类需求呢?