Oracle连接MySQL（odbc）

Oracle连接MySQL的流程(odbc)

1. 软件安装

  1. odbc rpm安装
rpm -ivh ***.rpm

安装完成之后会在/etc生成odbc.ini和odbcinst.ini

  1. 安装oracle网关 linux.x64_11gR2_gateways.zip 默认用图形界面安装，安装步骤
DISPLAY=:0.0
export DISPLAY

安装后执行

/u01/app/oracle/product/11g/db_1/root.sh

默认会生成/u01/app/oracle/product/11g/db_1/hs/admin/initdg4odbc.ora

2. 更改配置

  1. 修改/etc/odbc.ini（主要是mysql相关配置)
        [Data Sources]
        mysql-si = MySQL ODBC 5.3

        [mysql-si]
        Driver = /usr/lib64/libmyodbc5w.so
        Description = Connector/ODBC
        Server = 127.0.0.1(安装mysql的服务器ip)
        User =wuzehui(mysql连接用户名)
        Password = kkkkkk(密码)
        Port = 3306(端口)
        Database = test(mysql数据库)
        OPTION = 3

  1. /etc/odbcinst.ini默认不需要改

  2. 网关配置G /u01/app/oracle/product/11g/db_1/hs/admin/initdg4odbc.ora(默认不需要修改)

      cp /u01/app/oracle/product/11g/db_1/hs/admin/initdg4odbc.ora /u01/app/oracle/product/11g/db_1/hs/admin/initmyql-si.ora

3. 配置network下的监听

文件：/u01/app/oracle/product/11g/db_1/network/admin/listener.ora

        ID_LIST_LISTENER=
         (SID_LIST =
           (SID_DESC =
              (GLOBAL_DBNAME = orcl)
              (ORACLE_HOME = /u01/app/oracle/product/11g/db_1/)
              (SID_NAME = orcl)
           )
         )

        LISTENER =
          (DESCRIPTION_LIST =
            (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
            )
          )

        ADR_BASE_LISTENER = /u01/app/oracle

        SID_LIST_LISTENER2=
           (SID_LIST=
              (SID_DESC=
                 (SID_NAME= mysql-si)
                 (ORACLE_HOME= /u01/app/oracle/product/11g/db_1/)
                 (PROGRAM = dg4odbc)
                 (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11g/db_1//lib:/u01/app/oracle/product/11g/db_1//odbc/lib)
              )
           )

        LISTENER2 =
         (DESCRIPTION_LIST =
            (DESCRIPTION =
               (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1523))
            )
         )

文件：/u01/app/oracle/product/11g/db_1/network/admin/tnsnames.ora

        ORCL =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
            (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
            )
          )

        ##mysql的配置
        mysql-si=
                (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1523))
                        (CONNECT_DATA = (SID = mysql-si))
                        (HS = OK)
                )

4. 配置hs下的监听

文件：/u01/app/oracle/product/11g/db_1/hs/admin/listener.ora

        LISTENER =
         (ADDRESS_LIST=
              (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3306))
         )

        SID_LIST_LISTENER=
          (SID_LIST=
              (SID_DESC=
                 (SID_NAME=mysql-si)
                 (ORACLE_HOME=/u01/app/oracle/product/11g/db_1)
                 (PROGRAM=dg4odbc)
              )
          )

文件：/u01/app/oracle/product/11g/db_1/hs/admin/tnsnames.ora

        mysql-si=
          (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3306))
            (CONNECT_DATA=(SID=mysql-si))
            (HS=OK)
          )

5. 测试连接

isql mysql-si

6. 启动监听

   lsnrctl start/stop listener(oracle)
   lsnrctl start/stop listener2(mysql)
   lsnrctl status
   tnsping mysql-si (查看数据监听是否成功)

create public database link SI connect to "wuzehui"(mysql用户名) identified by "kkkkkk"(mysql密码) using 'mysql-si';(这里的设置是/etc/odbc.ini里面的配置)

8. 查询数据

select * from "table_name"@SI;

注：

  • table_name和MySQL数据库的用户名、密码都要加双引号，否则可能会出现权限不足的问题
  • SI为第七步创建的链接名

配置过程中碰到的问题

  • /etc/odbc.ini里面的配置问题
Driver = /usr/lib64/libmyodbc5w.so

这行驱动内容是安装完odbc默认的，所以根本没有检查它是不是真的存在， 然后就掉坑里了。 因为安装软件的过程是其他人做的，排查了好长时间也没发现这个问题，最后抱着试一试的心态在plsql客户端上创建的dblink，并查询了一下，结果报出了/usr/lib64/libmyodbc5w.so不存在的错误，发现目录下有一个类似的文件为/usr/lib64/libmyodbc5.so，然后放在配置文件里面试了一下，问题就解决了。

这个情况可能是软件安装过程的问题，没有进行重装，所以具体原因没有查明。

（完）