原 在Oracle中通过dblink访问MySQL数据库
👉 本文共约711个字，系统预计阅读时间或需3分钟。
简介
在之前的博客中已经配置过了，可以参考：https://www.dbaup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html#Oracle_lian_jie_daoMySQL
Oracle使用DG4ODBC数据网关连接MySQL数据库，可以不用安装Gateways网关，其原理图如下：
从上图可知，Oracle连接MySQL需要涉及到如下组件：DG4ODBC, ODBC Driver Manager, ODBC Driver。
环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | -- oracle 21c docker rm -f ora21tomariadb docker run -d --name ora21tomariadb -h lhroracle21c \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/oracle21c_ee_db_21.3.0.0 \ /usr/sbin/init -- 由于只读主目录特性，21c中的spfile、pfile、密码文件默认在$ORACLE_BASE/dbs目录下，例如：/u01/app/oracle/dbs下。 [oracle@lhroracle21c dbs]$ pwd /u01/app/oracle/dbs [oracle@lhroracle21c dbs]$ ll total 18308 -rw-rw---- 1 oracle oinstall 1544 Mar 18 16:19 hc_LHRCDB.dat -rw-r----- 1 oracle oinstall 686 Mar 18 16:19 initLHRCDB.ora -rw-r----- 1 oracle oinstall 24 Aug 16 2021 lkLHRCDB -rw-r----- 1 oracle oinstall 2048 Aug 16 2021 orapwLHRCDB -rw-r----- 1 oracle oinstall 18726912 Aug 16 2021 snapcf_LHRCDB.f -rw-r----- 1 oracle oinstall 3584 Mar 18 17:01 spfileLHRCDB.ora [oracle@lhroracle21c dbs]$ -- mysql 8.1 mkdir -p /etc/mysql/ora2mysql81/ cat > /etc/mysql/ora2mysql81/conf/my.cnf <<"EOF" [mysqld] default-time-zone = '+8:00' log_timestamps = SYSTEM skip-name-resolve log-bin server_id=813420 character_set_server=utf8mb4 default_authentication_plugin=mysql_native_password EOF docker run -d --name ora2mysql81 -h ora2mysql81 \ -v /etc/mysql/mysql81/conf:/etc/mysql/conf.d \ -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \ mysql:8.1.0 mysql -uroot -plhr -h192.92.0.54 -P3306 -e "select now(),@@hostname,@@version;" mysql -uroot -plhr -h192.92.0.54 -P3306 -e "create database lhrdb;" mysql> use lhrdb; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.04 sec) [root@alldb ~]# mysql -uroot -plhr -h192.92.0.54 -P3306 -e "select now(),@@hostname,@@version;" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------------+-------------+-----------+ | now() | @@hostname | @@version | +---------------------+-------------+-----------+ | 2024-03-18 16:23:35 | ora2mysql81 | 8.1.0 | +---------------------+-------------+-----------+ |
开始配置
1、以下内容均在Oracle的主机配置。
2、MySQL的主机为192.92.0.54
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | -- root操作 yum install -y unixODBC unixODBC-devel mysql-connector-odbc -- 升级 mysql-connector-odbc驱动 rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7.rpm yum update -y unixODBC unixODBC-devel mysql-connector-odbc cat >/etc/odbc.ini <<"EOF" [myodbc8] #Driver = /usr/lib64/libmyodbc8w.so Driver = /usr/lib64/libmyodbc8w.so Description = Connector/ODBC 5.2 Driver DSN SERVER = 192.92.0.54 PORT = 3306 USER = root PASSWORD = lhr DATABASE = lhrdb OPTION = 0 TRACE = OFF EOF export ODBCINI=/etc/odbc.ini isql myodbc8 -v show databases; [oracle@lhroracle21c admin]$ export ODBCINI=/etc/odbc.ini [oracle@lhroracle21c admin]$ isql myodbc8 -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-----------------------------------------------------------------+ | Database | +-----------------------------------------------------------------+ | information_schema | | lhrdb | | mysql | | performance_schema | | sys | +-----------------------------------------------------------------+ SQLRowCount returns 5 5 rows fetched SQL> -- oracle操作 cd /u01/app/oracle/product/21c/dbhome_1/network/admin cat >> tnsnames.ora <<"EOF" myodbc8 = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521) ) (CONNECT_DATA= (SID=myodbc8) ) (HS=OK) ) EOF -- vi listener.ora SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=myodbc8) (ORACLE_HOME=/u01/app/oracle/product/21c/dbhome_1) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/21c/dbhome_1/lib) ) ) -- 注意Oracle 21c的位置 cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initmyodbc8.ora <<"EOF" HS_FDS_CONNECT_INFO=myodbc8 HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 EOF lsnrctl reload lsnrctl status tnsping myodbc8 [oracle@lhroracle21c admin]$ tnsping myodbc8 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 18-MAR-2024 16:33:15 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA= (SID=myodbc8)) (HS=OK)) OK (0 msec) [oracle@lhroracle21c admin]$ [oracle@lhroracle21c admin]$ odbcinst -j unixODBC 2.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /etc/odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 [oracle@lhroracle21c admin]$ create public database link mysqltest connect to "root" identified by "lhr" using 'myodbc8' ; select count(*) from "t1"@mysqltest; SYS@LHRCDB> select count(*) from "t1"@mysqltest; COUNT(*) ---------- 1 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
