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

If the data is deleted by mistake, we can use the flashback feature to retrieve the misoperated data in oracle.

In MySQL, if we have a delay from the database, we can also retrieve the previous data, but sometimes it is not easy to use (because it is sometimes difficult to recover the data to the exact time before misoperation).

For flashbacks of mismanipulated data, we generally recommend binlog2sql or MyFlash (open source by Meituan Dianping)

In this article, we introduce the use of binlog2sql:

Binlog2sql [recommended at the first level]

Official website: https://github.com/danfengcao/binlog2sql

Note: the binlog must be in row format and a record of type FULL.

Installation:

Yum update nss curl libcurl-y # centos6 needs to upgrade this package, otherwise you can't go to github to pull the code cd / root/git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sqlpip install-r requirements.txt

Authorization:

> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'flashback'@'192.168.11.20' identified by' admin'

> USE testdb

[testdb] > SELECT * FROM t_stud WHERE name LIKE'Y%'

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 5 | Yu Wutong | 26 | M | 3 | 1 | |

| | 10 | Lee Lingshan | 19 | F | 3 | NULL |

| | 11 | John Chengzhi | 23 | M | 6 | NULL |

+-+ +

[testdb] > UPDATE t_stud SET age=100 WHERE name LIKE'Y% destroy;-- see 3 lines affected

Query OK, 3 rows affected (0.01sec)

Rows matched: 3 Changed: 3 Warnings: 0

[testdb] > SELECT * FROM t_stud WHERE name LIKE'Y%'

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 5 | Yu Wutong | 100 | M | 3 | 1 | |

| | 10 | Lee Lingshan | 100 | F | 3 | NULL |

| | 11 | John Chengzhi | 100 | M | 6 | NULL |

+-+ +

Parse out the standard SQL:

Cd / root/ first uses mysqlbinlog to find the binlog file and the displacement point where it was misoperated, and then uses the following command to parse: python / root/binlog2sql/binlog2sql/binlog2sql.py-h292.168.11.20-P3306-uflashback-pumped admin'- d testdb-t t_stud-- start-file='mysql-bin.000040'-- start-position=10030-- stop-position=10334

1. Resolve the rollback SQL:

Cd / root/ python / root/binlog2sql/binlog2sql/binlog2sql.py-- flashback-h292.168.11.20-P3306-uflashback-pawnadmin'- d testdb-t t_stud-- start-file='mysql-bin.000040'-- start-position=10030-- stop-position=10334

The parsed result looks like this:

UPDATE `testdb`.`t _ stud` SET `StuID` = 11, `Name` = 'John Chengzhi', `Age` = 23, `Gender` =' Lee Lingshan', 'Age`, `ClassID` = 6, `TeacherID` = NULL WHERE `StuID` = 11 AND `Name` =' John Chengzhi' AND `Age` = 100 AND `Gender` ='M' AND `ClassID` = 6 AND `TeacherID` Gender`; # start 10030 end 10334 time 2018-07-15 14:17:58UPDATE `testdb`.`t _ Stud``TeachID` = 10, `Name` = 'Lee Lingshan', `Age` = 19, `Gender` =' fit, `ClassID` = 3, `TeacherID` = NULL WHERE `StuID` = 10 AND `NameID` Lee Lingshan' AND `Age100 = `Gender` = 'AND `ClassID3 = AND `TeachID` IS NULL LIMIT # start 10030 end 10334 time 2018-07-15 14:17:58UPDATE `testdb`.`t _ stud`SET `StuID` = 5, `Name` ='Yu Wutong', `Age` = 26, `Gender` = 'WHERE, `ClassID` = 3, `TeacherID` = 1 WHERE `StuID` = 5 AND `Name` =' Yu Wutong' AND `Age` = 100 AND `Gender` ='M' AND `ClassID` = 3 AND `TeacherID` = 1 LIMIT 1; # start 10030 end 10334 time 2018-07-15

2 repair the data and remove the # at the end and the following content:

Sed-i.bak 's Compact GOG'/ root/rollback.sql

3 restore the data to the database:

Use testdb; UPDATE `testdb`.`t _ stud`StuID` = 11, `Name` = 'John Chengzhi', `Age` = 23, `Gender` =' John Chengzhi', `ClassID` = 6, `TeacherID` = NULL WHERE `StuID` = 11 AND `Name` = 'John Chengzhi' AND `Age` = 100 AND `Gender` =' M' AND `ClassID` = 6 AND `TeacherID` = 6 AND `TeacherID`UPDATE `testdb`.`t _ stud` `SET `StuID` = 10, `Name` = 'Lee Lingshan', `Age` = 19, `Gender` =' fit, `ClassID` = 3, `TeacherID` = NULL WHERE `StuID` = 10 `AND Name` = Lee Lingshan' AND `Age` AND `Gender`F` AND UPDATE `testdb`.`t _ stud`SET `StuID` = 5, `Name` ='Yu Wutong', `Age` = 26, `Gender` = 'WHERE, `ClassID` = 3, `TeacherID` = 1 WHERE `StuID` = 5 AND `Name` =' Yu Wutong' AND `Age` = 100 AND `Gender` ='M' AND `ClassID` = 3 AND `TeacherID` = 1 LIMIT 1

After the execution, take a look at the data again, you can see that it has been restored.

[testdb] > select * from t_stud where name like'Y%'

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 5 | Yu Wutong | 26 | M | 3 | 1 | |

| | 10 | Lee Lingshan | 19 | F | 3 | NULL |

| | 11 | John Chengzhi | 23 | M | 6 | NULL |

+-+ +

Add:

Parsing mode:

-- stop-never continuously synchronizes binlog. Optional. Add or synchronize to the latest binlog location when the command is executed.

-K,-- no-primary-key removes the primary key from the INSERT statement. Optional.

-B,-- flashback generates a rollback statement that parses large files without memory restrictions, adding a SLEEP SELECT (1) for every thousand lines printed. Optional. Cannot be added at the same time as stop-never or no-primary-key.

Parse range control:

-- start-file starting parsing file. Must.

-- the starting resolution position of the start-position/--start-pos start-file. Optional. The default is the starting position of start-file.

-- parse the file at the end of stop-file/--end-file. Optional. The default is start-file the same file. If the parsing mode is stop-never, this option has no effect.

-- the parsing position at the end of the stop-position/--end-pos stop-file. Optional. The default is the last location of stop-file; if the parsing mode is stop-never, this option has no effect.

At which point in time does the start-datetime begin to be parsed by binlog, and the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-11-1-1-1-11-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 Optional. Does not filter by default.

At which point in time does the binlog of stop-datetime stop parsing? the format must be datetime, such as' 2016-11-11-11-11-11-11-11-11-11-11-11-1-1-11-11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11. Optional. Does not filter by default.

Object filtering:

-d,-- databases outputs only the sql of the target db. Optional. The default is empty.

-t,-- tables outputs only the sql of the target tables. Optional. The default is empty.

Note:

The extracted sql is actually in reverse order.

For example, the misoperation of sql is as follows: 2018101916-- > 11-> 12

Step0: the work_id='2018101916' of the original T1 table

Step1: update test.t1 set work_id='11' where id=32 limit 1; # simple experiment, update does not update the data of other columns

Step2: update test.t1 set work_id='22' where id=32 limit 1; # simple experiment, update does not update the data of other columns

The flashback sql looks like this: 12-> 11-- > 2018101916

UPDATE `test`.`t1` SET `id` = 32, `Username` = 'admin' LIMIT 1

UPDATE `test`.`t1` SET `id` = 32, `work_ id` = '2018101916', `username` = 'admin' LIMIT 1

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.