一、安装Mysql数据库需安装Mysql开发包
# yum install mysql-*
# wget -c
# unzip mysql_fdw-2.1.2.zip
# cd mysql_fdw-2.1.2
# export PATH=/usr/local/pg9.5.2/bin/:$PATH
# export PATH=/usr/local/bin/:$PATH
# make USE_PGXS=1
# make USE_PGXS=1 install
二、配置Postgresql数据库
[postgres@db2 ~]$ psql -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
warehouse_db | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(4 rows)
postgres=# \c warehouse_db
You are now connected to database "warehouse_db" as user "postgres".
warehouse_db=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
warehouse_db=# CREATE SERVER mysql_server
warehouse_db-# FOREIGN DATA WRAPPER mysql_fdw
warehouse_db-# OPTIONS (host '127.0.0.1', port '3306');
CREATE SERVER
warehouse_db=#
三、配置Mysql数据库
[root@db2 ~]# mysql -uroot -p8732
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, 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> grant select,insert,update,create,delete,drop,index,alter,references,create temporary tables,lock tables on ghan. * to ghan@'localhost' identified by "ghan";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
You have new mail in /var/spool/mail/root
[root@db2 ~]# mysql -ughan -pghan
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, 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> \q
Bye
[root@db2 ~]#
四、初始化FOREIGN Table
warehouse_db=# CREATE FOREIGN TABLE warehouse(
warehouse_db(# warehouse_id int,
warehouse_db(# warehouse_name text)
warehouse_db-# SERVER mysql_server
warehouse_db-# OPTIONS (dbname 'ghan', table_name 'warehouse');
CREATE FOREIGN TABLE
warehouse_db=# INSERT INTO warehouse values (1, 'UPS');
ERROR: failed to execute the MySQL query:
Table 'ghan.warehouse' doesn't exist
warehouse_db=# INSERT INTO warehouse values (1, 'UPS');
ERROR: failed to execute the MySQL query:
Table 'ghan.warehouse' doesn't exist
五、创建mysql create warehouse tables
mysql> CREATE TABLE warehouse(
-> warehouse_id int,
-> warehouse_name text);
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> select * from warehouse;
Empty set (0.00 sec)
六、Postgreseq insert inot table 测试
warehouse_db=# INSERT INTO warehouse values (1, 'UPS');
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
warehouse_db=# INSERT INTO warehouse values (1, 'UPS');
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
七、创建 mysql create table unique index
mysql> CREATE UNIQUE INDEX warehouse_inx ON warehouse(warehouse_id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
八、postgresq insert into table 测试
[postgres@db2 ~]$ psql -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# \c warehouse_db
You are now connected to database "warehouse_db" as user "postgres".
warehouse_db=# INSERT INTO warehouse values (1, 'UPS');
INSERT 0 1
warehouse_db=# INSERT INTO warehouse values (2, 'UPS');
INSERT 0 1
warehouse_db=# INSERT INTO warehouse values (4, 'UPS');
INSERT 0 1
warehouse_db=# INSERT INTO warehouse values (4, 'UPSdsasf');
ERROR: failed to execute the MySQL query:
Duplicate entry '4' for key 1
warehouse_db=# INSERT INTO warehouse values (6, 'UPSdsasf');
INSERT 0 1
warehouse_db=# select * from warehouse;
warehouse_id | warehouse_name
--------------+----------------
1 | UPS
2 | UPS
4 | UPS
6 | UPSdsasf
(4 rows)
九、Postgresql\Mysql操作测试
9.0 查询测试
warehouse_db=# select * from warehouse;
warehouse_id | warehouse_name
--------------+----------------
1 | UPS
2 | UPS
4 | UPS
6 | UPSdsasf
(4 rows)
warehouse_db=#
9.1、Mysql
mysql> select * from warehouse;
+--------------+----------------+
| warehouse_id | warehouse_name |
+--------------+----------------+
| 1 | UPS |
| 2 | UPS |
| 4 | UPS |
| 6 | UPSdsasf |
+--------------+----------------+
4 rows in set (0.00 sec)
mysql>
9.2、测试：
warehouse_db=# DELETE FROM warehouse where warehouse_id = 3;
DELETE 0
warehouse_db=# UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;
UPDATE 1
warehouse_db=# EXPLAIN SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'UP' limit 1;
QUERY PLAN
---------------------------------------------------------------------------
Limit (cost=10.00..11.00 rows=1 width=36)
-> Foreign Scan on warehouse (cost=10.00..1010.00 rows=1000 width=36)
(2 rows)
warehouse_db=#
十、修改Mysql_fdw.c支持非唯一值索引
10.1.、修改mysql_fdw.c源文件
[root@db2 mysql_fdw-2.1.2]# vi mysql_fdw.c
1179 /*if (!mysql_is_column_unique(foreignTableId))
1180 elog(ERROR, "first column of remote table must be unique for INSERT/UPDATE/DELETE operation");
1181 */
注掉即可
10.2 重新编译安装
[root@db2 mysql_fdw-2.1.2]# export PATH=/usr/local/pg9.5.2/bin/:$PATH
[root@db2 mysql_fdw-2.1.2]# export PATH=/usr/local/bin/:$PATH
[root@db2 mysql_fdw-2.1.2]# make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv
-O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/pg9.5.2/include/postgresql/server -
I/usr/local/pg9.5.2/include/postgresql/internal -D_GNU_SOURCE -c -o mysql_fdw.o mysql_fdw.c
mysql_fdw.c:834: 警告：‘mysql_is_column_unique’ 定义后未使用
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv
-O2 -fpic -shared -o mysql_fdw.so connection.o option.o deparse.o mysql_query.o mysql_fdw.o -L/usr/local/pg9.5.2/lib -Wl,-rpath,'/usr/local/pg9.5.2/lib',--enable-new-dtags
[root@db2 mysql_fdw-2.1.2]# make USE_PGXS=1 install
/bin/mkdir -p '/usr/local/pg9.5.2/lib/postgresql'
/bin/mkdir -p '/usr/local/pg9.5.2/share/postgresql/extension'
/bin/mkdir -p '/usr/local/pg9.5.2/share/postgresql/extension'
/usr/bin/install -c -m 755 mysql_fdw.so '/usr/local/pg9.5.2/lib/postgresql/mysql_fdw.so'
/usr/bin/install -c -m 644 .//mysql_fdw.control '/usr/local/pg9.5.2/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//mysql_fdw--1.0.sql '/usr/local/pg9.5.2/share/postgresql/extension/'
[root@db2 mysql_fdw-2.1.2]# su - postgres
10.3、在Mysql库新非唯一值索引表：
[root@db2 ~]# mysql -ughan -pghan
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, 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 ghan
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> CREATE TABLE ghan(
-> warehouse_id int,
-> warehouse_name text);
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
+----------------+
| Tables_in_ghan |
+----------------+
| ghan |
| warehouse |
+----------------+
2 rows in set (0.00 sec)
mysql> use ghan
Database changed
mysql> select * from ghan;
+--------------+----------------+
| warehouse_id | warehouse_name |
+--------------+----------------+
| 6 | UPSdsasf |
| 6 | UPSdsasf |
| 5 | UPSdsasf |
+--------------+----------------+
3 rows in set (0.00 sec)
mysql>
10.4、在Postgrsql测试
[root@db2 mysql_fdw-2.1.2]# su - postgres
[postgres@db2 ~]$ psql -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# \c warehouse_db
You are now connected to database "warehouse_db" as user "postgres".
warehouse_db=# CREATE FOREIGN TABLE ghan(
warehouse_db(# warehouse_id int,
warehouse_db(# warehouse_name text)
warehouse_db-# SERVER mysql_server
warehouse_db-# OPTIONS (dbname 'ghan', table_name 'ghan');
CREATE FOREIGN TABLE
warehouse_db=# INSERT INTO ghan values (6, 'UPSdsasf');
INSERT 0 1
warehouse_db=# INSERT INTO ghan values (6, 'UPSdsasf');
INSERT 0 1
warehouse_db=# INSERT INTO ghan values (5, 'UPSdsasf');
INSERT 0 1
warehouse_db=# \q
[postgres@db2 ~]$
阅读(5401) | 评论(0) | 转发(0) |
