sqlServer数据实时同步到mysql

1.---安装mysqlconnector

http://www.mysql.com/products/connector/

2.---配置mysqlconnector

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

3.--新建链接服务器

sp_addlinkedserver

@server='jt',--Connector ODBC里面填的data source name

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

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

@datasrc='jt', ----ODBC里面data source name

@location=NULL,

@catalog=NULL

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

sp_addlinkedsrvlogin

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

@useself='false',

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

@rmtpassword='root';--mysql账号密码
 

刷新对象资源管理器中的链接服务器，能看到的Connector ODBC里面填的data source name内容，目录下对应的是mysql里的数据库

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

select * from openquery(jt,'SELECT * FROM t_attendance ; ')

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

USE [master]

GO

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

GO

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

GO

7.--建立LOOPBACK 服务器链接

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

@datasrc = @@SERVERNAME

go

8.--设置服务器链接选项，阻止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

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

--insert

--新建触发器(如果已经创建的则修改触发器，将create换成alter，下面同理)

use zjdhd_ice
go
create  TRIGGER TR_INSERT_S_KaoQin ON zjdhd_ice.dbo.S_KaoQin
FOR INSERT
AS
DECLARE @KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
SELECT @KaoQin_Code =KaoQin_Code, @KaoQin_Latitude=KaoQin_Latitude FROM INSERTED;
BEGIN
EXEC loopback.zjdhd_ice.dbo.pro_insert_kaoqin  @KaoQin_Code, @KaoQin_Latitude;
END
go

--创建存储过程（如果已经创建的则修改存储过程，将create换成alter，下面同理）

use zjdhd_ice
go
create PROCEDURE pro_insert_kaoqin 
@KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
AS
BEGIN
SET NOCOUNT ON
INSERT OPENQUERY (jt, 'select * from t_attendance')(attendance_dnum,lat)values(@KaoQin_Code ,@KaoQin_Latitude)
END
go

--update

--新建触发器

use zjdhd_ice
go
create TRIGGER TR_update_S_KaoQin ON zjdhd_ice.dbo.S_KaoQin
FOR update
AS
DECLARE @KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
SELECT @KaoQin_Code =KaoQin_Code, @KaoQin_Latitude=KaoQin_Latitude FROM INSERTED;
BEGIN
EXEC loopback.zjdhd_ice. dbo.pro_update_kaoqin @KaoQin_Code, @KaoQin_Latitude;
END
go

--新建存储过程

use zjdhd_ice
go
create PROCEDURE pro_update_kaoqin
@KaoQin_Code varchar(50) ,
@KaoQin_Latitude varchar(50)
AS
BEGIN
SET NOCOUNT ON
UPDATE OPENQUERY (jt, 'select * from t_attendance') SET lat=@KaoQin_Latitude where attendance_dnum=@KaoQin_Code
SET NOCOUNT OFF
END
go

--delete

新建触发器

use zjdhd_ice
go
create trigger tr_delete_S_KaoQin on zjdhd_ice.dbo.S_KaoQin
for delete
as
declare  @KaoQin_Code varchar(50),
@KaoQin_Latitude varchar(50)
select @KaoQin_Code =KaoQin_Code, @KaoQin_Latitude=KaoQin_Latitude from deleted;
begin
exec loopback.zjdhd_ice.dbo.sp_delete_kaoqin @KaoQin_Code
end
go

新建存储过程

use zjdhd_ice
go
create PROCEDURE sp_delete_kaoqin(
@KaoQin_Code varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
delete openquery(jt, 'select * from t_attendance')  where attendance_dnum=@KaoQin_Code
END
go

完工！