【實戰】:sqlserver 資料即時同步到mysql，sqlservermysql

【實戰】:sqlserver 資料即時同步到mysql，sqlservermysql

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

 

 

怎將SQL 2005中的資料即時同步到MYSQL中

mysql和sqlserver存在文法差異，直接導sql是不行的，不過可以通過sqlserver的bcp命令把 表裡的資料導成指定格式的txt檔案，比如列之間用逗號分割，行之間用\n\r分割
這樣的資料，mysql 就可以通過 load data infile命令來匯入到庫裡

因為sqlserver 不熟悉，所以bcp命令需要你自己研究下，
mysql的 load data文法你可以參考：
load data infile '/tmp/result.text' into table test_table fields terminated by ',' lines terminated by '\n' ;
意思是欄位之間用逗號分割，行之間用\n分割，資料檔案是result.text
 
資料庫mysql裡的資料 與sqlserver裡的資料同步

我將兩個思路提供給你：
1、首先，你要有一個業務層和一個資料提供者層和資料層（後兩層可以合并），業務層中有資料操作時同時操作其它兩層的資料，只有同時成功了才提交事務。這樣可以確保資料一致。
2、設計和實現一個資料同步工具，我實現了一個SQL Server和Oracle之間資料同步的工具，利用一個定義表（主鍵，欄位，表名，同步標識等）和DataSet做中轉，能夠將兩個資料庫中需要同步的表同步成相同的內容，你可以根據要求指定同步周期。