postgresql安装mysql_fdw（mysql和postgresql数据共享）

最新推荐文章于 2023-07-22 21:41:33 发布

滴霸哥

最新推荐文章于 2023-07-22 21:41:33 发布

阅读量3.4k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_42579328/article/details/89644444

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1、Mysql与PostgreSQL的安装过程省略。

Ubuntu16.04 mysql数据库安装：https://blog.csdn.net/xiangwanpeng/article/details/54562362
Ubuntu16.04 postgresql数据库安装：https://www.jianshu.com/p/dda94c4ffd52
mysql_fdw：
首先是下载 mysql_fdw：
http://pgxn.org/dist/mysql_fdw/
mysql_fdw-2.1.2.zip
然后是解压和安装
2、进入解压后mysql_fdw_2.1.2目录下打开终端输入如下指令：

在这里插入图片描述
A、终端输入“make USE_PGXS=1”

此时可能会出现如下报错（如未出现则忽略）：

You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
Makefile:52: *** PostgreSQL 9.3, 9.4, 9.5 or 9.6 is required to compile this extension。 停止。

在这里插入图片描述
注意：以上问题是Postgres环境未正确设置
通过如下步骤解决该问题：
①、终端输入：

pip3 install psycopg2

在这里插入图片描述
但是还是出现了问题不能进行安装：

 Error: b'You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.\n'
    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-cgb7qyw1/psycopg2/

在这里插入图片描述
此时需要终端输入：

sudo apt-get install libpq-dev python-dev

在这里插入图片描述
此时在进行安装：pscopg2就成功了。

B、此时终端在输入：“make USE_PGXS=1”
出现如下报错：

Makefile:47: /usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk: 没有那个文件或目录
Makefile:52: *** PostgreSQL 9.3, 9.4, 9.5 or 9.6 is required to compile this extension。 停止。

在这里插入图片描述
通过如下方式解决：
①、终端输入：

sudo apt-get install postgresql-server-dev-all

在这里插入图片描述

sudo apt-get install postgresql-common

在这里插入图片描述
注意：如果使用“centos7”时则使用“yum install postgresql-devel -y”

yum install postgresql-devel -y

3、解决以上问题后，在在终端输入make USE_PGXS=1和make USE_PGXS=1 install ：

make USE_PGXS=1 
make USE_PGXS=1 install

①、输入输入命令：

make USE_PGXS=1

在这里插入图片描述
②、输入如下命令：

make USE_PGXS=1 install

在这里插入图片描述
注意：如上权限不够的时候只需要在前面添加sudo。
4、检查一下 mysql_fdw.so文件，是否出现在 /usr/local/pgsql/lib 目录下。

5、然后，分别启动mysql和 postgresql：

mysql -uroot -p

在这里插入图片描述

2019-04-28T10:51:31.932963Z mysqld_safe Logging to syslog.
2019-04-28T10:51:31.947877Z mysqld_safe Logging to '/var/log/mysql/error.log'.
/usr/bin/mysqld_safe: 152: /usr/bin/mysqld_safe: cannot create /var/log/mysql/error.log: Permission denied
cat: /var/run/mysqld/mysqld.pid: 权限不够
rm: 无法删除'/var/run/mysqld/mysqld.pid': 权限不够
2019-04-28T10:51:31.989685Z mysqld_safe Fatal error: Can't remove the pid file:
/var/run/mysqld/mysqld.pid.
Please remove the file manually and start /usr/bin/mysqld_safe again;
mysqld daemon not started
/usr/bin/mysqld_safe: 152: /usr/bin/mysqld_safe: cannot create /var/log/mysql/error.log: Permission denied

出现了如上图所示问题，解决方案如下：
①、首先停止服务：

sudo service mysql start

②、然后使用skip-grant-tables在安全模式下重启：

sudo mysqld_safe --skip-grant-tables &

在这里插入图片描述
A、启动postgres：
a、终端输入：

/etc/init.d/postgresql statr

然后输入如下命令：
在这里插入图片描述
注意：红色标记处是我的数据库用户名：初始数据库用户名是“postgres"因此输入如下命令即可：

/etc/init.d/postgresql start
su - postgres
psql

