Linux Oracle访问MySQL的Client端软件
时间：2010-10-09 来源：mydear
Linux Oracle已被广泛应用但是也在不断的更新，这里介绍Linux Oracle装设置使用，帮助大家安装更新Linux Oracle系统前一阵在公司通过Linux Oracle访问MySQL，测试环境：CentOS5_X64, Oracle10g_X64, MySQL5 。把一些经验分享给大家！
1, 首先在Linux Oracle所在计算机安装MySQL的Client端软件，并且x86_64和i386版本的都需要安装，以便可以连接MySQL数据库。查看Client安装情况：
rpm -qa |grep mysql
mysql-5.0.45-7.el5
mysql-5.0.45-7.el5
得到两条记录，一条是x86_64的，一条是i386的。
如果看到还没有安装mysql客户端软件，则需要安装:
yum install mysql
yum install mysql.i386
验证在此Linux Oracle所在计算机是可以连接目标主机MySQL数据库:
mysql -h 192.168.1.1 -u root -p mysql
2, 检查Linux Oracle所在计算机是否已安装MySQL ODBC客户端，并且x86_64和i386版本的都需要安装。 rpm -qa |grep mysql-connect 如果没有安装mysql-connector-odbc，则用下面命令下载和安装：下载 mysql-connector-odbc：wget ftp://mirror.switch.ch/pool/3/mirror/centos/5.2/os/i386/CentO S/mysql-connector-odbc-3.51.12-2.2.i386.rpm 安装mysql-connector-odbc： rpm -ivh mysql-connector-odbc-3.51.12-2.2.i386.rpm 得到提示 libltdl.so.3 is needed by mysql-connector-odbc-3.51.12-2.2.i386 ，发现需要安装libtool的i386版本，因此通过如下命令安装libtool-ltdl.i386：
yum list *.i386|grep libtool
yum install libtool-ltdl.i386
3, 在Linux Oracle所在计算机编辑/etc/odbc.ini文件,测试ODBC工作vi /etc/odbc.ini #odbc.ini内容如下
- [test]
- Driver=/usr/lib64/libmyodbc3.so
- Description=MySQL
- Server=192.168.1.1(MySQL Server IP)
- Port=3306
- User= (MySQL Username)
- UID= (MySQL Username）
- Password= (MySQL PWD)
- Database= (MySQL Database Name)
- Option=3
- Socket=
在命令行中执行下列命令，应能够顺利登入MySQL Client窗口，即证明ODBC功能正常： isql -v testquit
4, 编辑hs配置文件 vi /ora10g/hs/admin/inittest.ora(注意文件名中蓝色部分为odbc.ini中蓝色名称)
- HS_FDS_CONNECT_INFO = test
- HS_FDS_TRACE_LEVEL = on（正式使用后，不需要排错的时候应设为off）
- HS_FDS_TRACE_FILE_NAME = test.trc
- HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
- set ODBCINI=/etc/odbc.ini
5, 编辑Linux Oracle所在计算机的Oracle listener的配置文件，建立一个模拟Oracle Listener的监听方式,为将来建立dblink做准备： vi /ora10g/network/admin/listener.ora 加入如下语句：
- (SID_DESC =
- (SID_NAME = test)
- (ORACLE_HOME = /ora10g)
- (PROGRAM = hsodbc)
- (ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib)
listener.ora文件现在的内容变成：
- SID_LIST_LISTENER = (
- SID_LIST =
- (SID_DESC =
- (ORACLE_HOME = /ora10g)
- (PROGRAM = extproc)
- (GLOBAL_DBNAME=prod)
- (SID_NAME=prod)
- (SID_DESC =
- (SID_NAME = test)
- (ORACLE_HOME = /ora10g)
- (PROGRAM = hsodbc)
- (ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib)
- LISTENER =
- (DESCRIPTION_LIST =
- (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)
- (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)
执行lsnrctl reload使Listener生效：
- su – oracle
- lsnrctl reload
- LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 09-FEB-2009 13:59:38 Copyright (c) 1991, 2007, Oracle.
- All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
- The command completed successfully
- lsnrctl status
- LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 12-FEB-2009 08:56:00
- Copyright (c) 1991, 2007, Oracle.All rights reserved.
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
- STATUS of the LISTENER
- AliasLISTENER
- Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
- Start Date 03-JAN-2009 03:47:39
- Uptime40 days 5 hr. 8 min. 20 sec
- Trace Leveloff
- SecurityON: Local OS Authentication
- SNMP OFF
- Listener Parameter File/ora10g/network/admin/listener.ora
- Listener Log File/ora10g/network/log/listener.log
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
- Services Summary...
- Service "PLSExtProc" has 1 instance(s).
- Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
- Service "test" has 1 instance(s).
- Instance "test", status UNKNOWN, has 1 handler(s) for this service...
- The command completed successfully
6, 编辑Linux Oracle所在计算机中的tnsnames.ora文件，便于建立dblink。注意，此tnsnames的配置可以支持tnsping,但是不能支持 sqlplus登录，只用于dblink：vi /ora10g/network/admin/tnsnames.ora
- test =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
- (CONNECT_DATA =
- (SID = test)
- (HS = OK)
7, 在Oracle Database建立dblink:
- create public database link MYSQL
- connect to "mysql username" identified by "mysql pwd"
- using '(DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )
- (CONNECT_DATA = (SID= test) )
- (HS=OK)
要注意用户名和密码处需要用双引号，否则Linux Oracle所传输的都是大写字母，可能无法登录进入MySQL。
8, 由于MySQL中的表名的大小写敏感，因此需要在进行SQL查询时对表名用双引号扩起来
select * from "tablename"@test
相关阅读 更多 +
