PostgreSQL通过mysql_fdw访问MySQL数据库-白红宇

Mysql与PostgreSQL的安装过程省略。

为简便起见，把MySQL和PostgreSQL都安装在一个机器上，然后在此机器上（准确地说是在PostgreSQL运行的机器上）安装mysql_fdw：

首先是下载 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]# lsMETA.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 READMEMySQL FDW for PostgreSQL 9.1+==============================This PostgreSQL extension implements a Foreign Data Wrapper (FDW) forthe MySQL.This code is experimental, and largely intended as a pet project for meto experiment with and learn about FDWs in PostgreSQL.By all means use it, but do so entirely at your own risk! You have beenwarned!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.1port:           The port number on which the MySQL server is listening.                Default: 3306The following parameter can be set on a MySQL foreign table:database:       The name of the MySQL database to query.                Default: NULLquery:          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. Usingquery can provide either a simple way to push down quals (which ofcourse 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 MySQLforeign server:username:       The username to use when connecting to MySQL                Default 
     
      password:       The password to authenticate to the MySQL server with.                Default: 
      
       Example--------- Install the extensionCREATE 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 Pagedpage@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=1gcc -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.cmysql_fdw.c: In function 'mysqlPlanForeignScan':mysql_fdw.c:395: 警告: 'rows' may be used uninitialized in this functiongcc -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 startserver starting[postgres@server pgsql]$ LOG:  database system was shut down at 2013-09-13 13:36:47 CSTLOG:  autovacuum launcher startedLOG:  database system is ready to accept connections

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

[root@server ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 existmysql> CREATE TABLE example ( id INT,data VARCHAR(100) );Query OK, 0 rows affected (0.05 sec)mysql> quitBye[root@server ~]# mysql -uusrabc -pEnter password: ERROR 1045 (28000): Access denied for user 'usrabc'@'localhost' (using password: YES)[root@server ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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/psqlpsql (9.1.2)Type "help" for help.postgres=# CREATE EXTENSION mysql_fdw;ERROR:  extension "mysql_fdw" already existspostgres=# CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '127.0.0.1', port '3306');CREATE SERVERpostgres=# CREATE FOREIGN TABLE example (id INT,data VARCHAR(100) ) SERVER mysql_svrpostgres-#     OPTIONS (table 'mysql.example');CREATE FOREIGN TABLEpostgres=# postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'usrabc', password 'usrabc');CREATE USER MAPPINGpostgres=# 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 selectedmysql> 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，如需转载请自行联系原作者