使用binlog2sql恢復(fù)數(shù)據(jù)

誤刪除數(shù)據(jù)的話，oracle里面我們可以使用閃回功能找回誤操作的數(shù)據(jù)。

在MySQL里面，如果我們有延遲從庫(kù)的話，也可以找回之前的數(shù)據(jù)，但是有時(shí)候不太好使（因?yàn)樽窋?shù)據(jù)到誤操作前的準(zhǔn)確的時(shí)間點(diǎn)有時(shí)候也不太好把握）。

對(duì)于誤操作數(shù)據(jù)的閃回，我們一般推薦 binlog2sql 或者M(jìn)yFlash(美團(tuán)點(diǎn)評(píng)開(kāi)源的)

本篇文章， 我們介紹下 binlog2sql的用法：

binlog2sql 【首級(jí)推薦使用】

官網(wǎng)：https://github.com/danfengcao/binlog2sql

注意： binlog必須是row格式，并且是FULL類(lèi)型的記錄。

安裝：?

yum?update?nss?curl?libcurl?-y???#?centos6需要升級(jí)下這個(gè)包，不然沒(méi)法去github拉代碼
cd?/root/
git?clone?https://github.com/danfengcao/binlog2sql.git?&&?cd?binlog2sql
pip?install?-r?requirements.txt

授權(quán)：

>?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%';? ? -- 看到有3行受到影響

Query OK, 3 rows affected (0.01 sec)

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 |

+-------+---------------+-----+--------+---------+-----------+

解析出標(biāo)準(zhǔn)SQL：

cd?/root/?
先用mysqlbinlog找到誤操作的那個(gè)地方binlog文件及位移點(diǎn)，然后使用下面命令解析：
python?/root/binlog2sql/binlog2sql/binlog2sql.py?-h292.168.11.20?-P3306?-uflashback?-p'admin'?-d?testdb?-t?t_stud?--start-file='mysql-bin.000040'?--start-position=10030?--stop-position=10334

1 解析出回滾SQL：

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

解析出的結(jié)果類(lèi)似這樣：

UPDATE?`testdb`.`t_stud`?SET?`StuID`=11,?`Name`='John?Chengzhi',?`Age`=23,?`Gender`='M',?`ClassID`=6,?`TeacherID`=NULL?WHERE?`StuID`=11?AND?`Name`='John?Chengzhi'?AND?`Age`=100?AND?`Gender`='M'?AND?`ClassID`=6?AND?`TeacherID`?IS?NULL?LIMIT?1;?#start?10030?end?10334?time?2018-07-15?14:17:58
UPDATE?`testdb`.`t_stud`?SET?`StuID`=10,?`Name`='Lee?Lingshan',?`Age`=19,?`Gender`='F',?`ClassID`=3,?`TeacherID`=NULL?WHERE?`StuID`=10?AND?`Name`='Lee?Lingshan'?AND?`Age`=100?AND?`Gender`='F'?AND?`ClassID`=3?AND?`TeacherID`?IS?NULL?LIMIT?1;?#start?10030?end?10334?time?2018-07-15?14:17:58
UPDATE?`testdb`.`t_stud`?SET?`StuID`=5,?`Name`='Yu?Wutong',?`Age`=26,?`Gender`='M',?`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?14:17:58

2 修復(fù)下數(shù)據(jù)，去掉結(jié)尾的#及后面的內(nèi)容：

sed?-i.bak??'s/#.*//g'?/root/rollback.sql

3 將數(shù)據(jù)恢復(fù)到數(shù)據(jù)庫(kù)中：

use?testdb?;
UPDATE?`testdb`.`t_stud`?SET?`StuID`=11,?`Name`='John?Chengzhi',?`Age`=23,?`Gender`='M',?`ClassID`=6,?`TeacherID`=NULL?WHERE?`StuID`=11?AND?`Name`='John?Chengzhi'?AND?`Age`=100?AND?`Gender`='M'?AND?`ClassID`=6?AND?`TeacherID`?IS?NULL?LIMIT?1;
UPDATE?`testdb`.`t_stud`?SET?`StuID`=10,?`Name`='Lee?Lingshan',?`Age`=19,?`Gender`='F',?`ClassID`=3,?`TeacherID`=NULL?WHERE?`StuID`=10?AND?`Name`='Lee?Lingshan'?AND?`Age`=100?AND?`Gender`='F'?AND?`ClassID`=3?AND?`TeacherID`?IS?NULL?LIMIT?1;?
UPDATE?`testdb`.`t_stud`?SET?`StuID`=5,?`Name`='Yu?Wutong',?`Age`=26,?`Gender`='M',?`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;

執(zhí)行完后，再次看下數(shù)據(jù)，可以看到已經(jīng)恢復(fù)好了。

[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 |

+-------+---------------+-----+--------+---------+-----------+

補(bǔ)充：

解析模式：

--stop-never 持續(xù)同步binlog?？蛇x。不加則同步至執(zhí)行命令時(shí)最新的binlog位置。

-K, --no-primary-key 對(duì)INSERT語(yǔ)句去除主鍵?？蛇x。

-B, --flashback 生成回滾語(yǔ)句，可解析大文件，不受內(nèi)存限制，每打印一千行加一句SLEEP SELECT(1)。可選。與stop-never或no-primary-key不能同時(shí)添加。

解析范圍控制：

--start-file 起始解析文件。必須。

--start-position/--start-pos start-file的起始解析位置?？蛇x。默認(rèn)為start-file的起始位置。

--stop-file/--end-file 末尾解析文件?？蛇x。默認(rèn)為start-file同一個(gè)文件。若解析模式為stop-never，此選項(xiàng)失效。

--stop-position/--end-pos stop-file的末尾解析位置。可選。默認(rèn)為stop-file的最末位置；若解析模式為stop-never，此選項(xiàng)失效。

--start-datetime 從哪個(gè)時(shí)間點(diǎn)的binlog開(kāi)始解析，格式必須為datetime，如'2016-11-11 11:11:11'?？蛇x。默認(rèn)不過(guò)濾。

--stop-datetime 到哪個(gè)時(shí)間點(diǎn)的binlog停止解析，格式必須為datetime，如'2016-11-11 11:11:11'?？蛇x。默認(rèn)不過(guò)濾。

對(duì)象過(guò)濾：

-d, --databases 只輸出目標(biāo)db的sql?？蛇x。默認(rèn)為空。

-t, --tables 只輸出目標(biāo)tables的sql?？蛇x。默認(rèn)為空。

注意：?

提取出來(lái)的sql 實(shí)際上是倒序的。

例如誤操作的sql是如下2步：? 2018101916 ---> 11 ---> 12

step0： 原始t1表的 work_id='2018101916'?

step1： update test.t1 set work_id='11' where id=32 limit 1 ;? # 簡(jiǎn)單實(shí)驗(yàn)，update不更新其它列的數(shù)據(jù)

step2： update test.t1 set work_id='22' where id=32 limit 1 ;? # 簡(jiǎn)單實(shí)驗(yàn)，update不更新其它列的數(shù)據(jù)

閃回出來(lái)的sql是這樣的：? 12 ---> 11 ---> 2018101916

UPDATE `test`.`t1` SET `id`=32, `work_id`='11', `username`='admin' LIMIT 1;

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