誤删除資料的話,oracle裡面我們可以使用閃回功能找回誤操作的資料。
在MySQL裡面,如果我們有延遲從庫的話,也可以找回之前的資料,但是有時候不太好使(因為追資料到誤操作前的準确的時間點有時候也不太好把握)。
對于誤操作資料的閃回,我們一般推薦 binlog2sql 或者MyFlash(美團點評開源的)
本篇文章, 我們介紹下 binlog2sql的用法:
binlog2sql 【首級推薦使用】
官網:https://github.com/danfengcao/binlog2sql
注意: binlog必須是row格式,并且是FULL類型的記錄。
安裝:
yum update nss curl libcurl -y # centos6需要更新下這個包,不然沒法去github拉代碼
cd /root/
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
授權:
> 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
| 5 | Yu Wutong | 100 | M | 3 | 1 |
| 10 | Lee Lingshan | 100 | F | 3 | NULL |
| 11 | John Chengzhi | 100 | M | 6 | NULL |
解析出标準SQL:
cd /root/
先用mysqlbinlog找到誤操作的那個地方binlog檔案及位移點,然後使用下面指令解析:
python /root/binlog2sql/binlog2sql/binlog2sql.py -h192.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 -h192.168.11.20 -P3306 -uflashback -p'admin' -d testdb -t t_stud --start-file='mysql-bin.000040' --start-position=10030 --stop-position=10334
解析出的結果類似這樣:
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 修複下資料,去掉結尾的#及後面的内容:
sed -i.bak 's/#.*//g' /root/rollback.sql
3 将資料恢複到資料庫中:
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;
執行完後,再次看下資料,可以看到已經恢複好了。
[testdb] > select * from t_stud where name like 'Y%';
補充:
解析模式:
--stop-never 持續同步binlog。可選。不加則同步至執行指令時最新的binlog位置。
-K, --no-primary-key 對INSERT語句去除主鍵。可選。
-B, --flashback 生成復原語句,可解析大檔案,不受記憶體限制,每列印一千行加一句SLEEP SELECT(1)。可選。與stop-never或no-primary-key不能同時添加。
解析範圍控制:
--start-file 起始解析檔案。必須。
--start-position/--start-pos start-file的起始解析位置。可選。預設為start-file的起始位置。
--stop-file/--end-file 末尾解析檔案。可選。預設為start-file同一個檔案。若解析模式為stop-never,此選項失效。
--stop-position/--end-pos stop-file的末尾解析位置。可選。預設為stop-file的最末位置;若解析模式為stop-never,此選項失效。
--start-datetime 從哪個時間點的binlog開始解析,格式必須為datetime,如'2016-11-11 11:11:11'。可選。預設不過濾。
--stop-datetime 到哪個時間點的binlog停止解析,格式必須為datetime,如'2016-11-11 11:11:11'。可選。預設不過濾。
對象過濾:
-d, --databases 隻輸出目标db的sql。可選。預設為空。
-t, --tables 隻輸出目标tables的sql。可選。預設為空。
注意:
提取出來的sql 實際上是倒序的。
例如誤操作的sql是如下2步: 2018101916 ---> 11 ---> 12
step0: 原始t1表的 work_id='2018101916'
step1: update test.t1 set work_id='11' where id=32 limit 1 ; # 簡單實驗,update不更新其它列的資料
step2: update test.t1 set work_id='22' where id=32 limit 1 ; # 簡單實驗,update不更新其它列的資料
閃回出來的sql是這樣的: 12 ---> 11 ---> 2018101916