PostgreSQL的mysql_fdw使用
发布时间:2020-12-13 17:29:34 所属栏目:百科 来源:网络整理
导读:要迁移Mysql的数据到PostgreSQL上来,有很多的办法,比如一些工具navicat,jmyetl等,但通常是要收费,而且效率和稳定性不一定好,最保守的办法是自己写一些脚本,当然也有一些开源的工具,比如要介绍的mysql_fdw,使用时本地需要建mysql软件和postgresql数据
要迁移Mysql的数据到PostgreSQL上来,有很多的办法,比如一些工具navicat,jmyetl等,但通常是要收费,而且效率和稳定性不一定好,最保守的办法是自己写一些脚本,当然也有一些开源的工具,比如要介绍的mysql_fdw,使用时本地需要建mysql软件和postgresql数据库,下面的测试例子是mysql和pg装在一起的。
mysql_fdw是一款基于BSD协议的开源工具,目前还不是postgresql内置,最新版本是1.0.1。 一、环境 CentOS 6.3 PostgreSQL 9.3.4 Host 10.1.11.73 二、Mysql的数据准备 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,password from mysql.user; +-----------+------------+-------------------------------------------+ | host | user | password | +-----------+------------+-------------------------------------------+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | db1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | :1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | % | usr_kenyon | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+------------+-------------------------------------------+ 5 rows in set (0.00 sec)三、 安装使用 mysql_fdw 下载地址: http://pgxn.org/dist/mysql_fdw/ 安装时使用postgres用户,参考README,有两步 make USE_PGXS=1 make USE_PGXS=1 install 安装完可能会有的异常,ERROR: could not load library "/home/postgres/lib/mysql_fdw.so": libmysqlclient.so.18: cannot open shared object file: No such file or directory 将mysql下的libmysqlclient.so.18文件拷贝到/home/postgres/lib下面或者做个软连接就可以了 1.创建extension扩展 postgres=# create extension mysql_fdw ; CREATE EXTENSION2.创建server postgres=# CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '10.1.11.73',port '3306'); CREATE SERVER3.创建一个或多个外部表(foreign table) postgres=# CREATE FOREIGN TABLE pg_mysql_tbl1 (id integer,name text) SERVER mysql_svr OPTIONS (table 'db_kenyon.tbl_kenyon'); CREATE FOREIGN TABLE postgres=# CREATE FOREIGN TABLE pg_mysql_tbl2 ( id integer,vname text) SERVER mysql_svr OPTIONS (query 'SELECT id,vname FROM db_kenyon.tbl_kenyon WHERE id<>2;'); CREATE FOREIGN TABLE4.创建PostgreSQL的fdw查询用户,pg_hba配置略 postgres=# create user u_select ENCRYPTED PASSWORD '123456'; CREATE ROLE 5.创建用户匹配关系(user mapping),用户为远程mysql的用户密码 CREATE USER MAPPING FOR u_select SERVER mysql_svr OPTIONS (username 'usr_kenyon',password '123456');6.查询数据,需要u_select登录,不然会报user mapping not found postgres=> select * from pg_mysql_tbl1 ; id | name ----+----------- 1 | test 2 | kenyon | it's null 4 | (4 rows) postgres=> select * from pg_mysql_tbl2; id | vname ----+------- 1 | test 4 | (2 rows)四、删除扩展 postgres=# drop foreign table pg_mysql_tbl1; DROP FOREIGN TABLE postgres=# drop foreign table pg_mysql_tbl2; DROP FOREIGN TABLE postgres=# drop user mapping for u_select server mysql_svr ; DROP USER MAPPING postgres=# drop server mysql_svr ; DROP SERVER postgres=# drop extension mysql_fdw ; DROP EXTENSION 五、总结 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |