专栏首页小麦苗的DB宝专栏[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c连接到MySQL 5.7

[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c连接到MySQL 5.7

今天小麦苗给大家分享的是[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c pdb连接到MySQL 5.7。

更多理论请参考: http://blog.itpub.net/26736162/viewspace-2144661/

[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c连接到MySQL 5.7

Oracle使用DG4ODBC数据网关连接其它非Oracle数据库,其原理图如下:

从上图可知,Oracle连接MySQL需要涉及到如下组件:DG4ODBC, ODBC Driver Manager, ODBC Driver,本文将一一讲解它们的配置。

版本信息:

Oracle: 19.2.0.0.0 OS: CentOS 7.3

MySQL: 5.7.19 OS: CentOS 6.5

1)判断32位还是64位

[oracle@raclhr-18c-n1 ~]$ file $ORACLE_HOME/bin/dg4odbc
/u01/app/oracle/product/19.2.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=4675eeb874fe889b960a29d3d084f9c1f4c471d0, not stripped
[oracle@raclhr-18c-n1 ~]$ ll

2)下载并安装ODBC Driver Manager

下载地址: http://www.unixodbc.org/download.html

解压并进行编译安装:

tar -zxvf unixODBC-2.3.7.tar.gz
cd /home/oracle/unixODBC-2.3.7
./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
make && make install

安装成功后,unixODBC所需的头文件都被安装到了/usr/inlucde下,编译好的库文件安装到了/usr/lib下,与unixODBC相关的可执行文件安装到了/usr/bin下,配置文件放到了/etc下。

3)下载并按照ODBC Driver for MySQL

下载地址 http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads

进行安装:

[root@raclhr-18c-n1 oracle]# rpm -ivh mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm 
warning: mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-connector-odbc-5.3.13-1.el7################################# [100%]
Success: Usage count is 1
Success: Usage count is 1

4)配置ODBC Driver

vi /etc/odbc.ini

[myodbc5]  
Driver = /usr/lib64/libmyodbc5w.so 
Description = Connector/ODBC 5.2 Driver DSN  
SERVER = 192.168.59.159  
PORT = 3306  
USER = root  
PASSWORD = lhr  
DATABASE = LHRDB  
OPTION = 0  
TRACE = OFF

5)验证ODBC连接

export ODBCINI=/etc/odbc.ini
isql myodbc5 -v
[root@raclhr-18c-n1 unixODBC-2.3.7]# which isql
/usr/bin/isql
[root@raclhr-18c-n1 unixODBC-2.3.7]# isql myodbc5 -v 
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database                                                        |
+-----------------------------------------------------------------+
| information_schema                                              |
| LHRDB                                                           |
| db_name                                                         |
| db_name2                                                        |
| lhrdb                                                           |
| lhrdb2                                                          |
| mysql                                                           |
| performance_schema                                              |
| sakila                                                          |
| sys                                                             |
| test                                                            |
+-----------------------------------------------------------------+
SQLRowCount returns 11
11 rows fetched

mysql准备数据:

[root@LHRDB ~]# mysql -h192.168.59.159 -uroot -plhr
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LHRDB              |
| db_name            |
| db_name2           |
| lhrdb              |
| lhrdb2             |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
+--------------------+
11 rows in set (0.00 sec)
mysql> use LHRDB;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table lhrtest(id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into lhrtest values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select count(1) from lhrtest;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

6)配置tnsnames.ora

myodbc5 =   
  (DESCRIPTION=  
    (ADDRESS=  
        (PROTOCOL=TCP) (HOST=192.168.59.52) (PORT=1521)  
    )  
    (CONNECT_DATA=  
      (SID=myodbc5)  
    )  
    (HS=OK)  
)

7)配置listener.ora

SID_LIST_LISTENER=  
  (SID_LIST=  
    (SID_DESC=  
      (SID_NAME=myodbc5)  
      (ORACLE_HOME=/u01/app/oracle/product/19.2.0/dbhome_1)  
      (PROGRAM=dg4odbc)  
      (ENVS=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/19.2.0/dbhome_1/lib)  
    )   
  )

8)创建 init.ora文件 创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora,内容如下:

HS_FDS_CONNECT_INFO=myodbc5
HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

9)使上述配置文件生效, 验证配置是否正确

lsnrctl reload
lsnrctl status
tnsping myodbc5


[oracle@raclhr-18c-n1 unixODBC-2.3.7]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

10)创建dblink并验证

[oracle@raclhr-18c-n1 admin]$ sas
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 11 22:13:20 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 LHRPDB2                        READ WRITE NO
SQL> alter session set container=lhrpdb2;
Session altered.
SQL> create   public   database  link mysqltest  connect   to   "root"  identified  by   "lhr"  using  'myodbc5' ;
Database link created.
SQL>  select   count(*)  from  "lhrtest"@mysqltest;
  COUNT(*)
----------
         1
SQL>  select   *  from  "lhrtest"@mysqltest;
        id
----------
         1

SQL> insert into "lhrtest"@mysqltest values(2) ;
1 row created.
SQL> commit;
Commit complete.

mysql中查询:

mysql> select  *  from  LHRDB.lhrtest;          
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

注意:本文内容太多,公众号有字数限制,全文可点击文末的阅读原文,谢谢大家的理解。Oracle培训和认证记得找小麦苗哟。

更多理论参考: http://blog.itpub.net/26736162/viewspace-2144661/

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一份完整的 MySQL 开发规范,进大厂必看!

    https://www.cnblogs.com/huchong/p/10219318.html

    Java技术栈
  • CDN搭建

    2.托管机房------买了机房,服务器,每月收多少费用--深圳机房--机房多少钱一天

    用户6252193
  • MySQL总结

    2.alter table t1 modify name char(3); 修改类型

    changxin7
  • 日均5亿查询量的京东订单中心,为什么舍MySQL用ES?

    京东到家订单中心系统业务中,无论是外部商家的订单生产,或是内部上下游系统的依赖,订单查询的调用量都非常大,造成了订单数据读多写少的情况。

    Yajun_
  • 玩转Mysql系列 - 第7篇:玩转select条件查询,避免采坑

    电商中:我们想查看某个用户所有的订单,或者想查看某个用户在某个时间段内所有的订单,此时我们需要对订单表数据进行筛选,按照用户、时间进行过滤,得到我们期望的结果。

    路人甲Java
  • 助力秋招-独孤九剑荡剑式 | Java语言&基础面试题

    https://blog.csdn.net/sufu1065/article/details/88051083

    王知无
  • 搬运基础服务到kubernetes,遇这3类大坑怎么破?

    ? 工作中需要将原本部署在物理机或虚拟机上的一些基础服务搬到kubernetes中,在搬的过程中遇到了不少坑,笔者在此特别分享一下所遇到的问题及相应的解决方法...

    腾讯云TStack
  • 3个点说清楚分库分表扩容问题

    像我这样的菜鸟,总会有各种疑问,刚开始是对 JDK API 的疑问,对 NIO 的疑问,对 JVM 的疑问,当工作几年后,对服务的可用性,可扩展性也有了新的疑问...

    程序员追风
  • 深入浅出InnoDB MLOG CHECKPOINT

    提示:公众号展示代码会自动折行,建议横屏阅读 1 MLOG CHECKPOINT是什么 在MySQL 5.7存储引擎InnoDB崩溃恢复中,我们一定看到过M...

    腾讯数据库技术
  • MySql 利用crontab实现MySql定时任务

    /usr/local/mysql/bin/mysql -u root -phdsds123 test -e "truncate table test.stude...

    授客

扫码关注云+社区

领取腾讯云代金券