SqlServer实时数据同步到mysql

因为业务需要要要实现 sqlserver和 mysql 异构数据库 数据同步；起先一点思路都没有，于是到网上一顿好找；

最后找到了2种解决方案：

1、利用第三方工具实现；

2、利用ODBC实现；

第一种测试结果不理想，弊端是：1、不稳定 2、出了问题不易定位3、不够智能

最后采用了第二种ODBC来实现，运行了快1年没有出过问题；今天共享出来以帮助其他朋友；

---安装安装mysqlconnector
http://www.mysql.com/products/connector/
/*
配置mysqlconnector

ODBC数据管理器->系统DSN->添加->mysql ODBC 5.3 ANSI driver->填入data source name如jt,mysql的ip、用户名、密码即可

--新建链接服务器

exec sp_addlinkedserver

@server = 'MySqll_Aggregation' , --ODBC里面data source name

@srvproduct = 'MySql' , --自己随便

@provider = 'MSDASQL' , --固定这个

@datasrc =NULL,

@location =NULL,

@provstr = 'DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=172.17.22.3;DATABASE=bi;UID=zhaowenzhong;PORT=3306;' ,

@catalog = NULL

---创建连接mysql数据库的账号及密码

exec sp_addlinkedsrvlogin

@rmtsrvname = ' MySqll_Aggregation ' , ---- ODBC里面data source name

@useself = 'false' ,

@rmtuser = 'zhaowenzhong' , ---mysql账号

@rmtpassword = 'mysqldba@2015' ; --mysql账号其密码

 

---测试是否可以访问mysql数据库中的表

select * from openquery ( MySqll_Aggregation , 'SELECT * FROM tb; ' )

-----建立允许远程访问连接操作

USE [master]

GO

EXEC master . dbo . sp_serveroption @server = N'MySqll_Aggregation' , @optname = N'rpc out' , @optvalue = N'TRUE'

GO

EXEC master . dbo . sp_serveroption @server = N'MySqll_Aggregation' , @optname = N'remote proc transaction promotion' , @optvalue = N'false'

GO

  

--建立LOOPBACK 服务器链接

EXEC sp_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI' ,

@datasrc = @@SERVERNAME

go

--设置服务器链接选项，阻止SQL Server 由于远过程调用而将本地事务提升为分布事务（重点）

USE [master]

GO

EXEC master . dbo . sp_serveroption @server = N'loopback' , @optname = N'rpc out' , @optvalue = N'TRUE'

GO

EXEC master . dbo . sp_serveroption @server = N'loopback' , @optname = N'remote proc transaction promotion' , @optvalue = N'false'

GO

---编写触发器和存储过程

--insert

CREATE TRIGGER TR_INSERT_TB ON DB_TY2015 . DBO . TB

FOR INSERT

AS

DECLARE @ID INT ,

                                 @QTY INT

                                 SELECT @ID = ID , @QTY = QTY FROM INSERTED ;

                                 BEGIN

                                                 EXEC loopback . db_ty2015 . dbo . sp_insert @id , @qty ;

                                 END

CREATE PROCEDURE SP_INSERT

@ID INT ,

@QTY INT

AS

BEGIN

                 SET NOCOUNT ON

                                 INSERT OPENQUERY ( db_ty2015 , 'select * from tb' )( id , qty ) values ( @id , @qty );

                 SET NOCOUNT OFF

END

 

---update

CREATE TRIGGER TR_UPDATE_TB ON DB_TY2015 . DBO . TB

FOR UPDATE

AS

DECLARE @ID INT , @QTY INT

SELECT @ID = ID , @QTY = QTY FROM INSERTED ;

BEGIN

                 EXEC loopback . db_ty2015 . dbo . sp_update @id , @qty ;

END

 

CREATE PROCEDURE SP_UPDATE

@ID INT ,

@QTY INT

AS

BEGIN

                 SET NOCOUNT ON

                                 UPDATE OPENQUERY ( db_ty2015 , 'select * from tb' ) set qty = @qty where id = @id

                 SET NOCOUNT OFF

END

 

--delete

CREATE TRIGGER TR_DELETE_TB ON DB_TY2015 . DBO . TB

FOR DELETE

AS

 

                 DECLARE @ID INT

                 SELECT @ID = ID FROM DELETED ;

BEGIN

                 EXEC loopback . db_ty2015 . dbo . sp_DELETE @id ;

END

 

CREATE PROCEDURE SP_DELETE

@ID INT

AS

BEGIN

                 SET NOCOUNT ON

                                 DELETE OPENQUERY ( db_ty2015 , 'select * from tb' ) where id = @id

                 SET NOCOUNT OFF

END

 

-------初始化数据      表已存在的情况

insert openquery ( MySqll_Aggregation , 'select * from bi.tb' )   select * from [FSLogin] . [dbo] . [tb] with ( nolock )

 

---从mysql同步表结构及数据到sqlserver上(与本案例无关)

select * into [SqlServerDBName] . dbo . tb from openquery ( localmysql , 'select * from mysqldbname.weibosession' )

 

 

转载于:https://www.cnblogs.com/zhaowenzhong/p/5165522.html