SQL Server2008 link remote MySQL

SQL Server2008 link remote MySQL

test Environment Preparation :

Operating Systems : Windows Server R2,CentOS 7.2 (ip:192.168.2.5)

SQL SERVER version : SQL

MySQL version:MySQL 5.7

1. Download mysql-connector-odbc

Https://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.7-winx64.msi

2. Download Visual C + + redistributable Packages for visual Studio component, such as failure to install the component, may be the following error

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/8D/F1/wKioL1iv7LSx_1LqAAA9s0lN-NA451.jpg-wh_500x0-wm_ 3-wmp_4-s_1144738517.jpg "title=" error. jpg "alt=" wkiol1iv7lsx_1lqaaa9s0ln-na451.jpg-wh_50 "/>

Https://download.microsoft.com/download/F/3/5/F3500770-8A08-488E-94B6-17A1E1DD526F/vcredist_x64.exe

2. window Server installation mysql-connector-odbc driver

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8D/F3/wKiom1iv7Mzww5WzAABY-T_u76c815.jpg-wh_500x0-wm_ 3-wmp_4-s_382184954.jpg "style=" Float:none; "title=" 1.jpg "alt=" Wkiom1iv7mzww5wzaaby-t_u76c815.jpg-wh_50 "/>

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8D/F1/wKioL1iv7M3Qe8f4AABzBYWRK1E032.jpg-wh_500x0-wm_ 3-wmp_4-s_268848019.jpg "style=" Float:none; "title=" 2.jpg "alt=" Wkiol1iv7m3qe8f4aabzbywrk1e032.jpg-wh_50 "/>

3. after installing mysql-connector-odbc , in the operating system control Panel -- management Tools - - data source ( ODBC)-- Add a system data source to the system DSN

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8D/F3/wKiom1iv7OqwBuk_AABaPw0E2YI939.jpg-wh_500x0-wm_ 3-wmp_4-s_2284250143.jpg "title=" 3.jpg "alt=" wkiom1iv7oqwbuk_aabapw0e2yi939.jpg-wh_50 "/>

4.Select "MySQL ODBC 5.3 ANSI Driver" to eject the following interface

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8D/F1/wKioL1iv7Pzz7zmgAAB0X1zvRBw569.jpg-wh_500x0-wm_ 3-wmp_4-s_2282455531.jpg "title=" 4.jpg "alt=" Wkiol1iv7pzz7zmgaab0x1zvrbw569.jpg-wh_50 "/>

Data source Name: DataSource names

Decription: description , write casually

IP of the Server:mysql server

The Port:mysql port , the default is 3306. It can be changed. .

User: Connect the account ( the account authorized on MySQL , and give the appropriate permission )

Password: Password

Database: Select the linked databases

and the test connection can be clicked , whether normal, such as

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/8D/F1/wKioL1iv7QjR8Xg9AABuBcqF_uA762.jpg-wh_500x0-wm_ 3-wmp_4-s_4049748576.jpg "title=" 5.jpg "alt=" wkiol1iv7qjr8xg9aabubcqf_ua762.jpg-wh_50 "/>

5.Windows server configuration ODBC data source completed  

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/8D/F3/wKiom1iv7RaS3pCMAABqhkTkZsQ290.jpg-wh_500x0-wm_ 3-wmp_4-s_1858368144.jpg "title=" 6.jpg "alt=" Wkiom1iv7ras3pcmaabqhktkzsq290.jpg-wh_50 "/>

6. SQL Database : Server Object -- linked server , Right-click " New Linked server "

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8D/F3/wKiom1iv7T6TqvniAAD-fRFGqKM143.jpg-wh_500x0-wm_ 3-wmp_4-s_352076090.jpg "style=" Float:none; "title=" 7.jpg "alt=" Wkiom1iv7t6tqvniaad-frfgqkm143.jpg-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8D/F3/wKiom1iv7T7ArYtlAACbUXtGVfk625.jpg-wh_500x0-wm_ 3-wmp_4-s_1293812121.jpg "style=" Float:none; "title=" 8.jpg "alt=" Wkiom1iv7t7arytlaacbuxtgvfk625.jpg-wh_50 "/>

" General " option to fill in the following information :

Linked Servers : MYSQL

Additional data Sources :

Access Interface : Microsoft OLE DB Provider for ODBC Drivers

Product name : can be arbitrarily named

data source : Tomysql(must use the data source created above)

" Security " need to fill in:

Select " Use this security context to establish a connection " :

telnet:root

use password:111111

Note : other default

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8D/F1/wKioL1iv7Vaxj3IBAABxMD5tW60848.jpg-wh_500x0-wm_ 3-wmp_4-s_1188785341.jpg "title=" 9.jpg "alt=" wkiol1iv7vaxj3ibaabxmd5tw60848.jpg-wh_50 "/>

7. SQL Server test Query

SELECT *  from  OPENQUERY(MYSQL, ' SELECT * from Tb01 ') 

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8D/F1/wKioL1iv7WLwcVfUAADnEbTyqqM849.jpg-wh_500x0-wm_ 3-wmp_4-s_510905305.jpg "title=" 10.jpg "alt=" wkiol1iv7wlwcvfuaadnebtyqqm849.jpg-wh_50 "/>

This article is from the "10,000-hour Law" blog, be sure to keep this source http://daisywei.blog.51cto.com/7837970/1900974

SQL Server2008 link remote MySQL