mysql 轨迹数据存储_利用MySQL日志模拟数据变化轨迹

题记 先吐槽~~，这周末给屌丝哥(@应元)给废了。 有这么一个需求，希望通过binlog去恢复数据库某个时间段内数据的变化情况。 正文： 先说一下为什么用binlog可以做这么一件事情。 由于我们的binlog采用ROW模式，那么binlog会记录每一条数据所有列的变化信息

题记

先吐槽~~，这周末给屌丝哥(@应元)给废了。

有这么一个需求，希望通过binlog去恢复数据库某个时间段内数据的变化情况。

正文：

先说一下为什么用binlog可以做这么一件事情。

由于我们的binlog采用ROW模式，那么binlog会记录每一条数据所有列的变化信息，这些信息，我们就可以认为是一个数据源。

首先，我们先看一下binlog，通过命令行

mysqlbinlog –no-defaults -v –start-datetime=”2012-10-01 00:00:00″? –stop-datetime=”2012-10-1 02:00:00″ mysql-bin.000001 > tmp.log

去解析binlog。INSERT、UPDATE、DELETE三种操作如下图：

图中的@1、@2就是表示表a的列名，等号后面的信息就是该列的值。

我们打算通过拼装这些信息，将所有的操作都转换成INSERT操作，重新插入到数据库中，这样就可以看到一个数据的变化轨迹。

当然，我们需要注意一点，将表结构中的主键替换成普通索引，将唯一约束去除，保证每一条拼装出来的sql都能顺利被执行。

对于INSERT和DELETE两个操作，其数据项是唯一的，而UPDATE则有两部分。由于是顺序操作，所以我们需要的是UPDATE中SET之后的部分，即变化后的数据。

我们看一下列子：

首先我们有一个表a，结构如下：

然后我们对其做了一些操作，如下：

假设这些操作的时间在2012-11-25 16:20:00?至 2012-11-25 16:21:00内操作。在之后的时间内也被操作过。

现在我们就想看到在2012-11-25 16:20:00?至 2012-11-25 16:21:00内的操作内容。

通过该思路的方法，恢复后的数据库内容如下：

这样我们就可以观察到表a中id=1的num列的变化过程。

经典场景：商品减库存。

最后，方法还有不足之处：

如上图中红色方框内这两条数据，其实应该表示一个是UPDATE之后的结果，一个是DELETE的结果。我们在考虑时候对表结构进行变更，增加新的一列，表示是什么操作引起数据变化，这样就更加直观的看到数据变化的轨迹了。

PS：下篇文章我们会给出在实现过程中碰到的问题以及实现工具。