[Oracle-> MySQL] Oracle通过dblink连接MySQL–Oracle 19c连接到MySQL 5.7
- 2019 年 10 月 10 日
- 筆記
今天小麦苗给大家分享的是[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/
