mysql 变更数据捕获_SQL Server 变更数据捕获(CDC)监控表数据

一.本文所涉及的内容(Contents)

二.背景(Contexts)

在SQL Server 2008版本之前,对表数据库的变更监控,我们通常使用DML触发器进行监控,把DML操作中的INSERT/UPDATE/DELETE数据记录下来,但是触发器的维护比较困难;

当SQL Server 2008新功能:变更数据捕获(Change Data Capture,即CDC)出来之后,我发现这正是我想要的,因为我之前使用DML触发器实现的时候也是把UPDATE操作按照两条记录进行记录的,共同的缺点都是在用户修改了表结构后,CDC不会自动同步到记录中,不过CDC也有DDL的监控可以补充这个缺陷;CDC的优点就是以异步进程读取事务日志进行捕获数据变更的。

三.实现过程(Realization)

(一) 创建一个测试数据库;

/******* Step1:创建示例数据库*******/

USEmasterGO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CDC_DB')DROP DATABASECDC_DBGO

CREATE DATABASECDC_DBGO

(二) 在开启数据库的CDC之前先查询一下状态,is_cdc_enabled值为0表示没有开启,1表示开启,当为数据库[CDC_DB]启用了CDC之后,在CDC_DB系统表中会出现下图Figure2所示的6个表;

/******* Step2:开启数据库CDC *******/

--查看数据库是否启用CDC

SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'CDC_DB'

--启用数据库CDC

USECDC_DBGO

EXECUTEsys.sp_cdc_enable_db;GO

--检查启用是否成功

SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END描述FROMsys.databasesWHERE NAME = 'CDC_DB'

fb3970633d510dc6a170f1b1ea1afd55.png

(Figure1:数据库CDC状态)

00aab8840d39046c8e522208544721e3.png

(Figure2:启用数据库CDC创建的系统表)

1f1a1ec9d27b4952d21d208825108d76.png

(Figure3:数据库CDC状态)

b06f6e37f2ba4ccb0acab5f515955aba.png

(Figure4:添加新用户和架构)

开启数据库的CDC之后,分别在用户和架构上创建新的用户cdc,新的架构cdc;

(三) 创建一个测试表,对表行变更启用捕获,为表[Department]启用CDC,首先会在系统表中创建[cdc].[dbo_Department_CT],会在Agent中创建两个作业,cdc.CDC_DB_capture和cdc.CDC_DB_cleanup,启用表变更捕获需要开启SQL Server Agent服务,不然会报错。每对一个表启用捕获就会生成一个向对应的记录表。

/******* Step3:对表启用变更捕获*******/

--创建测试表

USECDC_DBGO

CREATE TABLE [dbo].[Department]([DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](200) NULL,[GroupName] [nvarchar](50) NOT NULL,[ModifiedDate] [datetime] NOT NULL,[AddName] [nvarchar](120) NULL,CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED([DepartmentID] ASC)ON [PRIMARY])ON [PRIMARY]

GO

--对表启用捕获

EXECsys.sp_cdc_enable_table@source_schema= 'dbo',@source_name = 'Department',@role_name = N'cdc_Admin',@capture_instance = DEFAULT,@supports_net_changes = 1,@index_name = NULL,@captured_column_list = NULL,@filegroup_name = DEFAULT

--检查是否成功

SELECTname, is_tracked_by_cdc ,CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END描述FROMsys.tablesWHERE OBJECT_ID= OBJECT_ID('dbo.Department')--返回某个表的变更捕获配置信息

EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

54d6be88e98656ec5c781efd3f7cdec3.png

(Figure5:提示信息)

f6d9532e304576e70aa4c7febee99ea1.png

(Figure6:新增加的系统表)

17d86117cccbacfdabb2e6a7236de7ae.png

(Figure7:生成的捕获和清理作业)

14eaefd29c9846f5ab469da9e27dd7d3.png

(Figure8:表的CDC状态)

38516c3a48b304ba84821218069089f5.png

(Figure9:多了个数据库角色)

5e1f73a6f71d2694df769a6b5464d993.png

d85fe2d085c89eef9d241c0bd7168f81.png

(Figure10:sys.sp_cdc_enable_table配置选项)

上图深色部分的字段值是在执行sys.sp_cdc_enable_table的时候设置的。

(四) 测试插入数据、更新数据、删除数据,执行完这些DML,我们来观察下cdc.dbo_Department_CT帮我们记录些什么?

/******* Step4:测试DML变更捕获*******/

--测试插入数据

INSERT INTOdbo.Department(

Name ,

GroupName ,

ModifiedDate

)VALUES('Marketing','Sales and Marketing',GETDATE())--测试更新数据

UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE()WHERE Name = 'Marketing'

--测试删除数据

DELETE FROM dbo.Department WHERE Name='Marketing Group'

--查询捕获数据

SELECT * FROM cdc.dbo_Department_CT

f75f40580962029dc42498b10ae799e5.png

(Figure11:变更记录表)

对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值);

(五) 启用CDC之后,你怎么从中获取到数据呢?通过数据我们可以对数据进行恢复;

/******* Step6:使用LSN 查看CDC记录*******/

--http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx

SELECTsys.fn_cdc_map_time_to_lsn

('smallest greater than or equal', '2013-07-24 09:00:30') ASBeginLSNSELECTsys.fn_cdc_map_time_to_lsn

('largest less than or equal', '2013-07-24 23:59:59') ASEndLSN/******* 查看某时间段所有CDC记录*******/

DECLARE @FromLSN binary(10) =sys.fn_cdc_map_time_to_lsn

('smallest greater than or equal' , '2013-06-23 09:00:30')DECLARE @ToLSN binary(10) =sys.fn_cdc_map_time_to_lsn

('largest less than or equal' , '2013-07-26 23:59:59')SELECT CASE [__$operation]

WHEN 1 THEN 'DELETE'

WHEN 2 THEN 'INSERT'

WHEN 3 THEN 'Before UPDATE'

WHEN 4 THEN 'After UPDATE'

END Operation,[__$operation],[__$update_mask],DepartmentId,Name,GroupName,ModifiedDate,AddNameFROM [cdc].[fn_cdc_get_all_changes_dbo_Department](@FromLSN, @ToLSN, N'all update old')/*all 其中的update,只包含新值

all update old 包含新值和旧值*/

9d7f62558845a8b58d4f14d44ab2618e.png

(Figure15:通过时间获取LSN更新)

(六) CDC的维护

/******* Step5:维护CDC *******/

--返回所有表的变更捕获配置信息

EXECUTEsys.sp_cdc_help_change_data_capture;--返回某个表的变更捕获配置信息

EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

--查看对某个表的哪些列做了捕获监控,使用上面返回的capture_instance列值

EXECsys.sp_cdc_get_captured_columns@capture_instance = 'dbo_Department'

c37d0918990fdb9ee412d23d26f5580e.png

(Figure12:监控表字段信息)

由于sys.sp_cdc_enable_table 的参数:@captured_column_list = NULL,所以dbo.Department表的所有字段都进行监控了,如果你只关心某些字段,强烈建议在创建捕获的时候设置这个属性;

--所有数据库CDC Job信息

SELECT B.name,A.* FROM msdb.dbo.cdc_jobs ASALEFT JOIN sys.databases ASBON A.database_id =B.database_id--当前数据库CDC Job信息

EXEC sp_cdc_help_jobs

3afad4a0b7cbc2d6716f57cd512d76c6.png

(Figure13:数据库作业信息)

四.补充说明(Addon)

SQL Server记录数据变更有四种方法:触发器、Output子句、变更数据捕获(Change Data Capture 即CDC)功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。

CDC功能主要捕获SQLServer指定表的增删改操作;

CDC除了捕获数据变更之外,还能捕获DDL操作的变化;

无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限;

cdc._CT   可以看到,这样命名的表,是用于记录源表更改的表。对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录;

对于__$start_lsn列:由于更改是来源与数据库的事务日志,所以这里会保存其事务日志的开始序列号(LSN);

对于__$end_lsn列:

对于__$seqval列:

对于__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值);

对于__$update_mask列:

恢复模式为简单模式一样可以进行CDC;

虽然能捕获到数据变更,但是没有办法找到是谁更新的?

能使用这个做回滚嘛?备份的另外一种路径?对表更新不频繁的情况下?

如果是添加或者删除了某些字段DDL,那么创建的CDC表并没有做更改,那新字段的数据怎么捕获呢?修改字段长度等这些操作同样会一起修改CDC对应的表字段;

sys.sp_cdc_enable_table 的@role_name参数,是指角色-数据库角色,这个有什么用呢?应用程序角色又有什么用呢?

cdc.Person_Contact_CT这名字中CT代表什么意思呢?Capture Table?(用户.架构_表_CT)

SQL Server 自启动了两个job,一个捕获,一个清除,注意清除是默认凌晨2点,清除72小时以上的数据。如果同一数据库的表中CDC已经启用,不会重建job。

all

返回指定 LSN 范围内的所有更改。 对于由更新操作导致的更改,此选项只返回在应用更新之后包含新值的行。

all update old

返回指定 LSN 范围内的所有更改。 对于由更新操作导致的更改,此选项将返回在更新之前包含列值的行和更新之后包含列值的行。

五.参考文献(References)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值