【MySQL】MariaDB使用connect存储引擎访问SQLServer中的表

借助connect存储引擎，Mariadb可以访问任何ODBC数据源。

本文是以CentOS7为例。安装的内容有：ODBC driver，unixODBC

 

1.下载ODBC对应linux的驱动
下载地址：

https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver16

 

2.使用提供的脚本build_dm.sh安装unixODBC

bash build_dm.sh
....
The script is provided as a convenience to you as-is, without any express
or implied warranties of any kind.  Microsoft is not liable for any issues
arising out of your use of the script.

Enter 'YES' to have this script continue: YES

Verifying processor and operating system ................................... OK
Verifying wget is installed ................................................ OK
Verifying tar is installed ................................................. OK
Verifying make is installed ................................................ OK
Downloading unixODBC 2.3.0 DriverManager ................................... OK
Unpacking unixODBC 2.3.0 DriverManager ..................................... OK
Configuring unixODBC 2.3.0 DriverManager ................................... OK
Building unixODBC 2.3.0 DriverManager ...................................... OK
Build of the unixODBC 2.3.0 DriverManager complete.

Run the command 'cd /tmp/unixODBC.32235.28222.16428/unixODBC-2.3.0; make install' to install the driver manager.

然后安装

cd /tmp/unixODBC.32235.28222.16428/unixODBC-2.3.0; make install

如果发生依赖失败，可以执行：

yum install yum-utils && yum-builddep unixODBC

如果yum安装的是最新的包，驱动有可能失败。

 

3.安装MSSQL包

bash install.sh install

接受协议

Enter YES to accept the license or anything else to terminate the installation: YES

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ................................. NOT FOUND
unixODBC utilities (odbc_config and odbcinst) installed ............ NOT CHECKED
unixODBC Driver Manager version 2.3.0 installed .................... NOT CHECKED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED

也可以通过yum install unixODBC安装或更新。

可以通过/etc/odbcinst.ini查看MSSQL包是否存在。

 

4.安装connect引擎
在服务器上执行：

yum install MariaDB-connect-engine

登录mariadb执行：

INSTALL SONAME 'ha_connect.so';

 

5.创建表
根据文件/etc/odbcinst.ini的信息创建表：

CREATE TABLE mssql_table ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='Driver=SQL Server Native Client 11.0;Server=ms-sql01;Database=old_version_migration_data;UID=mariadb;PWD=password' `TABLE_TYPE`='ODBC'

 

https://mariadb.com/resources/blog/getting-microsoft-sql-server-data-into-mariadb-with-the-connect-storage-engine/