【MySQL】MariaDB使用connect引擎直接访问SQLServer数据库
2022-09-30 15:02 abce 阅读(651) 评论(0) 编辑 收藏 举报场景：
mariadb通过connect引擎，访问sqlserver2008中的数据。
1.安装connect存储引擎
如果mariadb是使用二进制包安装的话，默认已经安装了connect存储引擎
否则就需要执行手动安装。比如：
centos：
sudo yum install MariaDB-connect-engine
debain：
sudo apt-get install mariadb-plugin-connect
2.mariadb安装插件
登录mariadb数据库执行：
> INSTALL SONAME 'ha_connect';
安装插件后查看：
>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 | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
3.安装unixODBC
centos：
sudo yum install unixODBC
debain：
sudo apt-get install libodbc1
4.安装微软odbc驱动
下载地址：
https://learn.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver16#previous-releases
$ wget https://packages.microsoft.com/rhel/7/prod/msodbcsql17-17.10.1.1-1.x86_64.rpm$ sudo rpm -ivh msodbcsql17-17.10.1.1-1.x86_64.rpm
这里顺便也把mssql客户端工具也安装好，方便下面的测试过程
$ wget https://packages.microsoft.com/rhel/7/prod/mssql-tools-17.10.1.1-1.x86_64.rpm $ sudo rpm -ivh mssql-tools-17.10.1.1-1.x86_64.rpm
5.安装完成测试测试
$ sudo odbcinst -q -d -n "ODBC Driver 17 for SQL Server"
我们安装的微软odbc驱动是版本17
$ sudo odbcinst -q -d -n "ODBC Driver 17 for SQL Server" [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1 UsageCount=1
$ sudo 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
$ sudo /opt/mssql-tools/bin/sqlcmd -S 192.168.56.111 -U sa -P sa01 -d master -Q 'select "Hello World"' ----------- Hello World
$ sudo /opt/mssql-tools/bin/sqlcmd -S 192.168.56.111 -U sa -P sa01 -d master -Q 'select name from sys.databases' name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb test
6.创建数据据源DSN
先创建一个模板
$ sudo vim MariaDB_odbc_data_source_template.ini [SQLServer_DSN] Description = linux to sqlserver Driver = ODBC Driver 17 for SQL Server Server = 192.168.56.111 Port = 1433 UID=sa PWD=pw123 Trace = Yes TraceFile = /var/log/odbc.log
使用模板向/etc/odbcinst.ini文件中安装数据源
$ sudo odbcinst -i -s -l -f MariaDB_odbc_data_source_template.ini
验证DSN的配置
$ sudo isql -v SQLServer_DSN sa pw123 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select name from sys.databases; +---------------------------------------------------------------------------------------------------------------------------------+ | name | +---------------------------------------------------------------------------------------------------------------------------------+ | master | | tempdb | | model | | msdb | | test | +---------------------------------------------------------------------------------------------------------------------------------+ SQLRowCount returns 0 5 rows fetched
$ sudo -u mysql isql -v SQLServer_DSN sa pw123 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
7.新建connect引擎的表
CREATE TABLE `products` ( `id` int(10) NOT NULL, `code` varchar(64) DEFAULT NULL `auto_rra` tinyint(3) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 CONNECTION='DSN=SQLServer_DSN;Database=test;UID=sa;PWD=pw123' `TABLE_TYPE`='ODBC' 1 row in set (0.001 sec)
8.验证
MySQL端插入数据(略)
也可以使用freedsTDS。FreeTDS是Unix和Linux library的一个集合，通过FreeTDS可以连接到MicrosoftSQL Server和Sybase DB。
本文地址：
参考：
https://mariadb.com/kb/en/creating-a-data-source-with-mariadb-connectorodbc/
https://www.dandelioncloud.cn/article/details/1493027647344156673
补充一下freedstds的配置
#安装依赖包
sudo yum install gcc sudo tar -xvf freetds-1.3.tar.gz cd freetds-1.3
#查看当前支持的tdsver版本(参数--with-tdsver)，选择对应支持的 MSSQL 版本
sduo ./configure --help sduo ./configure --prefix=/usr/local/freetds --enable-msdblib
其中：
--prefix=/usr/local/freetds FreeTDS的默认安装目录/usr/local/freetds
--enable-msdblib 允许 Microsoft的函数库
--with-tdsver=7.3 指定TDS的协议版本，不填写则默认5.0版，7.3支持MSSQL2008
安装
sudo make sudo make install
加入环境变量
sudo vim /etc/profile
查看版本
/usr/local/freetds/bin/tsql -C
配置：
sudo vim /usr/local/freetds/etc/freetds.conf 例如： [sqlserver1] host = xxx.xxx.xxx.xxx port = 1433 uid = 用户名 pwd = 密码 tds version = auto client charset = UTF-8
连接测试
/usr/local/freetds/bin/tsql -S sqlserver1 -U <用户名> -P <密码>
odbc相关配置
$ sudo more /etc/odbcinst.ini [FreeTDS] Description=FreeTDS ODBC Driver Driver=/usr/local/freetds/lib/libtdsodbc.so Setup=/usr/local/freetds/lib/libtdsodbc.so UsageCount=1 $ sudo more /etc/odbc.ini [sqlserver1] cription=SQL Server on xxx.xxx.xxx.xxx Driver=FreeTDS Servername=sqlserver1
