Use MySQL logs to simulate data change tracks

Question first ~~, This weekend, I gave it to diaosi brother (@ Ying yuan. With such a requirement, we hope to use binlog to restore data changes in a certain period of time in the database. Body: Let's first explain why binlog can be used to do such a thing. Because our binlog adopts the ROW mode, the binlog records the change information of all columns of each data record.

Question first ~~, This weekend, I gave it to diaosi brother (@ Ying yuan. With such a requirement, we hope to use binlog to restore data changes in a certain period of time in the database. Body: Let's first explain why binlog can be used to do such a thing. Because our binlog adopts the ROW mode, the binlog records the change information of all columns of each data record.

Question

Speak out first ~~, This weekend, I gave it to diaosi brother (@ Ying yuan.

With such a requirement, we hope to use binlog to restore data changes in a certain period of time in the database.

Body:

Let's first explain why binlog can be used to do such a thing.

Because our binlog uses the ROW mode, the binlog records the changes in all columns of each data record. This information can be considered as a data source.

First, let's take a look at the binlog, through the command line

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

Parse the binlog. INSERT, UPDATE, and DELETE operations are as follows:

In the figure, @ 1 and @ 2 indicate the column name of table a. The information after the equal sign is the value of the column.

By assembling this information, we plan to convert all operations into INSERT operations and re-INSERT them into the database, so that we can see a Data Change track.

Of course, we need to note that we should replace the primary key in the table structure with a common index and remove the unique constraints to ensure that each assembled SQL statement can be executed smoothly.

For INSERT and DELETE operations, the data items are unique, while UPDATE has two parts. Because it is a sequential operation, we need the part after SET in UPDATE, that is, the changed data.

Let's take a look at the column:

First, we have a table a with the following structure:

Then we made some operations on it, as shown below:

Suppose these operations are performed at 16:20:00? The operation is performed within 16:21:00 on January 25. It was also operated in a later period.

Now we want to see at 16:20:00? The operation content within 16:21:00 a.m. on February 25.

The recovered database is as follows:

In this way, we can observe the change process of the num column with id = 1 in table.

Typical scenario: goods inventory reduction.

Finally, the method has the following shortcomings:

For example, the two data entries in the red box indicate the results after UPDATE and DELETE. We are considering changing the table structure and adding a new column to indicate what operations cause data changes, so that we can see the trajectory of data changes more intuitively.

PS: in the next article, we will introduce the problems encountered in the implementation process and the implementation tools.

Original article address: use MySQL logs to simulate data change tracks. Thanks to the original author for sharing.