PostgreSQL通过mysql_fdw访问MySQL数据库
2023-09-11 14:20:28 时间
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=#
结束
相关文章
- MySQL数据库Binlog解析工具--binlog2sql
- 【Java】通过“配置文件”建立MySQL数据库连接,读取数据库(有图,有示例)
- MySQL使用初步—mysql数据库的基本命令
- InnoDB 隔离模式对 MySQL 性能的影响
- 一文搞懂MySQL主从复制
- mysql数据库恢复
- 【Docker】安装Presto连接Hive、mysql、oracle、postgresql、SQL server等7种类型数据库
- mysql时间日期操作
- 在MySQL和PostgreSQL之外,为什么阿里要研发HybridDB数据库?
- Navicat for MySQL 使用
- 《MySQL高效编程》一一1.2 数据库的种类
- Hive(三)Hive元数据信息对应MySQL数据库表
- mysql数据库如何创建只读用户
- python操作mysql数据库系列-操作MySql数据库(四)
- python操作mysql数据库系列-操作MySql数据库(二)
- Mysql命令mysql:连接Mysql数据库
- MySQL教程:数据库具体操作
- 有关Mysql的mysql_store_result函数返回NULL的情况以及其他注意事项
- MySQL数字辅助表
- MySQL数据库:视图View
- MySQL存储过程中双层游标嵌套循环实例
- MySQL 高频面试题合集详解
- spring中操作mysql数据库
- MySQL数据库开发必备常识
- MySQL数据库改名的三种方法
- mysql数据库导入sql文件时提示“Error Code: 1153 - Got a packet bigger than 'max_allowed_packet' bytes”解决办法
- 【MySQL】实验五 子查询
- mysql 同义词_数据库中的同义词synonym
- 【C#】使用MySql.Data.dll连接MySQL数据库