程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 【實戰】:sqlserver數據實時同步到mysql

【實戰】:sqlserver數據實時同步到mysql

編輯:MySQL綜合教程

【實戰】:sqlserver數據實時同步到mysql


1.安裝安裝mysqlconnector

2.配置mysqlconnector

ODBC數據管理器->系統DSN->添加->mysql ODBC 5.3 ANSI driver->填入data source name如jt,mysql的ip、用戶名、密碼即可

3.新建鏈接服務器

exec sp_addlinkedserver
@server='jt', --ODBC裡面data source name
@srvproduct='mysql', --自己隨便
@provider='MSDASQL', --固定這個
@datasrc=NULL,
@location=NULL,
@provstr='DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=192.168.5.188;DATABASE=suzhou;UID=root;PORT=3306;',
@catalog = NULL

exec sp_addlinkedsrvlogin
@rmtsrvname='jt',
@useself='false',
@rmtuser='root',
@rmtpassword='password';

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

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

---4.sqlserver和mysql新建庫和表

create database suzhou;

create table sz(
id int not null identity(1,1) primary key,
orderno char(20) not null,
ordertime datetime not null default getdate(),
remark varchar(200)
)
go

create table sz(
id int(11) not null ,
orderno char(20) not null,
ordertime datetime(6) not null ,
remark varchar(200),
primary key (id)
) engine=innodb default charset=utf8;

---5.建立回環
--建立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

----6.編寫觸發器和存儲過程

----6.1 insert

--重寫觸發器
use suzhou
go
alter trigger tr_insert_sz on suzhou.dbo.sz
for insert
as
declare @id int, @orderno char(20),@ordertime datetime,@remark varchar(200)
select @id=id,@orderno=orderno,@ordertime=ordertime,@remark =remark from inserted;
begin
print @id
print @orderno
print @ordertime
print @remark
exec loopback.suzhou.dbo.sp_insert @id,@orderno,@ordertime,@remark
end
go

--存儲過程
use suzhou
go
create PROCEDURE sp_insert(
@id int,
@orderno char(20),
@ordertime datetime,
@remark varchar(200)
)
AS
BEGIN
SET NOCOUNT ON;
Insert openquery(jt, 'select * from sz')(id,orderno,ordertime,remark)values(@id,@orderno,@ordertime,@remark)
END
go

----6.2 update

--重寫觸發器
use suzhou
go
create trigger tr_update_sz on suzhou.dbo.sz
for update
as
declare @orderno char(20),@remark varchar(200)
select @orderno=orderno,@remark =remark from inserted;
begin
exec loopback.suzhou.dbo.sp_update @orderno,@remark
end
go

--存儲過程
use suzhou
go
create PROCEDURE sp_update(
@orderno char(20),
@remark varchar(200)
)
AS
BEGIN
SET NOCOUNT ON;
update openquery(jt, 'select * from sz') set remark=@remark where orderno=@orderno
END
go

---update數據測試

use suzhou
go
update sz set remark='ocpyang' where orderno='a001'
go

----6.3 delete

--重寫觸發器
use suzhou
go
create trigger tr_delete_sz on suzhou.dbo.sz
for delete
as
declare @orderno char(20)
select @orderno=orderno from deleted;
begin
exec loopback.suzhou.dbo.sp_delete @orderno
end
go

--存儲過程
use suzhou
go
create PROCEDURE sp_delete(
@orderno char(20)
)
AS
BEGIN
SET NOCOUNT ON;
delete openquery(jt, 'select * from sz') where orderno=@orderno
END
go

---delete數據測試

use suzhou
go
delete from sz where orderno='a001'
go

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved