问题
有时您需要从 SQL Server 中访问 MySQL 中的数据。您可以导出和导入数据，但另一种选择是创建一个从 SQL Server 到 MySQL的链接服务器。在本技巧中，我们将介绍如何从 SQL Server 为 MySQL 完成此操作。
解决方案
在本技巧中，我们将展示 SQL Server 如何通过 链接服务器访问 MySQL 数据。您可以从http://dev.mysql.com/downloads/connector/odbc/获取最新版本的 Connector/ODBC 二进制文件和源代码的副本 。
在 Windows 上安装 Connector/ODBC 驱动程序之前：
- 确保您的 Microsoft 数据访问组件 (MDAC) 是最新的。
- 确保您的系统上有 Microsoft Visual C++ 2013 Redistributable Package。
完成安装的步骤非常简单，按照工具中的安装步骤操作即可。
安装驱动程序后，您将在 ODBC 数据源管理器中看到该驱动程序。
要从 SQL Server 连接，我们将通过链接服务器创建连接，在此之前我们需要了解我们将使用的连接字符串。
例如：
Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=database_name;OPTION=134217728;PWD=user_password;UID=user_identification;SERVER=server_name下表包含可用于连接字符串的参数。
| 范围 | 默认值 | 评论 |
|---|---|---|
| 用户 | ODBC | 用于连接 MySQL 的用户名。 |
| uid | ODBC | 与用户同义。在 3.51.16 中添加。 |
| 服务器 | 本地主机 | MySQL 服务器的主机名。 |
| 数据库 | 默认数据库。 | |
| 选项 | 0 | 指定连接器/ODBC 工作方式的选项。 |
| 港口 | 3306 | 如果服务器不是 localhost，则要使用的 TCP/IP 端口。 |
| 初始化 | 初步声明。连接到 MySQL 时要执行的语句。在 3.51 版中，该参数称为 stmt。驱动程序支持仅在初始连接时执行的初始语句。 | |
| 密码 | 服务器上用户帐户的密码。 | |
| 密码 | 与密码同义。在 3.51.16 中添加。 | |
| 插座 | 如果服务器是 localhost，则要连接的 Unix 套接字文件或 Windows 命名管道。 | |
| sslca | 包含信任 SSL CA 列表的文件的路径。在 3.51.16 中添加。 | |
| sslcapath | 包含 PEM 格式的受信任 SSL CA 证书的目录的路径。在 3.51.16 中添加。 | |
| sslcert | 用于建立安全连接的 SSL 证书文件的名称。在 3.51.16 中添加。 | |
| 密码器 | 用于 SSL 加密的允许密码列表。密码列表与 openssl ciphers 命令的格式相同。在 3.51.16 中添加。 | |
| sslkey | 用于建立安全连接的 SSL 密钥文件的名称。在 3.51.16 中添加。 | |
| rsakey | 包含使用 MySQL 的 SHA256 身份验证插件的 RSA 公钥的 PEM 文件的全路径名。在 5.3.4 中添加。 | |
| 验证 | 如果设置为 1，SSL 证书将在与 MySQL 连接一起使用时进行验证。如果未设置，则默认行为是忽略 SSL 证书验证。 | |
| 字符集 | 用于连接的字符集。在 3.51.17 中添加。 | |
| 读取超时 | 尝试从服务器读取的超时时间（以秒为单位）。每次尝试都使用这个超时值，并且在必要时会重试，因此总有效超时值是选项值的三倍。您可以设置该值，以便在 TCP/IP Close_Wait_Timeout 值 10 分钟之前检测到丢失的连接。此选项仅适用于 TCP/IP 连接，并且仅适用于 MySQL 5.1.12 之前的 Windows。对应 MySQL 客户端库的 MYSQL_OPT_READ_TIMEOUT 选项。在 3.51.27 中添加。 | |
| 写超时 | 尝试写入服务器的超时时间（以秒为单位）。每次尝试都使用此超时值，并且如有必要会进行 net_retry_count 次重试，因此总有效超时值为 net_retry_count 次选项值。此选项仅适用于 TCP/IP 连接，并且仅适用于 MySQL 5.1.12 之前的 Windows。对应 MySQL 客户端库的 MYSQL_OPT_WRITE_TIMEOUT 选项。在 3.51.27 中添加。 | |
| 交互的 | 如果设置为 1，则启用 mysql_real_connect 的 CLIENT_INTERACTIVEconnection 选项。 | |
| 预取 | 0 | 当设置为非零值 N 时，会导致连接中的所有查询一次返回 N 行而不是整个结果集。对于查询非常大的表很有用，在这种情况下一次检索整个结果集是不切实际的。您可以一次滚动浏览结果集，N 条记录。此选项仅适用于只进游标。设置选项参数 MULTI_STATEMENTSis 时不起作用。它可以与选项参数 NO_CACHE 结合使用。它在 ADO 应用程序中的行为是未定义的：预取可能发生也可能不发生。 |
| no_ssps | 0 | 在 Connector/ODBC 5.2 中，默认情况下使用服务器端准备好的语句。当此选项设置为非零值时，在客户端模拟准备好的语句，这与 5.1 和 3.51 中的行为相同。在 5.2 中添加。 |
| can_handle_exp_pwd | 0 | 指示应用程序可以处理过期密码，该密码由 SQL 状态 08004（“服务器拒绝连接”）和本机错误代码 ER_MUST_CHANGE_PASSWORD_LOGIN (1862) 表示。该连接是“沙盒”的，除了发出 SET PASSWORD 语句之外什么也做不了。要在这种情况下建立连接，您的应用程序必须在开始时使用 initstmt 连接选项设置新密码，或者在连接后立即发出 SET PASSWORD 语句。一旦过期密码被重置，对连接的限制就会被解除。有关 MySQL 服务器帐户密码过期的详细信息，请参阅 ALTER USER 语法。在 5.2.4 中添加。 |
不同配置的推荐连接器/ODBC 选项值
选项参数用于告诉连接器/ODBC 客户端不是 100% 兼容 ODBC 。
| 配置 | 参数设置 | 期权价值 |
|---|---|---|
| 微软访问，Visual Basic | FOUND_ROWS=1； | 2 |
| Microsoft Access（改进了 DELETE 查询） | FOUND_ROWS=1；DYNAMIC_CURSOR=1； | 34 |
| 微软 SQL 服务器 | COLUMN_SIZE_S32=1； | 134217728 |
| 行数过多的大表 | COMPRESSED_PROTO=1； | 2048 |
| Sybase PowerBuilder | IGNORE_SPACE=1;FLAG_SAFE=1; | 135168 |
| 查询日志生成（调试模式） | LOG_QUERY=1; | 524288 |
| 没有缓存结果的大表 | NO_CACHE=1;FORWARD_CURSOR=1; | 3145728 |
| 运行全表“SELECT * FROM ...”查询的应用程序，但仅从结果 PREFETCH=N 中读取少量 (N) 行 | 不适用 |
创建与 MySQL 的 SQL Server 链接服务器
在SQL Server Management Studio中，打开对象资源管理器，展开服务器对象，右键单击链接服务器，然后单击新建链接服务器。在“常规”页面上执行以下操作：
- 链接服务器- 键入您要链接到的 MySQL 服务器的名称。
- 服务器类型- 选择其他数据源
- Provider - 指定一个 OLE DB Provider，在这种情况下，我选择 Microsoft OLE DB Provider for ODBC Drivers
- Provider string - 输入我们上面讨论的连接字符串 Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=database_name;OPTION=134217728;PWD=user_password;UID=user_identification;SERVER=server_name)
在 Server Options 页面上，我们需要将 RPC 和 RPC Out 设置调整为 True。将 RPC（远程过程调用）视为从服务器 1 远程运行到链接服务器 2 的存储过程。
您还可以使用以下 T-SQL 命令创建链接服务器：
使用 [主]
去
EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL'
,@srvproduct=N'MySQL'
,@provider=N'MSDASQL'
,@provstr=N'Driver={MySQL ODBC 5.3 ANSI 驱动程序};DATABASE=test;OPTION=134217728;PWD=P@ssw0rd;UID=test;SERVER=srvtest'
去
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'true'
去
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'true'
去
从 SQL Server 访问 MySQL 数据
创建链接服务器后， 您将能够在对象资源管理器中看到远程对象。
我们还可以运行查询来访问数据。
连接器/ODBC 性能
连接器/ODBC 驱动程序已经过优化，可提供非常快的性能。如果您遇到连接器/ODBC 的性能问题或注意到简单查询的大量磁盘活动，则需要检查多个方面：
- 确保未启用 ODBC 跟踪。启用跟踪后，ODBC 管理器会在跟踪文件中记录大量信息。您可以使用 ODBC 数据源管理器的跟踪面板在 Windows 中检查和禁用跟踪。
- 确保您使用的是标准版本的驱动程序，而不是调试版本。调试版本包括额外的检查和报告措施。
- 确保未启用驱动程序跟踪（选项值 4）和查询日志记录（选项值 524288）。