sqlServer数据实时同步到mysql

忘是亡心i 2022-09-01 14:47 443阅读 0赞

1.---安装mysqlconnector

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

2.---配置mysqlconnector

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70 2][]

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里的数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70 3][]

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

select \* from openquery(jt,'SELECT \* FROM t\_attendance ; ')

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

USE \[master\]

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

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

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

EXEC sp\_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI',

@datasrc = @@SERVERNAME

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

USE \[master\]

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

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

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

完工！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70]: /images/20220829/17074f2b3cbe4060acd791a2840699ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70 1]: /images/20220829/72bb8dd774f74773813d2c15e5f5a2d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70 2]: /images/20220829/50b773fb6e254f308a0e934e4cfc756e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lfd194X2s_size_16_color_FFFFFF_t_70 3]: /images/20220829/53f6c7e6b8904537b0f8621c806eba45.png