0%

Oracle dblink MySQL

下载安装包

需要下载两个安装包unixODBC-2.3.7和mysql-Connector-ODBC 5.3.13
unixODBC源码包http://www.unixodbc.org/unixODBC-2.3.7.tar.gz
mysql-connector-odbc-5.3.13包https://dev.mysql.com/downloads/connector/odbc
版本:5.3.13
平台:Linux - Generic
下载:mysql-connector-odbc-5.3.13-linux-glibc2.12-x86-64bit.tar.gz

安装unixODBC
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

安装mysql-connector-odbc(odbc 5.3及以上是libmyodbc5a.so和libmyodbc5w.so。其中c5a是Ansi版,c5w是Unicode版)

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"
配置odbc

编辑文件odbc.ini,把下面的内容加上去

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
配置Oracle

database gateway for odbc简称dg4odbc
oracle 11.2.0.4默认安装了odbc透明网关
验证

1
2
cd $ORACLE_HOME/hs
dg4odbc

hs配置

每个使用dg4odbc的实例,都必须单独一个”init*.ora”文件,文件命名规则:init+<网关sid>+.ora

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
)
)

重启监听后看下myodbc的服务是否已经启动

1
lsnrctl reload

配置tnsnames.ora

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)
)

创建dblink

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

检查dblink

1
select * from "test"@mysqldb;
常见问题

1.

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

原因:有一些内容显示不全,检查odbc监听文件,查看HS_LANGUAGE参数,配置为和数据库字符集一致

2.

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)

原因:NVARCHAR/NCHAR和图形数据类型通常以Unicode格式存储数据。unicode字符集因数据库的不同而不同,需要修改HS_NLS_NCHAR=UCS2

3.

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)

原因:MySQL数据中是区分大小写的,表需要用双引号引起来

-------------    本文结束  感谢您的阅读    -------------
请作者一杯咖啡。