Shulou(Shulou.com)06/01 Report--

Premise: binlog mode is row, isolation mode is read-committed

For misoperation of update, you can simulate the flashback feature of oralce and make use of binlog log. The specific operations are as follows:

Mysql > select * from test1

+-+

| | dept | name | salary | |

+-+

| | it | gaopeng | 100 | |

| | it | yhb | 100 | |

| | it | dzy | 100 | |

| | uu | yl | 100 | |

| | uu | yl1 | 200 | |

| | uu | yl3 | 300 | |

+-+

6 rows in set (0.05 sec)

Mysql > update test1 set name='test'

Query OK, 6 rows affected (0.06 sec)

Rows matched: 6 Changed: 6 Warnings: 0

Mysql > select * from test1

+-+

| | dept | name | salary | |

+-+

| | it | test | 100 | |

| | it | test | 100 | |

| | it | test | 100 | |

| | uu | test | 100 | |

| | uu | test | 200 | |

| | uu | test | 300 | |

+-+

6 rows in set (0.00 sec)

Mysql > exit

[root@localhost data] # mysqlbinlog-- no-defaults-- base64-output=decode-rows-v-v db-bin.000016 | grep-B 15 'test' | more

Ps: grep-B 15 'test' because the changed field value is test, so we choose the first 15 rows of test and all the data after that.

# at 384

# 150424 14:07:59 server id 199 end_log_pos 456 CRC32 0x7b4aabf1 Query thread_id=2 exec_time=0 error_code=0

SET timestamp 1429855679

BEGIN

/ *! * /

# at 456

# 150424 14:07:59 server id 199 end_log_pos 510 CRC32 0x5f63d428 Table_map: `test`.`test1` mapped to number 74

# at 510

# 150424 14:07:59 server id 199 end_log_pos 699 CRC32 0xf362ace6 Update_rows: table id 74 flags: STMT_END_F

# UPDATE `test`.`test1`

# WHERE

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 VARSTRING gaopeng / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyhb' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomdzy' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyl' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyl1' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3000200 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3000200 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyl3' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room300 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

This is the log we need to get this part of the data.

[root@localhost data] # mysqlbinlog-- no-defaults-- base64-output=DECODE-ROWS-v-v db-bin.000016 | sed-n'/ # at 510 root/1.txt

Ps:sed-n'/ # at 510Universe CommitUniverse p' represents the content that starts with the selection of'# at 510', ends with the first commit, and then leads to the 1.txt file.

The retrieved documents are as follows:

[root@localhost ~] # cat 1.txt

# at 510

# 150424 14:07:59 server id 199 end_log_pos 699 CRC32 0xf362ace6 Update_rows: table id 74 flags: STMT_END_F

# UPDATE `test`.`test1`

# WHERE

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 VARSTRING gaopeng / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyhb' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomdzy' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomit` / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyl' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room100 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyl1' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3000200 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3000200 / * INT meta=0 nullable=1 is_null=0 * /

# UPDATE `test`.`test1`

# WHERE

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2roomyl3' / * VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 3room300 / * INT meta=0 nullable=1 is_null=0 * /

# SET

# @ 1roomuu' / VARSTRING (20) meta=20 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# @ 3room300 / * INT meta=0 nullable=1 is_null=0 * /

# at 699

# 150424 14:07:59 server id 199 end_log_pos 730 CRC32 0x83588cbb Xid = 44

Com _ MIT _ blank /

[root@localhost ~] #

Now begin to convert this part of the data into an executable sql format. A large number of sed commands will be used, and sed himself is not proficient. I will explain the purpose achieved after the execution of each sed. For more information on the parameters of the sed command, please refer to

[root@localhost ~] # sed'/ WHERE/ {: a sed-r'/ WHERE/ {: sed-r'/ WHERE/ {: [[^\ n] *\)\ n\ (. *\)\ n\ (. *\) /\ 3\ n\ 2\ n\ 1 /} '4.txt | sed-r' / WHERE/ Sed'/ WHERE/ {: a TrachetNumberAccord / WHERE/ {: a TracenceAccorditsAccordAccord g}; spacedAccording.AccorditCompCompCompCompCompCompCompCompActionAccord g' | sed'/ ^ $/ d' > 9.sql

[root@localhost ~] # cat 9.sql

UPDATE `test`.`test1`

SET

@ 1 percent it`

