mysql 创建数据链路_创建oracle访问mysql的数据链路步骤

(1)登陆到mysql

C:/Documents and Settings/Administrator>mysql -h localhost -u root -p

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 69

Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

(2)创建数据库

mysql> create database oracle;

Query OK, 1 row affected (0.00 sec)

(3)创建被访问的mysql用户

mysql> grant all privileges on oracle.* to 'orauser'@'%' identified by 'orauser'

with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

(4)登陆该用户，并测试

C:/Documents and Settings/Administrator>mysql -h localhost -u orauser -p

Enter password: *******

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 70

Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> use oracle;

Database changed

mysql> create table test(tid integer,tname varchar(20));

Query OK, 0 rows affected (0.06 sec)

mysql> insert into test(tid,tname)values(1,'byf');

Query OK, 1 row affected (0.03 sec)

mysql> insert into test(tid,tname)values(2,'others');

Query OK, 1 row affected (0.02 sec)

mysql> select * from test;

+------+--------+

| tid | tname |

+------+--------+

| 1 | byf |

| 2 | others |

+------+--------+

2 rows in set (0.00 sec)

mysql>

(5)安装mysql odbc driver 3.51并设置odbc数据源

data source name:mysqlora

server:localhost

user: orauser

password:orauser

database:oracle

(6)配置初始化参数%ORACLE_HOME%/admin/initmysqlora.ora

HS_FDS_CONNECT_INFO =mysqlora

HS_FDS_TRACE_LEVEL = on

(7)配置%ORACLE_HOME%/newtwork/admin/tnsname.ora

mysqlora=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

(CONNECT_DATA =

( SID= mysqlora))

(HS=OK)

)

(8)配置%ORACLE_HOME%/newtwork/admin/listener.ora

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oradb)

(ORACLE_HOME = D:/oracle)

(SID_NAME = oradb)

)

(SID_DESC =

(PROGRAM = hsodbc)

(ORACLE_HOME = D:/oracle/)

(SID_NAME = mysqlora)

)

)

(9)建立oracle到mysql的数据链路，并测试

C:/Documents and Settings/Administrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 8月 8 14:17:49 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn byf/1234;

已连接。

SQL> create database link mysqlora

2 connect to "orauser" identified by "orauser" using 'mysqlora';

数据库链接已创建。

SQL> select * from test@mysqlora;

tid tname

---------- --------------------

1 byf

2 others

SQL>

分享到：

2010-01-10 20:01

浏览 621

评论