sqlserver 触发器 mysql_sqlserver利用触发器实时同步数据到mysql

1、前言

在工作中，想从sqlserver同步一些表的数据到mysql，原有方案是，在原项目中添加多数据源，然后从一个写，一个读，利用定时器定时执行更新。sqlserver的数据表结构建立的不严谨，没有一些创建、更新的时间，所以每次同步都能全部数据删除再写入，数据量多的话就会很慢，所以自己就想用触发器执行，同事说跨服务器跨数据库，你这样做的话，能达到么，自己研究了一下，中途试了很多错，最终还是实现了，现将自己的研究笔记记录一下，怕自己日后也忘记了。

2、背景

可以本地，也可以跨服务器，本文案例，使用的是两台window系统电脑，A电脑有sqlserver，B电脑有mysql，两个电脑均有结构相同的表sud，目的：每次sqlserver的sud表有增删改操作时，同步到mysql的sud表，A电脑sqlserver的sud表在MES_WORKBECH_TD数据库里，B电脑mysql的sud表在jeecgbootsy里。

3、准备工作

1、下载ODBC并安装,因自己下载过一些版本，一直报错分布式相关问题，此处直接放自己的使用版本链接，方便大家下载；

下载链接:https://download.csdn.net/download/qq_40065816/13196371

2、可能在安装ODBC的时候，会提示报错，根据提示，应该要安装visual Studio 2019，所以得继续下载安装,

下载链接：https://download.csdn.net/download/qq_40065816/13196382

这个安装的需要好久，但是 不需要安装完，大约过个几分钟，就可以继续让他安装，自己继续回到ODBC的安装。

3、确保两台电脑的ip能够通信。互相打开cmd,ping对方ip，能ping通，才可以正常操作，不能ping通的话，请关闭防火墙活查看其他原因，只有ping通了对方才可以访问到。

`

4、正式配置并设置同步

(1)打开ODBC，在电脑开始处搜索既可以看到，打开后点击系统DNS，并点击添加

选择驱动

填写完Test，如果成功说明可以连得上，如果不成功，在检查没有填写错误ip的情况下，原因可能是：mysql的数据库不支持A电脑的登录，开个账户给A电脑授权使用即可。

步骤：打开mysql，输入

grant select,update,insert,delete on . to root@ip identified by “123456”;

root：账户名，自己可以随意设置

ip ：A电脑的ip地址

123456为密码，自己设置的在连接的时候注意填写自己的就可以了。

(2)、设置完之后，就可以打开A电脑的sqlserver数据库，此处sqlserver用的是

SQL Server Management Studio 2008软件，

1)新建查询

--新建链接服务器

exec sp_addlinkedserver

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

@srvproduct='MySql' , --自己随便 @provider='MSDASQL' , --固定这个

@datasrc=NULL,

@location=NULL,

@provstr='DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=***ODBC里填的IP***;DATABASE=jeecgbootsy;UID=root;PORT=3306;

--新建查询。输入以下，登录服务器

exec sp_addlinkedsrvlogin

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

@useself='false' ,

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

@rmtpassword='123456';--mysql账号其密码1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

完后可以刷新左侧sqlserver，看看是否连接得上，是否有对应的数据库及表，有则成功

也可以新建查询，用数据库语句测试以下：select * from openquery(AA,'SELECT * FROM sud; ')

能正常查询则连接正常

2)、分别依次运行下列三个语句

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

USE [master]

GO

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

GO

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

GO

--(2)建立LOOPBACK 服务器链接

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

@datasrc = @@SERVERNAME

Go

--(3)设置服务器链接选项，阻止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'

GO1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

3)、开始建立插入的存储过程

use MES_WORKBECH_TD

go

create PROCEDURE sp_insert_sud(

@id int,

@name nvarchar(200)

)

AS

BEGIN

SET NOCOUNT ON;

Insert openquery(AA, 'select * from sud')(id,name)values(@id,@name)

END

Go1

2

3

4

5

6

7

8

9

10

11

12

建立触发器

use MES_WORKBECH_TD

go

create trigger tr_insert_sud on dbo.sud

for insert

as

declare @id int, @name nvarchar(200)

select @id=id,@name=name from inserted;

begin

print @id

print @name

exec loopback.MES_WORKBECH_TD.dbo.sp_insert_sud @id,@name

end

go1

2

3

4

5

6

7

8

9

10

11

12

13

在建立过程中，可能会出现该列找不到的报错，ctrl+Shift+R，报错就消失了，sqlserver插入一条试试：

insert into MES_WORKBECH_TD.dbo.sud values (5,'apple');1

mysql也真的新增了一条；

4)、建立修改的存储过程与触发器

--update

use MES_WORKBECH_TD

go

CREATE PROCEDURE sp_update_sud

@id INT ,

@name nvarchar(200)

AS

BEGIN SET NOCOUNT ON UPDATE OPENQUERY (AA, 'select * from sud') set name =@name where id =@id SET NOCOUNT OFF

END use MES_WORKBECH_TD

go

CREATE TRIGGER tr_update_sud ON dbo.sud

FOR UPDATE

AS

DECLARE @id INT, @name nvarchar(200)

SELECT @id =id, @name=name FROM INSERTED;

BEGIN EXEC loopback .MES_WORKBECH_TD. dbo.sp_update_sud @id, @name;

END1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

修改试试：update MES_WORKBECH_TD.dbo.sud set name = 'banana' where id=5

真的修改到了

5)删除存储过程与触发器的建立：

--delete

USE [MES_WORKBECH_TD]

GO

CREATE PROCEDURE sp_delete_sud

@ID INT

AS

BEGIN SET NOCOUNT ON DELETE OPENQUERY (AA, 'select * from sud') where id =@id SET NOCOUNT OFF

END

USE [MES_WORKBECH_TD]

GO

CREATE TRIGGER rt_delete_sud ON dbo.sud

FOR DELETE

AS

DECLARE @ID INT

SELECT @ID =ID FROM DELETED ;

BEGIN

EXEC loopback .MES_WORKBECH_TD. dbo.sp_delete_sud @id;

END1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

删除试试：delete from MES_WORKBECH_TD.dbo.sud where id=5

真的没了

5、总结

以上就是自己测试了用触发器同步的步骤，看着简单，但是在做的时候，遇到了一个提示：不支持分布式，困扰了自己很久，打算放弃了，后来抱着试试看的心态，真的成功了：

类似与图片上的报错，结果，原因是：我用的ODBC是5.1的版本，这个版本，真的很崩溃，这个版本可以链接到mysql，也可以查询，但是在触发器中做增删改，就会报错无法启动分布式，网上查了很多办法都试不成功，最后换了8.0的就成功了，在做的过程中，如大家遇到问题，欢迎一起留言讨论：

文章来源: blog.csdn.net，作者：JO安，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/qq_40065816/article/details/110230235