@ 2 percent gaopeng`

@ 3000100

WHERE

@ 1 percent it`

UPDATE `test`.`test1`

SET

@ 1 percent it`

@ 2percent yhb`

@ 3000100

WHERE

@ 1 percent it`

UPDATE `test`.`test1`

SET

@ 1 percent it`

@ 2percent dzy'

@ 3000100

WHERE

@ 1 percent it`

UPDATE `test`.`test1`

SET

@ 1percent uu'

@ 2roomyl'

@ 3000100

WHERE

@ 1percent uu'

UPDATE `test`.`test1`

SET

@ 1percent uu'

@ 2roomyl1'

@ 3000200

WHERE

@ 1percent uu'

UPDATE `test`.`test1`

SET

@ 1percent uu'

@ 2roomyl3'

@ 3000300

WHERE

@ 1percent uu'

[root@localhost ~] # sed-I-r's / (@ 3room.*), /\ 1Universe g '9.sql

[root@localhost ~] # sed-I 'Accordache1DETT _

Explain each command of sed step by step:

Sed'/ WHERE/ {: a transposter Nacht * *\ ([^\ n] *\)\ n\ (. *\)\ n\ (. *\) /\ 3\ n\ 1 /} '4.txt | sed's pedigree / *, / g' | sed-r' / WHERE/ {: a transmissible Nentax / WHERE/ {: a transmissible Naptic spur 3-abridged baathansUniverse 2.percent WHERE/ g}'| sed'/ WHERE/ {: / @ 1Accordant Bachet / sed'/ ^ $/ d'

Sed'/ WHERE/ {: a transact NittAccording to the 4.txt /\ ([^\ n] *\)\ n\ (.*\)\ n\ (.*\) /\ 3\ n\ 2\ n\ 1 /} '4.txt

This line means to change the where in the log to set and the set to where. Because in the bin-log log, the data before the change is after where, and after set is the data after update, now we want to roll back the data before update, so we have to swap where and set.

| | sed's Compact alarm engine / GMT /\ /\ *. * / / g'|

The purpose of this sed is to remove # and / *. * / from the binlog log.

| | sed-r'/ WHERE/ {: a * n *

This line removes non-key fields. As a where condition, you can also remove only the fields that are only update, and you can choose freely, as long as you ensure that the query result of the condition is the only row.

| | sed'/ WHERE/ {: a TracterNramBachtersAccordentandlemagandlemagandlemag.sAccordit.Company.Compact

This line means to add';'at the end of each statement, and the ending symbol of mysql is';', so you need to add this a Terminator.

| | sed'/ ^ $/ d' |

One line means to remove the superfluous, which is relatively simple. There is not much explanation.

Sed-I-r's / (@ 3room.*), /\ 1Universe g '9.txt removes the', 'after the last field of set (in this case @ 3).

Sed-I-I-9.txt change @ 1 ~ (nd) _.

[root@localhost ~] # more 9.txt

UPDATE `test`.`test1`

SET

Dept='it'

Name='gaopeng'

Salary=100

WHERE

Dept='it'

UPDATE `test`.`test1`

SET

Dept='it'

Name='yhb'

Salary=100

WHERE

Dept='it'

UPDATE `test`.`test1`

SET

Dept='it'

Name='dzy'

Salary=100

WHERE

Dept='it'

UPDATE `test`.`test1`

SET

Dept='uu'

Name='yl'

Salary=100

WHERE

Dept='uu'

UPDATE `test`.`test1`

SET

Dept='uu'

Name='yl1'

Salary=200

WHERE

Dept='uu'

UPDATE `test`.`test1`

SET

Dept='uu'

Name='yl3'

Salary=300

WHERE

Dept='uu'

At this point, the sql that needs to be rolled back is retrieved, and the file can be modified. There is no repetition here.

Ps: there is actually a problem with this test. When restoring the data, there is no way to restore the data. This is because I am in the table of the test, and there is no unique value. Therefore, when you test, you must find a table with a unique value for testing. There is no unique value, which will cause the data to be restored to its original appearance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.