PostgreSQL通过mysql_fdw访问MySQL数据库

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

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,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
22小时前
|
关系型数据库 MySQL API
实时计算 Flink版操作报错合集之同步MySQL数据到另一个MySQL数据库,第一次同步后源表数据发生变化时目标表没有相应更新,且Web UI中看不到运行的任务,该怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
23小时前
|
存储 弹性计算 关系型数据库
云服务器 ECS产品使用问题之安装MySQL数据库间断性无法连接,提示“数据库链接被拒绝”或“数据库链接丢失”的问题,该怎么解决
云服务器ECS(Elastic Compute Service)是各大云服务商阿里云提供的一种基础云计算服务,它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
|
1天前
|
SQL 存储 关系型数据库
关系型数据库中的PostgreSQL
【6月更文挑战第11天】
25 3
|
1天前
|
存储 关系型数据库 MySQL
关系型数据库中的MySQL
【6月更文挑战第11天】
20 4
|
2天前
|
SQL 存储 关系型数据库
深入理解MySQL:数据库管理与性能优化
第一章:MySQL基础 MySQL概述:简要介绍MySQL的历史、特点和应用领域
|
2天前
|
SQL 关系型数据库 MySQL
精通MySQL:从数据库管理到性能优化
第一章:MySQL入门 MySQL简介:了解MySQL的起源、发展历程以及在Web开发中的重要性
|
2天前
|
监控 安全 关系型数据库
精通MySQL:数据库核心技术与应用实践
h3> 一、引言 MySQL作为开源关系型数据库管理系统的佼佼者,凭借其出色的性能、灵活性和稳定性,成为许多企业和开发者的首选
|
2天前
|
关系型数据库 MySQL 数据库
精通MySQL:数据库管理、性能优化与最佳实践
h3> 一、引言 MySQL是一个功能强大的开源关系型数据库管理系统,广泛应用于各种Web应用、企业级应用和数据分析等领域
|
13天前
|
关系型数据库 分布式数据库 数据库
数据库内核那些事|PolarDB IMCI让你和复杂低效的子查询说拜拜
PolarDB IMCI(In-Memory Column Index)确实是数据库领域的一项重要技术,特别是当它面对复杂和低效的子查询时,表现尤为出色。以下是关于PolarDB IMCI如何助力解决
|
23小时前
|
关系型数据库 MySQL 数据库
上手体验 PolarDB-X 数据库
PolarDB-X,一款高性能云原生分布式数据库。
16 1

热门文章

最新文章