July 19, 2012 at 7:51 am
I need to perform periodic updates of our web server's MySQL database (in the DMZ) from an internal SQL Server.
I've seen the ODBC connector for MySQL that's available to use with SSIS, but I'm not sure how to securely deal with the firewall, as I'm reluctant to open port 1433.
Googling didn't turn up too much for me. I have to believe this is a fairly common scenario. I could generate a SQL script from SQL Server for the INSERTs and execute that against MySQL with PHPMyAdmin, but I'd like something less manual. Ideally, an Agent job running SSIS or something like that.
Software: SQL Server 2005 Enterprise on LAN, MySQL 5.0.95 on DMZ (InnoDB, if that matters).
Thanks for any ideas you might have,
Rich
July 19, 2012 at 8:02 am
since you said "from an internal SQL Server.
" to "MySQL", that doesn't use 1433 at all.
if you wanted to connect to the MySQL database from your PC(which is also on the other side of the DMZ) could you?
once you have that ability, you can create a linked server on the SQL server to the MySQL database.
then you could insert/update to the proper table(s) in the mySQL database.
this forum post has become the definitive working example for me when i need to create a linked server to MySQL, but you'll have to resolve the connectivity tot eh DMZ first.
http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx
somewhere in that post, i harvested this procedure to create MySQL Linked servers: i notice it is using port 3306.
CREATE PROC uspCreateLinkToMySQLDB @linkedservername varchar(50), @mysqlip varchar(50), @dbname varchar(100), @username varchar(50), @password varchar(50) AS
--@linkedservername = the name you want your linked server to have
--@mysqlip = the ip address of your mysql database
--@dbname = the name of the mysql database you want to operate against. Without this, some of the features of openquery fail
--@username = the username you will use to connect to the mysql database
--@password = the password used by your username to connect to the mysql database
BEGIN
--DROP THE LINKED SERVER IF IT EXISTS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @linkedservername)
EXEC master.dbo.sp_dropserver @server=@linkedservername, @droplogins='droplogins'
--ADD THE LINKED SERVER
DECLARE @ProviderString varchar(1000)
Select @ProviderString = 'DRIVER={MySQL ODBC 5.1 Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'
EXEC master.dbo.sp_addlinkedserver
@server=@linkedservername,
@srvproduct='MySQL',
@provider='MSDASQL',
@provstr=@ProviderString
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'use remote collation', @optvalue=N'true'
EXEC sp_addlinkedsrvlogin @linkedservername, 'false', NULL, @username, @password
END
Once you have the Linked Server in place I have found a few different ways of interacting with the mysql database:
Select Statements:
select * from mysql5...country
select * from OPENQUERY(mysql5, 'select * from country')
Insert Statements:
insert mysql5...country(code,name)
values ('US', 'USA')
insert OPENQUERY(mysql5, 'select code,name from country;')
values ('US', 'USA')
Other Statements:
EXEC('truncate table country') AT mysql5;
Resources:
http://213.136.52.24/bug.php?id=39965
/*
works for me with openquery, but not 4 part naming convention
*/
Lowell
July 19, 2012 at 8:52 am
Thanks Lowell, I'll take a look at this.
Unfortunately, my first pass at trying to execute a query in SSMS against the MySQL linked server (after creating the linked server) resulted in the following error and crashed SQL Server!!
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Why querying a linked server would bring down the SQL Server service is a mystery to me, but this is a big problem....
Rich
July 25, 2012 at 1:58 pm
Thanks again for your help.
I did get this working from my workstation, connected to both SQL Server and MySQL. That at least gets me to the point of being able to manually push updates out to our web server, so I'm most of the way there.
I encountered several problems along the way that some Googling took care of. I decided to continue the thread here http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx, as it contains a good history of the subject, which is where you'll find my issues and fixes.
Rich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply