資料庫同步方案

kuailewangzi1212發表於2015-01-18

1--所有的表新增'datatsp和datatsp_int'

--select * from sysobjects where xtype='U' order by name

--資料庫中所有的只具有一個主鍵表新增'datatsp和datatsp_int'
declare @table_name varchar(50),
		@sql varchar(8000),
		@col_key varchar(50)
		
declare cur_tb cursor for
	select distinct tablename from v_table_des 
	where iskey='1' and tablename not like '%bak'
	group by tablename
	having COUNT(*)=1
open cur_tb
fetch cur_tb into @table_name
while @@FETCH_STATUS=0
begin 
	--select * from v_table_des
	select @col_key= colname from v_table_des where iskey='1' and tablename=@table_name

	select @sql='alter table '+@table_name+' add datatsp timestamp '
			+'alter table '+@table_name+' add datatsp_int bigint '
	print @sql
			
	fetch cur_tb into @table_name
end
close cur_tb
deallocate cur_tb

　　2、將主資料庫分離，將mdf檔案移到備份伺服器上附加資料庫

3、--將資料版本修改為一致--ok

--主資料庫
--4--將主資料庫 資料版本修改為一致
declare @table_name varchar(50),
		@sql varchar(8000),
		@col_key varchar(50)
		
declare cur_tb cursor for
	select distinct tablename from v_table_des 
	where iskey='1' and tablename not like '%bak'
	group by tablename
	having COUNT(*)=1
open cur_tb
fetch cur_tb into @table_name
while @@FETCH_STATUS=0
begin 
	--select * from v_table_des
	select @col_key= colname from v_table_des where iskey='1' and tablename=@table_name

	select @sql='update '+@table_name+' set datatsp_int = convert(bigint,b.datatsp) from [192.168.1.163].ylbx.dbo.'+@table_name+' b where '+@table_name+'.'+@col_key+'=b.'+@col_key+'
	print '''+@table_name+'''
	 go '		   
	print @sql
			
	fetch cur_tb into @table_name
end
close cur_tb
deallocate cur_tb


--分資料庫
declare @i int,
		@start bigint,
		@end bigint,
		@sql varchar(8000),
		@dbname varchar(50)--資料庫名稱
		
		
select @i=0,@start=1,@end=50000

while @i<=19
begin
	select @start=1 + @i*50000,
			@end=50000+ @i*50000
	select @dbname='ylbx_'+convert(varchar(8),@start)+'_'+convert(varchar(8),@end)
	select @sql='
	update '+@dbname+'.dbo.T_DataAttachment set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].'+@dbname+'.dbo.T_DataAttachment b where '+@dbname+'.dbo.T_DataAttachment.PersonID=b.PersonID and '+@dbname+'.dbo.T_DataAttachment.Reason=b.Reason and '+@dbname+'.dbo.T_DataAttachment.Category=b.Category
	print '''+@dbname+'.dbo.T_DataAttachment''
	go
	update '+@dbname+'.dbo.T_DataCertificate set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].'+@dbname+'.dbo.T_DataCertificate b where '+@dbname+'.dbo.T_DataCertificate.PersonID=b.PersonID 
	print '''+@dbname+'.dbo.T_DataCertificate''
	go
	update '+@dbname+'.dbo.T_DataFingerprint set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].'+@dbname+'.dbo.T_DataFingerprint b where '+@dbname+'.dbo.T_DataFingerprint.PersonID=b.PersonID and '+@dbname+'.dbo.T_DataFingerprint.Finger=b.Finger
	print '''+@dbname+'.dbo.T_DataFingerprint''
	go
	update '+@dbname+'.dbo.T_DataPhoto set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].'+@dbname+'.dbo.T_DataPhoto b where '+@dbname+'.dbo.T_DataPhoto.PersonID=b.PersonID 
	print '''+@dbname+'.dbo.T_DataPhoto''
	go'
	
		
	print @sql
	select @i=@i + 1
end

　　4、資料庫同步過程

declare @table_name varchar(50),
		@sql varchar(8000),
		@col_key varchar(50),
		@col_name varchar(50),
		@sql_upd varchar(8000),
		@sql_sel varchar(8000)
		
declare cur_tb cursor for
	select distinct tablename from v_table_des 
	where iskey='1' and tablename not like '%bak' --and tablename='t_person'
	group by tablename
	having COUNT(*)=1
open cur_tb
fetch cur_tb into @table_name
while @@FETCH_STATUS=0
begin 
	--select * from v_table_des
	select @col_key= colname from v_table_des where iskey='1' and tablename=@table_name
	
	select @sql_upd=''
	select @sql_sel=''
	declare cur_ist cursor for--select * from v_table_des where tablename='t_person' and colname<>'datatsp_int'
		select colname from v_table_des where tablename=@table_name and colname not in('datatsp_int','datatsp')
	open cur_ist
	fetch cur_ist into @col_name
	while @@FETCH_STATUS=0
	begin	
		if @col_name <>@col_key
		begin	
			select @sql_upd=@sql_upd+@table_name+'.'+@col_name+'=bb.'+@col_name+','		
		end
		select @sql_sel=@sql_sel+@col_name+','		
		
		fetch cur_ist into @col_name	 
	end
	close cur_ist
	deallocate cur_ist
			
	
	select @sql='
	--declare @max_id bigint--訂閱資料庫最大版本id	
	--declare @max_id1 bigint--釋出資料庫最大版本id	
	--同步主資料庫--下列順序不能打亂--'+@table_name+'
	select  @max_id=0,@max_id1=0
	select @max_id=max(datatsp_int) from '+@table_name+'	 
	select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].ylbx.dbo.'+@table_name+' with(nolock) 	 
	--1、刪除不存在
	delete from '+@table_name+' where '+@col_key+' not in(select '+@col_key+' from [192.168.1.163].ylbx.dbo.'+@table_name+' with(nolock) )	
	--2、修改
	update '+@table_name+' 
	set '+@sql_upd+@table_name+'.datatsp_int=convert(bigint,bb.datatsp)
	from 
	(
		select a.* from [192.168.1.163].ylbx.dbo.'+@table_name+' a with(nolock) where CONVERT(bigint,a.datatsp)>@max_id and CONVERT(bigint,a.datatsp)<=@max_id1
	)bb
	where '+@table_name+'.'+@col_key+'=bb.'+@col_key+'
	--3、新增
	set identity_insert '+@table_name+' on
	insert into '+@table_name+'('+@sql_sel+'datatsp_int)
	select '+@sql_sel+'convert(bigint,datatsp) as datatsp_int 
	from [192.168.1.163].ylbx.dbo.'+@table_name+' with(nolock) 
	where CONVERT(bigint,datatsp)>@max_id and CONVERT(bigint,datatsp)<=@max_id1 and '+@col_key+' not in(select '+@col_key+' from '+@table_name+')
	set identity_insert '+@table_name+' off
	'
	
	print @sql			
	fetch cur_tb into @table_name
end
close cur_tb
deallocate cur_tb



-------------------------------------------------------------------------------------------------------------------------------------------------------------

　　5、如果資料庫比較大，採用分片同步

--執行的結果升級釋出資料庫和訂閱資料庫
declare @i int,
		@start bigint,
		@end bigint,
		@sql varchar(max),
		@dbname varchar(50)--資料庫名稱
		
		
select @i=0,@start=1,@end=50000

while @i<=19
begin
	select @start=1 + @i*50000,
			@end=50000+ @i*50000
	--select @sql='union all SELECT PersonID,Data,OperatorID,OperatorTime,Data_All from ylbx_'+convert(varchar(8),@start)+'_'+convert(varchar(8),@end)+'..t_dataphoto'
	--select @sql='union all SELECT PersonID,Data,Img,Finger,OperatorID,OperatorTime from ylbx_'+convert(varchar(8),@start)+'_'+convert(varchar(8),@end)+'..T_DataFingerprint'
	--select @sql='union all SELECT * from ylbx_'+convert(varchar(8),@start)+'_'+convert(varchar(8),@end)+'..T_DataCertificate'
	select @dbname='ylbx_'+convert(varchar(8),@start)+'_'+convert(varchar(8),@end)
	select @sql='use '+@dbname+'
	go
	alter procedure sys_sync_database
	as
	begin
		declare @max_id bigint--訂閱資料庫最大版本id	
	    declare @max_id1 bigint--釋出資料庫最大版本id		    
		--T_DataPhoto
		select  @max_id=0,@max_id1=0
		select @max_id=max(datatsp_int) from T_DataPhoto
		select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataPhoto with(nolock) where  CONVERT(bigint,datatsp)>@max_id
		select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataPhoto with(nolock)	 
		if @max_id1-@max_id>=500
		begin
			select @max_id1=@max_id +500
		end
		--1、刪除不存在
		delete from T_DataPhoto where PersonID not in(select PersonID from [192.168.1.163].'+@dbname+'.dbo.T_DataPhoto with(nolock))	
		--2、修改
		update T_DataPhoto 
		set T_DataPhoto.Data=bb.Data,T_DataPhoto.OperatorID=bb.OperatorID,T_DataPhoto.OperatorTime=bb.OperatorTime,T_DataPhoto.Data_All=bb.Data_All,T_DataPhoto.datatsp_int=convert(bigint,bb.datatsp)
		from 
		(
			select a.* from [192.168.1.163].'+@dbname+'.dbo.T_DataPhoto a with(nolock)
			where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1
		)bb
		where T_DataPhoto.PersonID=bb.PersonID
		--3、新增
		--set identity_insert T_DataPhoto on
		insert into T_DataPhoto(PersonID,Data,OperatorID,OperatorTime,Data_All,datatsp_int)
		select PersonID,Data,OperatorID,OperatorTime,Data_All,convert(bigint,datatsp) as datatsp_int 
		from [192.168.1.163].'+@dbname+'.dbo.T_DataPhoto with(nolock) 
		where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and PersonID not in(select PersonID from T_DataPhoto)
		--set identity_insert T_DataPhoto off
		---------------------------------------------------------------------------------------						
		--T_DataCertificate
		select  @max_id=0,@max_id1=0
		select @max_id=max(datatsp_int) from T_DataCertificate
		select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataCertificate with(nolock) where  CONVERT(bigint,datatsp)>@max_id
		select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataCertificate with(nolock)	 
		if @max_id1-@max_id>=500
		begin
			select @max_id1=@max_id +500
		end
		--1、刪除不存在
		delete from T_DataCertificate where PersonID not in(select PersonID from [192.168.1.163].'+@dbname+'.dbo.T_DataCertificate with(nolock))	
		--2、修改
		update T_DataCertificate 
		set T_DataCertificate.Data=bb.Data,T_DataCertificate.OperatorID=bb.OperatorID,T_DataCertificate.OperatorTime=bb.OperatorTime,T_DataCertificate.memo=bb.memo,T_DataCertificate.datatsp_int=convert(bigint,bb.datatsp)
		from 
		(
			select a.* from [192.168.1.163].'+@dbname+'.dbo.T_DataCertificate a with(nolock)
			where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1
		)bb
		where T_DataCertificate.PersonID=bb.PersonID
		--3、新增
		--set identity_insert T_DataCertificate on
		insert into T_DataCertificate(PersonID,Data,OperatorID,OperatorTime,memo,datatsp_int)
		select PersonID,Data,OperatorID,OperatorTime,memo,convert(bigint,datatsp) as datatsp_int 
		from [192.168.1.163].'+@dbname+'.dbo.T_DataCertificate with(nolock) 
		where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and PersonID not in(select PersonID from T_DataCertificate)
		--set identity_insert T_DataCertificate off
		---------------------------------------------------------------------------------------
					
		--T_DataFingerprint
		select  @max_id=0,@max_id1=0
		select @max_id=max(datatsp_int) from T_DataFingerprint	 
		select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataFingerprint with(nolock) where  CONVERT(bigint,datatsp)>@max_id
		select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataFingerprint with(nolock)
		if @max_id1-@max_id>=2000
		begin
			select @max_id1=@max_id +2000
		end	 
		--1、刪除不存在
		delete from T_DataFingerprint where convert(varchar(20),PersonID)+''-''+convert(varchar(20),Finger) not in(select convert(varchar(20),PersonID)+''-''+convert(varchar(20),Finger) from [192.168.1.163].'+@dbname+'.dbo.T_DataFingerprint with(nolock))	
		--2、修改
		update T_DataFingerprint 
		set T_DataFingerprint.Data=bb.Data,T_DataFingerprint.Img=bb.Img,T_DataFingerprint.OperatorID=bb.OperatorID,T_DataFingerprint.OperatorTime=bb.OperatorTime,T_DataFingerprint.datatsp_int=convert(bigint,bb.datatsp)
		from 
		(
			select a.* from [192.168.1.163].'+@dbname+'.dbo.T_DataFingerprint a with(nolock)
			where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1
		)bb
		where convert(varchar(20),T_DataFingerprint.PersonID)+''-''+convert(varchar(20),T_DataFingerprint.Finger)=convert(varchar(20),bb.PersonID)+''-''+convert(varchar(20),bb.Finger)
		--3、新增
		--set identity_insert T_DataFingerprint on
		insert into T_DataFingerprint(PersonID,Data,Img,Finger,OperatorID,OperatorTime,datatsp_int)
		select PersonID,Data,Img,Finger,OperatorID,OperatorTime,convert(bigint,datatsp) as datatsp_int 
		from [192.168.1.163].'+@dbname+'.dbo.T_DataFingerprint with(nolock) 
		where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and convert(varchar(20),PersonID)+''-''+convert(varchar(20),Finger) not in(select convert(varchar(20),PersonID)+''-''+convert(varchar(20),Finger) from T_DataFingerprint)
		--set identity_insert T_DataFingerprint off
		
		---------------------------------------------------------------------------------------
					
		--T_DataAttachment
		select  @max_id=0,@max_id1=0
		select @max_id=max(datatsp_int) from T_DataAttachment	 
		select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataAttachment with(nolock) where  CONVERT(bigint,datatsp)>@max_id
		select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].'+@dbname+'.dbo.T_DataAttachment with(nolock)	 
		if @max_id1-@max_id>=500
		begin
			select @max_id1=@max_id +500
		end
		--1、刪除不存在
		delete from T_DataAttachment where convert(varchar(20),PersonID)+''-''+convert(varchar(50),Reason)+''-''+convert(varchar(50),Category) not in(select convert(varchar(20),PersonID)+''-''+convert(varchar(50),Reason)+''-''+convert(varchar(50),Category) from [192.168.1.163].'+@dbname+'.dbo.T_DataAttachment with(nolock))	
		--2、修改
		update T_DataAttachment 
		set T_DataAttachment.Data=bb.Data,T_DataAttachment.OperatorID=bb.OperatorID,T_DataAttachment.OperatorTime=bb.OperatorTime,T_DataAttachment.memo=bb.memo,T_DataAttachment.datatsp_int=convert(bigint,bb.datatsp)
		from 
		(
			select a.* from [192.168.1.163].'+@dbname+'.dbo.T_DataAttachment a with(nolock)
			where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1
		)bb
		where convert(varchar(20),T_DataAttachment.PersonID)+''-''+convert(varchar(50),T_DataAttachment.Reason)+''-''+convert(varchar(50),T_DataAttachment.Category)=convert(varchar(20),bb.PersonID)+''-''+convert(varchar(50),bb.Reason)+''-''+convert(varchar(50),bb.Category)
		--3、新增
		--set identity_insert T_DataAttachment on
		insert into T_DataAttachment(PersonID,Reason,Category,Data,OperatorID,OperatorTime,memo,datatsp_int)
		select PersonID,Reason,Category,Data,OperatorID,OperatorTime,memo,convert(bigint,datatsp) as datatsp_int 
		from [192.168.1.163].'+@dbname+'.dbo.T_DataAttachment with(nolock) 
		where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and convert(varchar(20),PersonID)+''-''+convert(varchar(50),Reason)+''-''+convert(varchar(50),Category) not in(select convert(varchar(20),PersonID)+''-''+convert(varchar(50),Reason)+''-''+convert(varchar(50),Category) from T_DataAttachment)
		--set identity_insert T_DataAttachment off
	end
	go	'
	print @sql
	select @i=@i + 1
end

完善昨天寫的資料庫結構同步方案
2019-03-07
資料庫
資料庫同步
2022-02-28
資料庫
Linux 資料同步方案
2021-07-17
Linux
使用者級資料庫結構同步方案（測試版）
2019-07-20
資料庫
使用 RMAN 同步資料庫
2012-07-02
資料庫
資料庫同步問題
2005-06-20
資料庫
用impdp同步資料庫
2007-11-21
資料庫
資料庫同步利器 otter 雙A同步配置
2020-03-15
資料庫
DataX將MySql資料庫資料同步到Oracle資料庫
2024-05-16
MySql資料庫Oracle
斷網收銀資料同步方案
2019-01-08
[資料整合/資料同步] 基於資料庫增量日誌的資料同步方案 : Flink CDC/Debezium/DataX/Canal/Oracle Goldengate/Kettle/Sqoop
2024-10-16
資料庫OracleGoOOP
DataX將Oracle資料庫資料同步到達夢資料庫
2024-05-17
Oracle資料庫
[資料庫]MYSQL主從同步
2019-02-20
資料庫MySql主從同步
資料庫非同步操作
2019-01-08
資料庫非同步
同步寫兩個資料庫
2016-06-12
資料庫
資料庫同步更新問題？
2007-04-12
資料庫
SQL Server 資料庫同步配置
2009-11-26
SQLServer資料庫
Redux 非同步資料流方案對比
2017-10-18
Redux非同步
全域性資料同步（三）終極方案
2017-07-09
關於異地資料同步的方案
2008-02-13
高併發架構系列：資料庫主從同步的3種方案
2023-01-09
架構資料庫主從同步
資料庫恢復方案
2016-05-14
資料庫
資料庫遷移方案
2010-11-03
資料庫
資料庫備份方案
2009-09-18
資料庫
不同Oracle資料庫之間的資料同步
2017-04-19
Oracle資料庫
資料庫同步你們用啥
2022-07-27
資料庫
oracle資料庫配置goldengate同步
2012-12-29
Oracle資料庫Go
Oracle資料庫同步技術1
2009-11-17
Oracle資料庫
Oracle資料庫同步技術2
2009-11-17
Oracle資料庫
Oracle資料庫同步技術3
2009-11-17
Oracle資料庫
Oracle資料庫同步技術4
2009-11-17
Oracle資料庫
Oracle資料庫同步技術5
2009-11-17
Oracle資料庫
兩臺SQL Server資料同步解決方案
2014-10-25
SQLServer
Mysql 資料庫主庫，備庫實時同步配置
2013-12-20
MySql資料庫
MySQL資料庫之分庫分表方案
2019-07-24
MySql資料庫
MySQL資料庫高可用方案
2020-08-05
MySql資料庫
海量資料庫解決方案
2010-11-04
資料庫
資料庫空間重整方案
2010-09-16
資料庫

資料庫同步方案

相關文章