PostgreSQL通过mysql_fdw访问MySQL数据库

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

Mysql与PostgreSQL的安装过程省略。

为简便起见,把MySQL和PostgreSQL都安装在一个机器上,然后在此机器上(准确地说是在PostgreSQL运行的机器上)安装mysql_fdw:

首先是下载 mysql_fdw:

http://pgxn.org/dist/mysql_fdw/

mysql_fdw-1.0.1.zip

然后是解压和安装:

[root@server mysql_fdw-1.0.1]# pwd
/soft/fdw/mysql_fdw-1.0.1
[root@server mysql_fdw-1.0.1]# ls
META.json  Makefile  README  mysql_fdw--1.0.sql  mysql_fdw.c  mysql_fdw.control 

运行 make 和 make install,其README文件说得很清楚:

复制代码
[root@server mysql_fdw-1.0.1]# cat README
MySQL FDW for PostgreSQL 9.1+
==============================

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for
the MySQL.

This code is experimental, and largely intended as a pet project for me
to experiment with and learn about FDWs in PostgreSQL.

By all means use it, but do so entirely at your own risk! You have been
warned!

Building
--------

Install MySQL, or just the C client library, and Once that's done, the 
extension can be built with:

PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 
sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install

(assuming you have PostgreSQL 9.1 in /usr/local/pgsql and MySQL in 
/usr/local/mysql).

I've tested on Mac OS X 10.7 only, but other *nix's should also work.
I haven't tested on Windows, but the code should be good on MinGW.

Limitations
-----------

- No attempt is made to pushdown quals to MySQL.

- The MySQL connection used to plan queries isn't currently reused
  during execution.

Usage
-----

The following parameters can be set on a MySQL foreign server:

address:        The address or hostname of the MySQL server.
                Default: 127.0.0.1

port:           The port number on which the MySQL server is listening.
                Default: 3306

The following parameter can be set on a MySQL foreign table:

database:       The name of the MySQL database to query.
                Default: NULL

query:          An SQL query to define the data set on the MySQL server.

table:          The name of a table (quoted and qualified as required)
                on the MySQL table.

Note that the query and table paramters are mutually exclusive. Using
query can provide either a simple way to push down quals (which of
course is fixed at definition time), or to base remote tables on 
more complex SQL queries.

The following parameter can be set on a user mapping for a MySQL
foreign server:

username:       The username to use when connecting to MySQL
                Default <none>

password:       The password to authenticate to the MySQL server with.
                Default: <none>

Example
-------

-- Install the extension
CREATE EXTENSION mysql_fdw;

-- Create the foreign server, a pointer to the MySQL server.
CREATE SERVER mysql_svr 
    FOREIGN DATA WRAPPER mysql_fdw 
    OPTIONS (address '127.0.0.1', port '3306');

-- Create one or more foreign tables on the MySQL server. The first of 
-- these maps to a remote table, whilst the second uses an SQL query.
CREATE FOREIGN TABLE employees (
    id integer,
    name text,
    address text)
    SERVER mysql_svr
    OPTIONS (table 'hr.employees');

CREATE FOREIGN TABLE overtime_2010 (
    id integer,
    employee_id integer,
    hours integer)
    SERVER mysql_svr
    OPTIONS (query 'SELECT id, employee_id, hours FROM hr.overtime WHERE year = 2010;');

-- Create a user mapping to tell the FDW the username/password to 
-- use to connect to MySQL, for PUBLIC. This could be done on a per-
-- role basis.
CREATE USER MAPPING FOR PUBLIC 
    SERVER mysql_svr 
    OPTIONS (username 'dpage', password '');

-- 
Dave Page
dpage@pgadmin.org
[root@server mysql_fdw-1.0.1]# 
复制代码

 

复制代码
[root@server mysql_fdw-1.0.1]# PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/usr/local/mysql/include -I. -I. -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_GNU_SOURCE   -c -o mysql_fdw.o mysql_fdw.c
mysql_fdw.c: In function 'mysqlPlanForeignScan':
mysql_fdw.c:395: 警告: 'rows' may be used uninitialized in this function
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o mysql_fdw.so mysql_fdw.o -L/usr/local/pgsql/lib  -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags  -L/usr/local/mysql/lib -lmysqlclient -lpthread -lm -lrt -ldl 
[root@server mysql_fdw-1.0.1]# sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/sh /usr/local/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  mysql_fdw.so '/usr/local/pgsql/lib/mysql_fdw.so'
/bin/sh /usr/local/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mysql_fdw.control '/usr/local/pgsql/share/extension/'
/bin/sh /usr/local/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mysql_fdw--1.0.sql  '/usr/local/pgsql/share/extension/'
[root@server mysql_fdw-1.0.1]# 
复制代码

检查一下 mysql_fdw.so文件,是否出现在 /usr/local/pgsql/lib 目录下。

 

然后,分别启动mysql和 postgresql:

