oracle 配置DBlink 链接mysql库
一，环境配置与准备、简介
| \ | 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/
这个就代表安装成功了。
如果没有的话：
访问网站：https://www.oracle.com/downloads/
我的是11G然后进去，然后下载数据库文件来下载gatways软件，具体还是百度吧，我有这个，------，不懂联系我
02，ODBC-mysql安装
官网下载地址：https://dev.mysql.com/downloads/connector/odbc/
rpm 下载地址：https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm
我直接下载rpm的。
安装：
安装基础安装包：
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!
保证安装成功
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的用户名） identified by "123456"（需要连接的密码） using 'mysql_test'（使用的监听，就是tns配置的那个名字）;
连接成功
查看成功
人生就像一滴水，非要落下才后悔！
--kingle
