Oracle dblink MySQL

1

yum install unixODBC*

1
2
3
4

tar -zxf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/local/lib -bindir=/usr/bin --sysconfdir=/usr/local/etc
make && make install

1
2
3
4
5

tar -zxf mysql-connector-odbc-5.3.13-linux-glibc2.12-x86-64bit.tar.gz
cd mysql-connector-odbc-5.3.13-linux-glibc2.12-x86-64bit/lib
cp libmyodbc5w.so /usr/local/lib
cd ..
cd bin

1

./myodbc-installer  -d -a -n "MySQL ODBC 5.3 Driver"  -t "DRIVER=/usr/lib/libmyodbc5w.so;SETUP=/usr/lib/libmyodbc5w.so"

1

vi /etc/odbc.ini

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

[mysqldb]
Driver      = /usr/local/lib/libmyodbc5w.so
Description = MyODBC 5 Driver DSN
SERVER      = 192.168.1.71
PORT        = 3306
USER        = root
Password    = root
Database    = test
OPTION      = 3
charset=UTF8

1

isql mysqldb -v

1
2

cd $ORACLE_HOME/hs
dg4odbc

1
2

cd $ORACLE_HOME/hs/admin
vi initmysqldb.ora

1
2
3
4
5
6

HS_FDS_CONNECT_INFO = mysqldb
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE= AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR= UCS2
set ODBCINI=/etc/odbc.ini

1

vi $ORACLE_HOME/network/admin/listener.ora

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /data/oracle

SID_LIST_LISTENER=
      (SID_LIST =
       (SID_DESC =
        (GLOBAL_DBNAME = orcl)
        (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
        (SID_NAME = orcl)
    )
     (SID_DESC =
       (SID_NAME = mysqldb)	#前面定义的SID 
       (ORACLE_HOME = /data/oracle/product/11.2.0/db_1) 
       (PROGRAM = dg4odbc)	#11g为dg4odbc
     )
   )

1

lsnrctl reload

1

vi $ORACLE_HOME/network/admin/tnsnames.ora

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysqldb =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST=localhost)(PORT=1521))    
     )
     (CONNECT_DATA =
       (SID = mysqldb)	#前面定义的SID 
     )
     (HS=OK)
   )

1

create public database link mysqldb connect to "root" identified by "root" using 'mysqldb';

1

select * from "test"@mysqldb;

1
2
3
4
5
6

SYS@vbox66in>select "id" from t1@myodbc1;
select "id" from t1@myodbc1
*
第 1 行出现错误:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[

1
2
3
4
5
6
7

SYS@vbox66in>select "id" from "t1"@myodbc1;
select "id" from "t1"@myodbc1
*
第 1 行出现错误:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息: ORA-28541:
HS 初始化文件的第 8 行发生错误。 ORA-02063:
紧接着 2 lines (起自 MYODBC1)

1
2
3
4
5
6
7
8

SYS@vbox66in>select * from a2@myodbc1;
select * from a2@myodbc1
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
[MySQL][ODBC 5.1 Driver][mysqld-5.7.21-log]Table 'test.A2' doesn't exist
{42S02,NativeErr = 1146}
ORA-02063: 紧接着 2 lines (起自 MYODBC1)