unixODBC的安装与使用

数据库 2024-10-10

前言

已经好多年没写日志了，最近在一个项目中有需要数据库直接另一数据库的需求，折腾了两天，通过unixODBC解决了，写一下总结，与大家分享一下。

需求

项目运行于linux服务器（当前环境为Centos）
项目使用的是mariadb数据库；
另一数据库为异种数据库，可能是mysql,oracle,Sqlserver等等；
曾经因另一异种数据库数据量较小，使用定期同步功能，将数据同步到项目数据库，但现在的环境中目标数据库数据量较大，定期同步都无法在确定时间内完成，所以考虑数据库端能够直接异种数据库以达到不用修改代码即可以实现数据查询的目的。

解决方案

经多方尝试，最后选择unixODBC来实现以上目标。

unixODBC的安装

这一步相对比较简单，如果你的CentOS源比较合适的话，yum可以直接安装，实在水行，也可以到unixODBC官网下载，unixODBC本身是免费的。

1	yum install -y unixODBC

各种数据库驱动的安装与配置

虽然unixODBC本身是免费的，但各种驱动程序官网虽然提供，但基本是付费的；但各种数据库官网同样提驱动，可以去各数据库官网下载，都是免费的，只是可能需要在各网站有相应账号，注册即可。这里提供本人下载的文件名以供参考。
如果通过yum安装的 unixODBC,会提供odbcinst命令，且默认就安装了两种(PostgreSQL和MySQL)驱动。
通过以下命令查看unixODBC的配置

[root@localhost ~]# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@localhost ~]#

默认驱动配置

[root@localhost ~]# cat /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[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
[root@localhost ~]#

安装其他驱动并配置

我从oracle官网下载了以下文件，请注意文件版本一定要配套，sqlplus并不是必须的，只是为了验证odbc配置和查询结果用。

oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpm
oracle-instantclient-devel-21.1.0.0.0-1.x86_64.rpm
oracle-instantclient-odbc-21.1.0.0.0-1.x86_64.rpm
oracle-instantclient-sqlplus-21.1.0.0.0-1.x86_64.rpm

依次安装好后，驱动程序一般会出现在 /usr/lib/oracle/21/client64/lib目录下，既然安装了sqlplus可以试试能否正常连接oracle

sqlplus 账号/密码@IP:端口/实例名

SQL*Plus: Release 12.2.0.1.0 Production on 星期四 10月 10 11:14:52 2024

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

上次成功登录时间: 星期四 10月 10 2024 03:30:58 +08:00

连接到: 
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

打开/etc/odbcinst.ini增加以下内容

[Oracle]
Description     = ODBC for Oracle
Driver          = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
FileUsage       = 1

odbc配置

打开/etc/odbc.ini 文件进行配置

[root@localhost ~]# cat /etc/odbc.ini 
[ora]
DSN = ora

Driver = Oracle
DefaultDriver = Oracle
ServerName = //ip:端口/实例名
UserID = 数据库账号名
Password = 数据库密码

DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8}

Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = T
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192
AggregateSQLType=FLOAT


[root@localhost ~]#

这样就配置好了一个新的数据源名称，叫ora,后面会在mariadb中使用到这个名称。

odbc的验证

安装好unixODBC后同时会安装isql命令，可以检查配置是否成功

isql ora
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

如果成功进入，表明配置是成功的，如果不能进入请仔细检查配置。

在mariadb中的使用

mariadb中需要有 connect引擎

首先，mariadb中需要有 connect引擎，如果没有请先安装

1	yum install MariaDB-connect-engine -y

之后使用 sql命令查看

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.6.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show engines;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                                         | Transactions | XA   | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | Stores tables as CSV files                                                                      | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                           | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                       | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint                         | NO           | NO   | NO         |
| CONNECT            | YES     | Management of External Data (SQL/NOSQL/MED), including Rest query results                       | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                                  | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables                | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                              | NO           | NO   | NO         |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.000 sec)

MariaDB [(none)]>

从上表中，如果显示 CONNECT | YES ，说明 connect引擎安装成功，否则请检查和设置。

mariadb中创建别名表

成功后，继续在sql命令状态执行

DROP TABLE IF EXISTS 别名表;
create OR REPLACE table 别名表 
CHARSET='utf8mb4' 
ENGINE='CONNECT' 
table_type='ODBC'
tabname='oracle中的真实表或者视图'
CONNECTION='DSN=ora;';

这样就在mariadb中创建了一张别名表对应到oracle中的真实表，后续我们就可以直接在mariadb中查询另一个数据库中的数据了。

注意事项

文件 /etc/odbc.ini中的 Driver = Oracle,这里的Oracle是/etc/odbcinst.ini中方括号内的名称
如果在 sqlplus中查询显示中文正常，而在 isql中查询显示的是乱码，需要增加 DMEnvAttr 这个配置，特别重要，少有文档提到这个怎么配置
mariadb中创建别名时 CONNECTION=’DSN=ora;’; 这里的ora是 /etc/odbc.ini里 DSN 填写的值，在 odbc.ini文件中方括号内的内容最后与DSN后面的值一致。

赏