数据库则如下：
在这里插入图片描述
6、然后，在mysql客户端，执行以下命令来创建表以及访问该表的用户：

odoo11@ubuntu:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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 database db_kenyon;
Query OK, 1 row affected (0.01 sec)

mysql> create table tbl_kenyon(id int,vname varchar(48));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl_kenyon values(1,'test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(2,'kenyon');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(null,'it\'s null');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(4,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tbl_kenyon;
+------+-----------+
| id   | vname     |
+------+-----------+
|    1 | test      |
|    2 | kenyon    |
| NULL | it's null |
|    4 | NULL      |
+------+-----------+
4 rows in set (0.00 sec)

mysql> grant select on db_kenyon.tbl_kenyon to 'usr_kenyon'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,authentication_string from mysql.user;
+----------------+------------------+-------------------------------------------+
| host           | user             | authentication_string                     |
+----------------+------------------+-------------------------------------------+
| localhost      | root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost      | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost      | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost      | debian-sys-maint | *37601F1A8F99A99095F119489AC97BF5E50DABB7 |
| % | usr_kenyon       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

登录mysql用户：

mysql -h192.168.79.129 -uusr_kenyon -p

7、然后，开始在PostgreSQL端，建立FDW:
在这里插入图片描述
①.创建extension扩展

odoo=# create extension mysql_fdw;
CREATE EXTENSION

②.创建server

odoo=# CREATE SERVER mysql_svr 
 		FOREIGN DATA WRAPPER mysql_fdw  
		 OPTIONS (host '%', port '3306');
		 CREATE USER MAPPING

CREATE SERVER
③.创建一个或多个外部表(foreign table)

odoo=# CREATE FOREIGN TABLE pg_mysql_tbl1 (id integer,
    vname text)
    SERVER mysql_svr
    OPTIONS (dbname 'db_kenyon',table_name 'tbl_kenyon');
	CREATE FOREIGN TABLE

此处一定要注意：dbname 、table_name：

	dbname ：对应的是mysql中数据库的名字；
	table_name：对应的是数据库中某张表的名字；

如果此处不写明dbname、table_name可能会出现如下问题：

odoo=> select name from pg_mysql_tbl1 order by name;
ERROR:  failed to prepare the MySQL query: 
Table 'public.db_kenyon.tbl_kenyon' doesn't exist

在这里插入图片描述
④.创建PostgreSQL的fdw查询用户

odoo=# CREATE USER MAPPING FOR u_select
		     SERVER mysql_svr 
			 OPTIONS (username 'usr_kenyon', password '123456');
			CREATE USER MAPPING

⑤.创建用户匹配关系(user mapping),用户为远程mysql的用户密码

CREATE USER MAPPING FOR u_select
    SERVER mysql_svr 
    OPTIONS (username 'usr_kenyon', password '123456');

⑥.登录u_select用户：

psql -h 127.0.0.1 -U odoo11 -d u_selec -p 5432

效果如下：
postgresql数据库查询的：
在这里插入图片描述
mysql数据库查询的如下：

8、删除扩展：

odoo=# drop foreign table pg_mysql_tbl1;
DROP FOREIGN TABLE
odoo=# drop foreign table pg_mysql_tbl2;
DROP FOREIGN TABLE

odoo=# drop user mapping for u_select server mysql_svr ;
DROP USER MAPPING

odoo=# drop server mysql_svr ;
DROP SERVER

postgres=# drop extension mysql_fdw ;
DROP EXTENSION

出现的问题总结：

①、创建mysql用户的时候注意账户的host，如本地测试建议使用本地127.0.0.1。
②、如果拒绝连接请注意是否是mysql用户的host问题。
③、如果无法查询等问题，请注意是否给用户赋予了权限。此处权限不做介绍。

滴霸哥

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
postgresql安装mysql_fdw（mysql和postgresql数据共享）

1、Mysql与PostgreSQL的安装过程省略。为简便起见，把MySQL和PostgreSQL都安装在一个机器上，然后在此机器上（准确地说是在PostgreSQL运行的机器上）安装mysql_fdw：首先是下载 mysql_fdw：http://pgxn.org/dist/mysql_fdw/mysql_fdw-2.1.2.zip然后是解压和安装2、进入解压后mysql_fdw_2...
复制链接

扫一扫