【SQL SERVER】SQL SERVER数据库实时同步到MySQL

SQL Server 数据实时同步到MySQL中(先做记录,待会儿再详细规划)

EXEC sp_addlinkedserver
@server='YCGA_MySQL', --ODBC里面data source name 上图记住的名字
@srvproduct='MySQL', --自己随便
@provider='MSDASQL', --固定这个
@datasrc=NULL,
@location=NULL,
@provstr='DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=IP地址;DATABASE=数据库名;UID=richard;PORT=3306;',   
--这里面的参数,分别是驱动名称,比如我的是MySQL ODBC 8.0 一般只改下版本号就可以,后面是数据库地址,数据库名,UID就是唯一标识这个自己起就行,最后的是数据库端口号
@catalog = NULL

exec sp_addlinkedsrvlogin
@rmtsrvname='YCGA_MySQL' ,----ODBC里面data source name 跟上面的一样
@useself='false' ,
@rmtuser='账号' ,---mysql账号
@rmtpassword='密码' ;--mysql账号其密码

select * from openquery(YCGA_MySQL,'SELECT * FROM xinhu_A04; ')

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

--select * from openquery(YCGA_MySQL,'SELECT * FROM xinhu_A04; ')

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


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

USE [YC_HR]
GO
EXEC YC_HR.dbo.sp_serveroption @server=N'loopback',@optname=N'rpc out',@optvalue=N'TRUE'
GO
EXEC YC_HR.dbo.sp_serveroption @server=N'loopback',@optname=N'remote proc transaction promotion',@optvalue=N'false'
GO


CREATE PROCEDURE A34_INSERT
@PersonID VARCHAR,
@DispOrder INT,
@A3405 VARCHAR,
@A3410 VARCHAR,
@E3401 VARCHAR
AS
BEGIN
SET NOCOUNT ON
            INSERT OPENQUERY(YCGA_MySQL, 'select * from xinhu_A34')(PersonID,DispOrder,A3405,A3410,E3401) values (@PersonID,@DispOrder,@A3405,@A3410,@E3401);
SET NOCOUNT OFF
END


CREATE TRIGGER SRV_A34_INSERT ON  loopback.yc_hr.dbo.A34
FOR INSERT
AS
DECLARE 
@PersonID VARCHAR,
@DispOrder INT,
@A3405 VARCHAR,
@A3410 VARCHAR,
@E3401 VARCHAR
SELECT @PersonID=PersonID,@DispOrder=DispOrder,@A3405=A3405,@A3410=A3410,@E3401=E3401 FROM INSERTED;
BEGIN
EXEC loopback.yc_hr.dbo.A34_insert @PersonID,@DispOrder,@A3405,@A3410,@E3401;
END

SELECT * from loopback.yc_hr.dbo.A34;


CREATE TRIGGER SRV_A34_INSERT ON  YC_HR.dbo.A34
FOR INSERT
AS
DECLARE 
@PersonID VARCHAR,
@DispOrder INT,
@A3405 VARCHAR,
@A3410 VARCHAR,
@E3401 VARCHAR
SELECT @PersonID=PersonID,@DispOrder=DispOrder,@A3405=A3405,@A3410=A3410,@E3401=E3401 FROM INSERTED;
BEGIN
EXEC loopback.yc_hr.dbo.A34_insert @PersonID,@DispOrder,@A3405,@A3410,@E3401;
END


CREATE PROCEDURE A17_INSERT
@PersonID VARCHAR(10),
@DispOrder INT,
@E1701 VARCHAR(1),
@E1703 VARCHAR(20),
@E1705 datetime,
@E1709 datetime
AS
BEGIN
SET NOCOUNT ON
            INSERT OPENQUERY(YCGA_MySQL, 'select * from xinhu_A17')(PersonID,DispOrder,E1701,E1703,E1705,E1709) values (@PersonID,@DispOrder,@E1701,@E1703,@E1705,@E1709);
SET NOCOUNT OFF
END


--声明数据库引用
use YC_HR;
go

--判断是否存在,如果存在则删除
if exists(select * from sys.triggers where name='SRV_A17_INSERT')

--删除DML触发器
drop trigger SRV_A17_INSERT;

----删除DDL触发器
drop trigger SRV_A17_INSERT on database;

--删除登录触发器
drop trigger SRV_A17_INSERT on all server;
go

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值