SqlServer数据实时同步到mysql

SqlServer数据实时同步到mysql

一、首先要装一个ODBC的驱动用来建立mysql的连接。http://www.mysql.com/products/connector/

注意：一定要用32位的可用cmd执行这个命令：

c:\windows\syswow64\odbcad32.exe

配置【用户DSN】和【系统DSN】

二、打开sqlserver建立链接服务器
右键新建链接服务器
1、点击【常规】

填上在ODBC建立的服务器名称，访问接口选择ODBC
2、点击【安全性】

选择最后一个，并填上mysql的账户密码
最后点确定就会看到链接服务器多了一个名为“mysqltest”的链接服务器

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

USE [master]
GO
EXEC master .dbo. sp_serveroption @server =N'MYSQLTEST' , @optname= N'rpc out', @optvalue=N'TRUE'
GO
EXEC master .dbo. sp_serveroption @server =N'MYSQLTEST' , @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

到这就可以测试一下这个能不能链接上

select * from OPENQUERY(MYSQLTEST,'SELECT * FROM users ')

六、建立存储过程和触发器

1、insert

--存储过程
USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[insert_user]
@id INT ,
@name varchar(50)
AS
BEGIN
                 SET NOCOUNT ON
                                insert OPENQUERY(MYSQLTEST,'SELECT * FROM user')(id,name) values(@id,@name);
                 SET NOCOUNT OFF
END

--触发器
USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[insert_trigger] ON [master].[dbo].[user]
FOR INSERT
AS
DECLARE @id INT,
                                 @name VARCHAR(50)
                                 SELECT @id =id, @name=name FROM INSERTED;
                                 BEGIN
                                                 EXEC loopback .master. dbo.insert_user @id, @name;
                                 END

2、update

--存储过程
CREATE PROCEDURE [dbo].[update_user]
@id INT ,
@name varchar(50)
AS
BEGIN
                 SET NOCOUNT ON
                                 UPDATE OPENQUERY (MYSQLTEST,'SELECT * FROM users ') set id =@id where name =@name
                 SET NOCOUNT OFF
END

--触发器
CREATE TRIGGER [dbo].[update_trigger] ON [master].[dbo].[user]
FOR UPDATE
AS
DECLARE @id INT,
                                 @name VARCHAR(50)
         SELECT @id =id, @name=name FROM INSERTED;
BEGIN
                 EXEC loopback .master. dbo.update_user @id, @name;
END

3、delete

--存储过程
CREATE PROCEDURE [dbo].[delete_user]
@id INT 
AS
BEGIN
                 SET NOCOUNT ON
                                 DELETE OPENQUERY ((MYSQLTEST, ,'SELECT * FROM users ') where id =@id
                 SET NOCOUNT OFF
END

--触发器
CREATE TRIGGER [dbo].[delete_trigger] ON [master].[dbo].[user]
FOR DELETE
AS 
                 DECLARE @ID INT
                 SELECT @ID =ID FROM DELETED ;
BEGIN
	 EXEC loopback .master. dbo.delete_user @id;
END

给存储过程添加异常机制

如果遇到不正确的数据导致无法更新同步，则可以做一个异常机制记录。在sqlserver中建一张记录异常的表：LogTable
这里用insert做示范

CREATE PROCEDURE [dbo].[insert_user]
@id INT ,
@name varchar(50)
AS
BEGIN
                 SET NOCOUNT ON
                 BEGIN  try
                                insert OPENQUERY(MYSQLTEST,'SELECT * FROM user')(id,name) values(@id,@name);
                 END try
                 BEGIN catch
INSERT INTO LogTable values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE());
                 END catch
                 SET NOCOUNT OFF
END

这样就可以把一些错误的数据记录在LogTable 表里

小结

//MSDTC不可用解决办法
报错MSDTC(分布式交易协调器)，协调跨多个数据库、消息队列、文件系统等资源管理器的事务。该服务的进程名为Msdtc.exe,该进程调用系统Microsoft Personal Web Server和Microsoft SQL Server。该服务用于管理多个服务器 .
位置：控制面板－－管理工具－－服务－－Distributed Transaction Coordinator
依存关系：Remote Procedure Call(RPC)和Security Accounts Manager
建议：一般家用计算机涉及不到，除非你启用Message Queuing服务，可以停止。
解决办法: 1. 在windows控制面版–>管理工具–>服务–>Distributed Transaction Coordinator–>属性–>启动
2.在CMD下运行"net start msdtc"开启服务后正常。
注：如果在第1步Distributed Transaction Coordinator 无法启动，则是因为丢失了日志文件,重新创建日志文件,再启动就行了。重新创建 MSDTC 日志,并重新启动服务的步骤如下：
(1) 单击"开始",单击"运行",输入 cmd 后按"确定"。
(2) 输入:msdtc -resetlog (注意运行此命令时,不要执行挂起的事务)
(3) 最后输入:net start msdtc 回车,搞定!