Parameter binlog_row_image sets MINIMAL, have you been cheated today?

Today, the netizen "Fanta" discussed with me a parameter binlog_row_image, which is set to MINIMAL in what scenario. I think this case is very meaningful, especially in the production environment, it is necessary to set this parameter carefully.

First of all, this MINIMAL will only record the affected rows in binlog, instead of recording all the complete records before and after changes, as the default FULL does. Here, there will be a problem: the data has been mistakenly changed and cannot be flashed back with tools such as mysqlbinlog. In addition, big data uses canal to extract binlog, which will cause a series of problems such as inaccurate data.

Let's take a look at what's recorded in binlog through a simulation experiment.

The table structure is as follows:

The data in it:

At this point, perform a update change operation:

Take a look at what is recorded in binlog right away:

Note that the contents of the red box show that the complete original data before the change has not been recorded and cannot be flashed back.

Although MINIMAL can reduce the file size of binlog (binlog_format = ROW) and reduce disk IO, this can be dangerous from a data security perspective!

I would like to thank the netizen "Fanta" for providing material.