Article / 文章中心

如何在 SQL Server 中创建和配置链接服务器以连接到 MySQL

发布时间:2022-03-11 点击数:841
MSSQL企业中的使用还是很普遍的,尤其是在中小企业中,MSSQL数据库配置链接服务器也是一个常见的应用,最近在生产环境中碰到这样一个案例,所以作了一下笔记。

概述

本文将指导您完成在 SSMS 中成功创建链接服务器以连接到 MySQL 数据库的所有必要步骤。

本文分为三个部分:

  • 为 MySQL 安装ODBC驱动程序。

  • 配置 ODBC 驱动程序以连接到 MySQL 数据库。

  • 使用 ODBC 驱动程序创建和配置链接服务器。

编辑搜图

什么是链接服务器?

MSSQL 中的链接服务器是连接到给定服务器的其他数据库服务器,可以查询和操作其他数据库中的数据。例如,我们可以将一些 MySQL 数据库链接到 MSSQL,并像使用 MSSQL 上的任何其他数据库一样使用它。

编辑搜图

1、为 MySQL 安装 ODBC 驱动程序

ODBC 代表开放式数据库连接(连接器)。它是微软在 1990 年代开发的。通常,即用于访问数据库系统的 API(应用程序编程接口)。对于非 Windows 操作系统,使用JDBC(Java 数据库连接)。在 Windows 上安装 MySQL 的 ODBC 驱动程序之前,请确保 Microsoft 数据访问组件 (MDAC) 是最新的,并且您的系统上安装 了Microsoft Visual C++ 2013 Redistributable Package 。你可以下载和安装适用于 Windows 的 MySQL ODBC 驱动程序。可以安装两个版本的适用于 Windows 的 MySQL ODBC 驱动程序,具体取决于将与哪个应用程序一起使用:

编辑搜图

  • mysql-connector-odbc-8.0.17-win32.msi 用于 32 位应用程序。

  • mysql-connector-odbc-8.0.17-winx64.msi 用于 64 位应用程序。

安装适用于 Windows 的 MySQL ODBC 驱动程序非常简单。双击下载的文件,将出现欢迎对话框:

编辑搜图

按下一步按钮后,将出现许可协议对话框。如果您同意许可协议,请按我接受许可协议中的条款单选按钮,然后单击下一步按钮:

编辑搜图

在“设置类型”对话框下,选择“典型”单选按钮并按“下一步”按钮:

编辑搜图

“准备安装程序”对话框显示将安装的内容和位置。按安装按钮安装 ODBC 驱动程序:

编辑搜图

几秒钟后,MySQL ODBC 驱动程序的安装完成:

编辑搜图

要确认机器上安装了 MySQL 的 ODBC 驱动程序,可以从控制面板检查:

编辑搜图

另一种检查方法是通过ODBC 数据源管理器对话框:

编辑搜图

在ODBC 数据源管理器对话框 的驱动程序选项卡下,检查 MySQL ODBC 驱动程序是否存在:

编辑搜图

2、配置 ODBC 驱动程序以连接到 MySQL 数据库

要使用 ODBC 驱动程序连接到 MySQL 数据库,请在“ ODBC 数据源管理器”对话框中的“系统 DSN ”选项卡下,按“添加”按钮:

编辑搜图

在Create New Data Source对话框中,选择MySQL ODBC Driver并按Finish按钮:

编辑搜图

在MySQL 连接器/ODBC 数据源配置对话框中:

编辑搜图

对于数据源名称文本框,选择输入数据源名称。在描述文本框中,根据需要输入数据源的描述。通过选择适当的单选按钮,使用TCP/IP 服务器或命名管道连接方法连接到 MySQL。

在此示例中,选择了TCP/IP Server单选按钮。在文本框中,输入 MySQL 服务器的主机名或 IP 地址。默认情况下,主机名是localhost,IP 地址是127.0.0.1。在端口框中,输入列出 MySQL 服务器的 TCP/IP 端口。默认为3306端口。

在“用户”框中,键入连接到 MySQL 数据库所需的用户名,并在“密码”框中,键入用户密码。在Database组合框下,选择要建立连接的数据库:

编辑搜图

要测试它是否连接到正确配置的 MySQL 数据库,请按测试按钮。如果连接建立成功,会出现以下信息:

编辑搜图

此外,数据源名称将出现在ODBC 数据源管理器对话框 的系统 DSN选项卡中:

编辑搜图

3、使用 ODBC 驱动程序创建和配置链接服务器

现在当 MySQL 的 ODBC 驱动程序已经安装并配置了连接 MySQL 数据库的 ODBC 驱动程序后,就可以开始在 SSMS 中配置 Linked Server 以连接 MySQL。

转到 SSMS,在对象资源管理器中,在Server Objects文件夹下,右键单击Linked Servers文件夹,然后从菜单中选择New Linked Server选项:

编辑搜图

将出现新建链接服务器对话框。这里将输入配置以连接到 MySQL 服务器:

编辑搜图

在常规选项卡的链接服务器文本框中,输入链接服务器的名称(例如 MYSQL_SERVER)。

选择其他数据源单选按钮并从提供程序列表中选择Microsoft OLE DB Provider for ODBC Drivers项:

编辑搜图

在产品名称框下,输入任何适当的(有效)名称。对于数据源,应输入 ODBC 数据源的名称:

编辑搜图

在Security选项卡中,单击Be made using this security context单选按钮,然后在Remote login和With password框中,输入 MySQL 服务器实例中存在的用户名和密码,该实例被选为数据源:

编辑搜图

在Server Options 选项卡下,将RPC和RPC Out字段设置为True:

编辑搜图

如果这两个选项未设置为 true 并执行如下代码:

复制
EXEC ('SELECT * FROM test.table1') AT MYSQL_SERVER1.

The following error may appear:

复制
Msg 7411, Level 16, State 1, Line 1 Server ‘MYSQL_SERVER’ is not configured for RPC.1.

设置“新建链接服务器”对话框 下的所有选项后,按“确定”按钮。新创建的链接服务器应该出现在Linked Servers文件夹中:

编辑搜图

在开始从 MySQL 数据库查询数据之前,转到Linked Server文件夹下的Providers文件夹,右键单击MSDASQL提供程序,然后从上下文菜单中选择Properties命令:

编辑搜图

在Provider Options对话框中,选中Nested queries、Level zero only、Allow in process、Support 'Like' operator复选框:

例如,如果未选中Allow in process复选框,则在执行如下代码时:

复制
SELECT *FROM OPENQUERY(MYSQL_SERVER, 'SELECT * FROM test.table1')1.2.

可能会出现以下错误消息:

复制
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider “MSDASQL” for linked server “MYSQL_SERVER” reported an error. Access denied. Msg 7350, Level 16, 
State 2, Line 1 Cannot get the column information from OLE DB provider “MSDASQL” for linked server “MYSQL_SERVER”.1.2.3.4.

小结

MSSQL企业中的使用还是很普遍的,尤其是在中小企业中,MSSQL数据库配置链接服务器也是一个常见的应用,最近在生产环境中碰到这样一个案例,所以作了一下笔记,以备不时之需。