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>