小小编
一，环境配置与准备、简介
| \ | oracle | mysql |
| 主机名 | oracle01 | mysqlre1 |
| IP | 192.168.0.10 | 192.168.0.187 |
本文章是oracle通过dblink连接mysql
二,安装基础环境
01，透明网关、ODBC
默认安装oracle数据库的时候，会配置安装好这个，当然有的还是会没有安装的，验证是否安装：
在oracle环境下：
[oracle@oracle01 ~]$ cd $ORACLE_HOME/hs/
这个就代表安装成功了。
02，ODBC-mysql安装
安装：
安装基础安装包：
yum install unixODBC*
1 [root@oracle01 ~]# yum install unixODBC* 2 Loaded plugins: fastestmirror, langpacks 3 base | 3.6 kB 00:00:00 4 epel | 5.3 kB 00:00:00 5 extras | 2.9 kB 00:00:00 6 updates | 2.9 kB 00:00:00 7 zabbix | 2.9 kB 00:00:00 8 zabbix-non-supported | 951 B 00:00:00 9 (1/8): base/7/x86_64/group_gz | 165 kB 00:00:00 10 (2/8): epel/x86_64/group_gz | 90 kB 00:00:00 11 (3/8): extras/7/x86_64/primary_db | 153 kB 00:00:00 12 (4/8): epel/x86_64/updateinfo | 1.0 MB 00:00:00 13 (5/8): zabbix/x86_64/primary_db | 117 kB 00:00:01 14 (6/8): base/7/x86_64/primary_db | 6.0 MB 00:00:02 15 (7/8): epel/x86_64/primary_db | 6.9 MB 00:00:02 16 (8/8): updates/7/x86_64/primary_db | 5.8 MB 00:00:03 17 Determining fastest mirrors 18 * base: mirrors.aliyun.com 19 * extras: mirrors.aliyun.com 20 * updates: mirrors.aliyun.com 21 Resolving Dependencies 22 --> Running transaction check 23 ---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be updated 24 ---> Package unixODBC.x86_64 0:2.3.1-14.el7 will be an update 25 ---> Package unixODBC-devel.x86_64 0:2.3.1-11.el7 will be updated 26 ---> Package unixODBC-devel.x86_64 0:2.3.1-14.el7 will be an update 27 --> Finished Dependency Resolution 28 29 Dependencies Resolved 30 31 ======================================================================================== 32 Package Arch Version Repository Size 33 ======================================================================================== 34 Updating: 35 unixODBC x86_64 2.3.1-14.el7 base 413 k 36 unixODBC-devel x86_64 2.3.1-14.el7 base 55 k 37 38 Transaction Summary 39 ======================================================================================== 40 Upgrade 2 Packages 41 42 Total download size: 468 k 43 Is this ok [y/d/N]: y 44 Downloading packages: 45 No Presto metadata available for base 46 (1/2): unixODBC-devel-2.3.1-14.el7.x86_64.rpm | 55 kB 00:00:00 47 (2/2): unixODBC-2.3.1-14.el7.x86_64.rpm | 413 kB 00:00:00 48 ---------------------------------------------------------------------------------------- 49 Total 1.0 MB/s | 468 kB 00:00 50 Running transaction check 51 Running transaction test 52 Transaction test succeeded 53 Running transaction 54 Warning: RPMDB altered outside of yum. 55 Updating : unixODBC-2.3.1-14.el7.x86_64 1/4 56 Updating : unixODBC-devel-2.3.1-14.el7.x86_64 2/4 57 Cleanup : unixODBC-devel-2.3.1-11.el7.x86_64 3/4 58 Cleanup : unixODBC-2.3.1-11.el7.x86_64 4/4 59 Verifying : unixODBC-devel-2.3.1-14.el7.x86_64 1/4 60 Verifying : unixODBC-2.3.1-14.el7.x86_64 2/4 61 Verifying : unixODBC-devel-2.3.1-11.el7.x86_64 3/4 62 Verifying : unixODBC-2.3.1-11.el7.x86_64 4/4 63 64 Updated: 65 unixODBC.x86_64 0:2.3.1-14.el7 unixODBC-devel.x86_64 0:2.3.1-14.el7 66 67 Complete!
View Code
保证安装成功
03，mysql创建远程用户与远程数据库
mysql> show databases; +-------------------------+ | Database | +-------------------------+ | information_schema | | mysql | | #mysql50#mysql-bin-obar | | performance_schema | | sys | +-------------------------+ 5 rows in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table test ( id int ,name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into test (id,name) values (1,\'nihao\'); Query OK, 1 row affected (0.00 sec) mysql> grant all privileges on test.* to \'kingle\'@\'%\' IDENTIFIED BY \'123456\'; Query OK, 0 rows affected, 1 warning (0.00 sec) 连接测试 [root@mysqlre1 ~]# mysql -ukingle -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 747202 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, 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 | | test | +--------------------+ 2 rows in set (0.00 sec) mysql>
04，配置文件配置
在oracle数据库上配置odbc连接mysql的环境
[oracle@oracle01 hs]$ cat /etc/odbc.ini [mysql_test] Description = ODBC for MySQL Driver = /usr/lib64/libmyodbc8w.so Server = 192.168.0.187 ---mysql远程地址 Port = 3306 ----mysql 端口 User = kingle ----mysql连接用户 Password = 123456 ---mysql 连接密码 Database = test ---mysql连接数据库
[oracle@oracle01 hs]$ cat /etc/odbcinst.ini [MySQL] Description=ODBC for MySQL Driver=/usr/lib/libmyodbc5.so Setup=/usr/lib/libodbcmyS.so Driver64=/usr/lib64/libmyodbc5.so Setup64=/usr/lib64/libodbcmyS.so FileUsage=1 UsageCount=2
05，配置连接监听地址
[oracle@oracle01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@oracle01 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@oracle01 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = oracle01)
(SID_NAME = oracle01)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC = ##主要添加这一段，前面的默认是oracle的，这一段才是mysql的
(SID_NAME = mysql_test) ##给需要链接的mysql配置一个名字，这个后续连接的时候需要用上其他的默认看自己主机情况修改
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
)
)
[oracle@oracle01 admin]$ cat tnsnames.ora
mysql_test= ---链接名字
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME= mysql_test) ---连接实例名，与listence那个对应
)
(HS = OK)
)
[oracle@oracle01 admin]$
重启监听
[oracle@oracle01 db_1]$ lsnrctl stop
[oracle@oracle01 db_1]$ lsnrctl start
测试连接
[oracle@oracle01 admin]$ tnsping mysql_test TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 12-DEC-2019 15:33:54 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME= mysql_test)) (HS = OK)) OK (0 msec)
06，连接测试
oracle数据库上链接mysql测试
发现成功了，
07，配置initdg4odbc.ora
进入oracle_home 目录下找到hs\admin目录中initdg4odbc.ora文件并复制一份。然后修改复制文件。把文件名称改为init+刚刚配置数据源名称（本实例为mysql_test）.ora
[oracle@oracle01 admin]$ pwd /u01/app/oracle/product/11.2.0/db_1/hs/admin [oracle@oracle01 admin]$ cat initmysql_test.ora HS_FDS_CONNECT_INFO = mysql_test HS_FDS_TRACE_LEVEL = on HS_FDS_SHAREABLE_NAME = libodbc.so HS_LANGUAGE = AMERICAN_AMERICA.UTF8 HS_NLS_NCHAR = UCS2 [oracle@oracle01 admin]$
HS_FDS_TRACE_LEVEL =on 这里最好设置on 网上一般将都是设置为off，为了查看错误日志，最好改为on，日志存在 $ORACLE_HOME/hs/log 下面。
同样这行HS_FDS_SHAREABLE_NAME=libodbc.so 一定不能少，不然后面会报错
ORA-28500: connection from ORACLE to a non-Oracle system returned this message。
08，创建dblink
create public database link mysql_test connect to "kingle" identified by "123456" using \'mysql_test\'; create public database link mysql_test (创建的link名字可以自己写)connect to "kingle"（需要链接mysql的用户名） ide
连接成功
查看成功
