Oracle access MySql via unixODBC by DBLINK


Oracle access MySql via unixODBC by DBLINK
 
原来公司的同事询问了一个问题：
我问一下oracle连接远程mysql的方法 我之前用hsodbc 但是不能显示不同属性的字段 
oracle好像也没for mysql的透明网关  请问有什么方法能远程获取mysql的数据？ 我oracle专门做分析的 会调用mysql的数据 所以就要获取mysql的数据 又不想做同步
------------、
介个你懂吗
 
对MySql了解仅限于通用的SQL语句，其他完全不懂；但是我知道至少有超过2中以上的办法来实现这个需求。
他这里应该是借助unixODBC来访问MySql的数据，以下是测试过程。
环境：
MySql5.6 For Windows2003
Oracle10G For CentOS5.7
 
0. MySql安装部署数据准备
MySql在windows2003上的安装部署非常简单，简单的一步一步next就好了，这里略过，我们将创建一个数据库用户gtlions，创建一个表access_by_oracle并插入几条测试数据。
C:\Documents and Settings\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10-log 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> help
 
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/
 
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog
with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
 
For server side help, type 'help contents'
mysql> CREATE USER 'gtlions'@'%' IDENTIFIED BY '000000';
ERROR 1396 (HY000): Operation CREATE USER failed for 'gtlions'@'%'
mysql> CREATE USER 'gtlions' IDENTIFIED BY '000000';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT USAGE ON * . * TO 'gtlions' IDENTIFIED BY '000000' WITH MAX_QUERIES
_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIO
NS 0 ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE DATABASE IF NOT EXISTS `gtlions` ;
Query OK, 1 row affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON `gtlions` . * TO 'gtlions';
Query OK, 0 rows affected (0.00 sec)
 
mysql>exit
Bye
C:\Documents and Settings\Administrator>mysql -u gtlions -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.10-log 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 gtlions
Database changed
mysql> create table access_by_oracle(id int(5),name char(20));
Query OK, 0 rows affected (0.09 sec)
 
mysql> insert into access_by_oracle values(1,'gtlions');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into access_by_oracle values(2,'laidye');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from access_by_oracle;
+------+---------+
| id   | name    |
+------+---------+
|    1 | gtlions |
|    2 | laidye  |
+------+---------+
2 rows in set (0.00 sec)
 
mysql>
 
好了，MySql已经安装部署完毕并准备可以对外访问了。
 
1. Oracle访问端配置
我们需要借助unixODBC来访问，如果系统有自带安装好的就可以直接使用，如果没有就要安装它，可以使用yum或者源码包的方式，由于我这里系统已经安装好了，不再描述，如果需要yum安装它，命令参考如下：
yum -y unixODBC
我本机上已经安装完成的如下包：
[root@gtserdev ~]# rpm -qa|grep unixODBC
unixODBC-devel-2.2.11-10.el5
unixODBC-libs-2.2.11-10.el5
unixODBC-2.2.11-10.el5
unixODBC-kde-2.2.11-10.el5
安装完成后编辑/etc/odbc.ini，内容如下：
[oracle@gtserdev admin]$ cat /etc/odbc.ini
[test]
Driver=/usr/lib/libmyodbc3_r.so
Description=MySQL
Server=192.168.56.101
Port=3306
User=gtlions
Password=000000
Database=gtlions
Option=3
Socket= 
上述内容第3行开始为MySql服务器地址、端口、用户、密码、数据库，酌情修改吧。
然后执行下列命令进行测试，如果能够顺利登入MySQL Client窗口，说明我们的unixODBC功能可以正常：
[root@gtserdev ~]# isql -v test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [