Using MySQL Log simulation to restore Data Change track II

In the previous article "using MySQL Log simulation to recover data change track", we have introduced the general idea of our solution. Its principle is the log fishing method mentioned by a netizen. After mysqlbinlog is used to parse the binlog, we can find that all the useful information for us starts with ###. We can get it through regular expression matching. In the log

In the previous article "using MySQL Log simulation to recover data change track", we have introduced the general idea of our solution. Its principle is the log fishing method mentioned by a netizen. After mysqlbinlog is used to parse the binlog, we can find that all the useful information for us starts with ###. We can get it through regular expression matching. In the log

In the previous article "using MySQL Log simulation to recover data change track", we have introduced the general idea of our solution. Its principle is the log fishing method mentioned by a netizen.

After mysqlbinlog is used to parse the binlog, we can find that all the useful information for us starts with ###. We can get it through regular expression matching.

The log contains characters such as @ 1 and @ 2. This indicates the field name of the table structure, that is, @ 1 indicates the first field in the table, and @ 2 indicates the second field in the table. Then, we can query the INFORMATION_SCHEMA.COLUMNS table to find all the fields in the table and replace them one by one. For INSERT and DELETE operations, there is only one data row, while UPDATE has two data rows. We need a second copy.

For example:

Table Structure Information

Binlog Information

We need to translate it into fully executable SQL: insert into a (id, num) values (1,199 );

When intercepting field values, we encounter several pitfalls:

1. The field value is of the date type. The log is saved in the format of @ 1 = 13:14:35. At this time, you must quote 13:14:35.

2. negative number. The negative number is saved in the log in the format of @ 1 =-1 (4294967295). In this case, we only need to '-1.

3. Escape Character Set. The field value information displayed in the log is the same as the field value information in the database. However, when mysql inserts data into the database, it is escaped. As a result, the content in the log cannot be immediately intercepted and used directly. In this regard, we modified the mysqlbinlog tool so that the parsed text is not escaped. Examples:

Root @ test 09:50:58> insert into tx values ('a \ 'B ');

Root @ test 09:56:47> insert into tx values ('a \ tb ');

Root @ test 10:06:01> insert into tx values ('a \ bb ');

Root @ test 10:06:04> insert into tx values ('a \ 0b ');

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

ORIGINAL VERSION

### Insert into test. tx
### SET
###@ 1 = 'a 'B'
### Insert into test. tx
### SET
###@ 1 = 'a \ x09b'
### Insert into test. tx
### SET
###@ 1 = 'a \ x08b'
### Insert into test. tx
### SET
###@ 1 = 'a \ x00b'

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

Modified Version

### Insert into test. tx
### SET
###@ 1 = 'a \ 'B'
### Insert into test. tx
### SET
###@ 1 = 'a \ tb'
### Insert into test. tx
### SET
###@ 1 = 'a \ bb'
### Insert into test. tx
### SET
###@ 1 = 'a \ 0b'

Specific content can refer to: https://bugs.launchpad.net/percona-server/+bug/949965

4. dubyte characters.

The binlog is parsed once.

·······

###@ 1 = 'casual women's shoes with white and yellow powder taobao'

······

The parsed SQL statement is insert into a values ('values \ '). An error is returned when the SQL statement is inserted back to the database. The reason is that there is an extra escape character. When you go back to view the binlog, there is no extra escape character '\'. After careful reading, we find that this is not the 'black'. The hexadecimal system of the former is (FC5C), and The hexadecimal System of the latter is (BADA ). After viewing the ASCII code table, it is found that the 5C corresponds to the '\' character, and the escape character set has been modified for the third type of problem, as a result, it becomes @ 1 = 'pipeline \ 'when parsed \'.

5. No problems found. This requires more test practices for us to discover.

PS: later I will present the modified mysqlbinlog and tool script code.

Original article address: using MySQL Log simulation to restore Data Change track II, thanks to the original author for sharing.