[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位
1[oracle@raclhr-18c-n1 ~]$ file $ORACLE_HOME/bin/dg4odbc 2/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 3[oracle@raclhr-18c-n1 ~]$ ll
2)下载并安装ODBC Driver Manager
解压并进行编译安装:
1tar -zxvf unixODBC-2.3.7.tar.gz 2cd /home/oracle/unixODBC-2.3.7 3./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc 4make && 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进行安装:
1[root@raclhr-18c-n1 oracle]# rpm -ivh mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm 2warning: mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY 3Preparing... ################################# [100%] 4Updating / installing... 5 1:mysql-connector-odbc-5.3.13-1.el7################################# [100%] 6Success: Usage count is 1 7Success: Usage count is 1
4)配置ODBC Driver
vi /etc/odbc.ini
1[myodbc5] 2Driver = /usr/lib64/libmyodbc5w.so 3Description = Connector/ODBC 5.2 Driver DSN 4SERVER = 192.168.59.159 5PORT = 3306 6USER = root 7PASSWORD = lhr 8DATABASE = LHRDB 9OPTION = 0 10TRACE = OFF
5)验证ODBC连接
1export ODBCINI=/etc/odbc.ini 2isql myodbc5 -v 3[root@raclhr-18c-n1 unixODBC-2.3.7]# which isql 4/usr/bin/isql 5[root@raclhr-18c-n1 unixODBC-2.3.7]# isql myodbc5 -v 6+---------------------------------------+ 7| Connected! | 8| | 9| sql-statement | 10| help [tablename] | 11| quit | 12| | 13+---------------------------------------+ 14SQL> show databases; 15+-----------------------------------------------------------------+ 16| Database | 17+-----------------------------------------------------------------+ 18| information_schema | 19| LHRDB | 20| db_name | 21| db_name2 | 22| lhrdb | 23| lhrdb2 | 24| mysql | 25| performance_schema | 26| sakila | 27| sys | 28| test | 29+-----------------------------------------------------------------+ 30SQLRowCount returns 11 3111 rows fetched
mysql准备数据:
1[root@LHRDB ~]# mysql -h192.168.59.159 -uroot -plhr 2mysql: [Warning] Using a password on the command line interface can be insecure. 3Welcome to the MySQL monitor. Commands end with ; or \g. 4Your MySQL connection id is 14 5Server version: 5.7.19 MySQL Community Server (GPL) 6Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 7Oracle is a registered trademark of Oracle Corporation and/or its 8affiliates. Other names may be trademarks of their respective 9owners. 10Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 11mysql> show databases 12 -> ; 13+--------------------+ 14| Database | 15+--------------------+ 16| information_schema | 17| LHRDB | 18| db_name | 19| db_name2 | 20| lhrdb | 21| lhrdb2 | 22| mysql | 23| performance_schema | 24| sakila | 25| sys | 26| test | 27+--------------------+ 2811 rows in set (0.00 sec) 29mysql> use LHRDB; 30Database changed 31mysql> show tables; 32Empty set (0.00 sec) 33mysql> create table lhrtest(id int); 34Query OK, 0 rows affected (0.09 sec) 35mysql> insert into lhrtest values(1); 36Query OK, 1 row affected (0.00 sec) 37mysql> select count(1) from lhrtest; 38+----------+ 39| count(1) | 40+----------+ 41| 1 | 42+----------+ 431 row in set (0.00 sec)
6)配置tnsnames.ora
1myodbc5 = 2 (DESCRIPTION= 3 (ADDRESS= 4 (PROTOCOL=TCP) (HOST=192.168.59.52) (PORT=1521) 5 ) 6 (CONNECT_DATA= 7 (SID=myodbc5) 8 ) 9 (HS=OK) 10)
7)配置listener.ora
1SID_LIST_LISTENER= 2 (SID_LIST= 3 (SID_DESC= 4 (SID_NAME=myodbc5) 5 (ORACLE_HOME=/u01/app/oracle/product/19.2.0/dbhome_1) 6 (PROGRAM=dg4odbc) 7 (ENVS=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/19.2.0/dbhome_1/lib) 8 ) 9 )
8)创建 init.ora文件
创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora,内容如下:
1HS_FDS_CONNECT_INFO=myodbc5 2HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so 3HS_FDS_SUPPORT_STATISTICS=FALSE 4HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
9)使上述配置文件生效, 验证配置是否正确
1lsnrctl reload 2lsnrctl status 3tnsping myodbc5 4 5 6[oracle@raclhr-18c-n1 unixODBC-2.3.7]$ odbcinst -j 7unixODBC 2.3.1 8DRIVERS............: /etc/odbcinst.ini 9SYSTEM DATA SOURCES: /etc/odbc.ini 10FILE DATA SOURCES..: /etc/ODBCDataSources 11USER DATA SOURCES..: /home/oracle/.odbc.ini 12SQLULEN Size.......: 8 13SQLLEN Size........: 8 14SQLSETPOSIROW Size.: 8
10)创建dblink并验证
1[oracle@raclhr-18c-n1 admin]$ sas 2SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 11 22:13:20 2019 3Version 19.2.0.0.0 4Copyright (c) 1982, 2018, Oracle. All rights reserved. 5Connected to: 6Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 7Version 19.2.0.0.0 8SQL> show pdbs 9 CON_ID CON_NAME OPEN MODE RESTRICTED 10---------- ------------------------------ ---------- ---------- 11 2 PDB$SEED READ ONLY NO 12 3 LHRPDB2 READ WRITE NO 13SQL> alter session set container=lhrpdb2; 14Session altered. 15SQL> create public database link mysqltest connect to "root" identified by "lhr" using 'myodbc5' ; 16Database link created. 17SQL> select count(*) from "lhrtest"@mysqltest; 18 COUNT(*) 19---------- 20 1 21SQL> select * from "lhrtest"@mysqltest; 22 id 23---------- 24 1 25 26SQL> insert into "lhrtest"@mysqltest values(2) ; 271 row created. 28SQL> commit; 29Commit complete.
mysql中查询:
1mysql> select * from LHRDB.lhrtest; 2+------+ 3| id | 4+------+ 5| 1 | 6| 2 | 7+------+ 82 rows in set (0.00 sec)
注意:本文内容太多,公众号有字数限制,全文可点击文末的阅读原文,谢谢大家的理解。Oracle培训和认证记得找小麦苗哟。
更多理论参考: http://blog.itpub.net/26736162/viewspace-2144661/