[root@server ~]# mysqld_safe &
[1] 3223
[root@server ~]# 130918 09:38:14 mysqld_safe Logging to '/usr/local/mysql/data/server.gao.err'.
130918 09:38:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

 

复制代码
[root@server ~]# su - postgres
[postgres@server ~]$ pwd
/home/postgres
[postgres@server ~]$ cd /usr/local/pgsql
[postgres@server pgsql]$ ./bin/pg_ctl -D ./data start
server starting
[postgres@server pgsql]$ LOG:  database system was shut down at 2013-09-13 13:36:47 CST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
复制代码

 

然后,在mysql客户端,执行以下命令来创建表以及访问该表的用户:

复制代码
[root@server ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> select host,user from mysql.user;
+------------+--------+
| host       | user   |
+------------+--------+
| %          | usrabc |
| 127.0.0.1  | root   |
| ::1        | root   |
| localhost  |        |
| localhost  | root   |
| server.gao |        |
| server.gao | root   |
+------------+--------+
7 rows in set (0.08 sec)

mysql> select * from example;
ERROR 1146 (42S02): Table 'mysql.example' doesn't exist
mysql> CREATE TABLE example ( id INT,data VARCHAR(100) );
Query OK, 0 rows affected (0.05 sec)

mysql> quit
Bye
[root@server ~]# mysql -uusrabc -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'usrabc'@'localhost' (using password: YES)
[root@server ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'usrabc'@'localhost' identified by 'usrabc';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, DELETE,UPDATE ON *.* TO 'usrabc'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 
复制代码

 

 

然后,开始在PostgreSQL端,建立FDW:

复制代码
[postgres@server pgsql]$ ./bin/psql
psql (9.1.2)
Type "help" for help.

postgres=# CREATE EXTENSION mysql_fdw;
ERROR:  extension "mysql_fdw" already exists
postgres=# CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '127.0.0.1', port '3306');
CREATE SERVER

postgres=# CREATE FOREIGN TABLE example (id INT,data VARCHAR(100) ) SERVER mysql_svr
postgres-#     OPTIONS (table 'mysql.example');
CREATE FOREIGN TABLE
postgres=# 
postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'usrabc', password 'usrabc');
CREATE USER MAPPING
postgres=# 
postgres=# select * from example;
ERROR:  failed to connect to MySQL: Access denied for user 'usrabc'@'localhost' (using password: YES)
postgres=# 
postgres=# select * from example;
 id | data 
----+------
(0 rows)

postgres=# 
复制代码

 

 

此时,在mysql端,增加数据:

复制代码
mysql> insert into mysql.example values(123,'11111');
Query OK, 1 row affected (0.01 sec)

mysql> select * from example;
ERROR 1046 (3D000): No database selected
mysql> select * from mysql.example;
+------+-------+
| id   | data  |
+------+-------+
|  123 | 11111 |
+------+-------+
1 row in set (0.00 sec)

mysql> 
复制代码

 

然后,在psql端验证:

复制代码
postgres=# select * from example;
 id  | data  
-----+-------
 123 | 11111
(1 row)

postgres=# 
复制代码

 

结束





本文转自健哥的数据花园博客园博客,原文链接:http://www.cnblogs.com/gaojian/p/3328031.html,如需转载请自行联系原作者

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
10天前
|
关系型数据库 MySQL 分布式数据库
阿里云PolarDB云原生数据库收费价格:MySQL和PostgreSQL详细介绍
阿里云PolarDB兼容MySQL、PostgreSQL及Oracle语法,支持集中式与分布式架构。标准版2核4G年费1116元起,企业版最高性能达4核16G,支持HTAP与多级高可用,广泛应用于金融、政务、互联网等领域,TCO成本降低50%。
|
11天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
11天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
10天前
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器,操作系统为windows server。服务器上部署mysql单实例,innodb引擎,独立表空间。未进行数据库备份,未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句,导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份,也未开启binlog日志,无法直接还原数据库。
|
4月前
|
存储 关系型数据库 测试技术
拯救海量数据:PostgreSQL分区表性能优化实战手册(附压测对比)
本文深入解析PostgreSQL分区表的核心原理与优化策略,涵盖性能痛点、实战案例及压测对比。首先阐述分区表作为继承表+路由规则的逻辑封装,分析分区裁剪失效、全局索引膨胀和VACUUM堆积三大性能杀手,并通过电商订单表崩溃事件说明旧分区维护的重要性。接着提出四维设计法优化分区策略,包括时间范围分区黄金法则与自动化维护体系。同时对比局部索引与全局索引性能,展示后者在特定场景下的优势。进一步探讨并行查询优化、冷热数据分层存储及故障复盘,解决分区锁竞争问题。
440 2
|
关系型数据库 分布式数据库 数据库
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
516 0
|
存储 缓存 关系型数据库
|
存储 SQL 并行计算
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(中)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
619 0
|
存储 算法 安全
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(下)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
548 0

推荐镜像

更多