黄东旭解析 TiDB 的核心优势
462
2024-04-30
某客户现有系统大量使用dblink+物化视图+同义词的方式进行对基础代码库的访问,现基础代码库拟使用tidb进行国产化替换,因链路复杂固继续使用dblink为最稳定的方案。原业务系统调用关系图如下:
ORACLE透明网关(*** Transparent Gateway)可以解决ORACLE数据库和非ORACLE数据库交互数据的需求。在一个异构环境中,通过ORACLE透明网关可以访问其他类型数据库,例如***、***、 mysql。
ORACLE透明网关需要*** Net与ORACLE数据库进行通信,在ORACLE透明网关安装后,必须为ORACLE透明网关配置监听。*** Net 侦听器侦听来自 *** 数据库的传入请求。为了让*** Net Listener为透明网关侦听, 必须将有关透明网关的信息添加到*** Net Listener配置文件listener.ora中。该文件默认位于$ORACLE_HOME/network/admin下,其中$ORACLE_HOME是安装透明网关的目录。如果透明网关的安装目录和ORACLE实例一致的话,那么它会和数据库共用监听文件。
***使用DG4ODBC数据网关连接其它非***数据库,利用tidb上层完全兼容mysql的特性来实现。
其数据流为***——dg4odbc——odbc——mysql(TIDB)
根据原理数据流向图可知数据流向经过DG4ODBC, ODBC Driver Manager, ODBC Driver组件,下面根据组件进行配置。
若无特殊说明所有步骤均在***端进行操作。
***版本号
Release 12.2.0.1.0tidb版本
Server version: 5.7.25-TiDB-v5.4.0 TiDB Server (Apache License 2.0)ORACLE服务器系统版本
Red Hat Enterprise Linux Server release 7.9 (Maipo)***从11G开始默认安装了odbc透明网关
验证:
***用户 bjzxtestdb:/home/***(orclbk)$cd $ORACLE_HOME/hs bjzxtestdb:/u01/app/***/product/12.2/db/hs(orclbk)$dg4odbc *** Corporation --- FRIDAY JUN 17 2022 11:43:17.621 Heterogeneous Agent Release 12.2.0.1.0 - 64bit Production Built with *** Database Gateway for ODBC bjzxtestdb:/home/***(orclbk)$file $ORACLE_HOME/bin/dg4odbc /u01/app/***/product/12.2/db/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=644a5ecc0ded4f35826b0bb55a75871dd6203a51, not stripped上述命令表面已经有64位的DG4ODBC
到http://www.unixodbc.org/下载最新的 unixodbc,当前最新版本为 unixODBC-2.3.11.tar.gz
root用户安装 tar -zxvf unixODBC-2.3.11.tar.gz cd /home/***/unixODBC-2.3.11 ./configure --prefix=/usr/local/unixODBC-2.3.11 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc make && make install查看是否安装后版本以及配置文件路径
[root@bjzxtestdb ~]# odbc_config --version 2.3.11 [root@bjzxtestdb~]# odbcinst -j unixODBC 2.3.11 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首先查看tidb目前支持的版本
根据实际环境下载最新兼容版本,***
https://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads
笔者使用的为红帽系统下载版本为
mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm
直接安装
rpm -ivh mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm5、配置 ODBC Driver
参考地址:
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-unix.html
[root@bjzxtestdb ~]# odbcinst -j unixODBC 2.3.11 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 cat /etc/odbc.ini [ODBC Data Sources] myodbc8w = MyODBC 8.0 UNICODE Driver DSN myodbc8a = MyODBC 8.0 ANSI Driver DSN [myodbc8w] Description = Connector/ODBC 8.0 UNICODE Driver DSN Driver = /usr/lib64/libmyodbc8w.so SERVER = 172.XX.XX.81 USER = root PASSWORD = XXX PORT = 4001 DATABASE = XXXX_sit OPTION = 0 TRACE = OFF注:Database 区分大小写
测试连通性
[root@bjzxtestdb odbc]# isql myodbc8w -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | echo [string] | | quit | | | +---------------------------------------+ SQL> show tables; +------------------------------------+ | Tables_in_rbac_sit | +------------------------------------+ | ACT_EVT_LOG | | ACT_EVT_LOG_SEQ | | ACT_GE_BYTEARRAY | | ACT_GE_PROPERTY | | ACT_HI_ACTINST | | ACT_HI_ATTACHMENT | | ACT_HI_COMMENT | | ACT_HI_DETAIL |注:透明网关的TNS配置是有HS=OK
必须以init开头
bjzxtestdb:/u01/app/***/product/12.2/db/hs/admin(orclbk)$pwd /u01/app/***/product/12.2/db/hs/admin bjzxtestdb:/u01/app/***/product/12.2/db/hs/admin(orclbk)$cat initmyodbc8w.ora HS_FDS_CONNECT_INFO=myodbc8w#与listener.ora名称对应 HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so#odbc的lib路径 HS_FDS_SUPPORT_STATISTICS=FALSE#默认#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8#ORACLE字符集 HS_NLS_NCHAR = UCS2#异构数据库字符编码 UTF8一般对应此参数 HS_FDS_SUPPORT_STATISTICS = FALSE HS_FDS_TRACE_LEVEL = debug#日志级别 #HS_KEEP_REMOTE_COLUMN_SIZE=ALL 字符转换相关参数含义连接
https://xy2401.com/local-doc-***-19c.zh/content/tginu/database-gateway-for-informix-initialization-parameters.html#GUID-2AB55A84-36E7-437D-BDC9-58ADDCE35F2D
重启监听并确认
lsnrctl reload LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUN-2022 15:17:42 Copyright (c) 1991, 2016, ***. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bjzxtestdb)(PORT=1521)))STATUS of the LISTENER ------------------------Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 07-APR-2022 18:32:58 Uptime 70 days 20 hr. 44 min. 43 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/***/product/12.2/db/network/admin/listener.ora Listener Log File /u01/app/***/diag/tnslsnr/bjzxtestdb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bjzxtestdb)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "myodbc8w" has 1 instance(s). Instance "myodbc8w", status UNKNOWN, has 1 handler(s) for this service... Service "orclbk" has 1 instance(s). Instance "orclbk", status READY, has 1 handler(s) for this service...The command completed successfully -----------------------------------------------------------TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUN-2022 15:18:25 Copyright (c) 1997, 2016, ***. All rights reserved. Used parameter files: /u01/app/***/product/12.2/db/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST= bjzxtestdb) (PORT=1521)) (CONNECT_DATA= (SID=myodbc8w)) (HS=OK)) OK (0 msec)到此系统级别配置完成,可按需进行创建dblink进行测试
创建dblink并验证
***端 create public database link tidbtest connect to "root" identified by "XXXXX" using myodbc8w; select count(*) from “rbac_user”@tidbtest; COUNT(*) ----------4567 mysql端 MySQL [(none)]> MySQL [(none)]> use rbac_sit; Database changed MySQL [rbac_sit]> select count(*) from rbac_user; +----------+ | count(*) | +----------+ | 4567 | +----------+ 1 row in set (0.03 sec)创建物化视图并验证
create materialized view rbac_user as select * from "rbac_user"@tidbtest; select count(*) from rbac_user; COUNT(*) ---------- 4567创建同义词并验证
CREATE SYNONYM "TEST"."RBAC_USER1" FOR "RBAC_USER"@"tidbtest"; SQL> select count(*) from rbac_user1; COUNT(*) ---------- 4567此类似错误为varchar字符转换时存在的问题,对于mysql中varchar类型的字符,网关默认会转换成***中nvarchar2类型但***中nvarchar2类型存在最大长度限制,当长度大于最大限制时则网关会转换成long字段类型,此时便会存在转换问题。
解决方案
查看ORACLE中nvarchar2长度限制
从12.1开始,取决于两个设置 —— MAX_STRING_SIZE和国家字符集 16383 if MAX_STRING_SIZE=EXTENDED and the national character set is AL16UTF16 32767 if MAX_STRING_SIZE = EXTENDED and the national character set is UTF8 2000 if MAX_STRING_SIZE = STANDARD and the national character set is AL16UTF16 4000 if MAX_STRING_SIZE = STANDARD and the national character set is UTF8 select parameter,value from nls_database_parameters where parameter like NLS_NCHAR_%; show parameter MAX_STRING_SIZE建议修改静态参数MAX_STRING_SIZE
CONNNECT SYS / AS SYSDBA SHUTDOWN IMMEDIATE; STARTUP UPGRADE; ALTER SYSTEM SET max_string_size=extended; START $ORACLE_HOME/rdbms/admin/utl32k.sql SHUTDOWN IMMEDIATE; STARTUP;错误原因以及处理方法:hs/admin/init[sid].ora里配置错误
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so,应该odbc的Lib包路径。
错误原因以及处理方法:执行的查询操作,表名需要带双引号,因为mysql默认表名区分大小写,而***是不区分大小写。
错误原因以及处理方法:hs/admin/init[sid].ora里配置字符集错误
HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk字符集不正确,应配置***数据库字符集
HS_NLS_NCHAR = UCS2 有奇效
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。