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\’)
版权声明：本文为zhaowenzhong原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。