利用binlog2sql快速閃回誤删除資料 - 别拿豆包不當幹糧

今兒給大家分享一篇利用binlog2sql閃回工具，來恢複誤删除的資料。我們都知道binlog的作用是備份恢複和完成MySQL的主從複制功能。利用mysqlbinlog工具可以進行基于時間點或者位置偏移量的資料恢複工作，在生産環境中遇到誤删除，改錯資料的情況，那是常有的事兒。我們都知道Oracle資料庫有閃回功能，而MySQL本身沒有自帶閃回，但我們可以使用binlog2sql來完成這項工作。

我們都知道binlog是以event作為機關，來記錄資料庫變更的資料資訊，閃回就是可以幫助我們重制這些變化資料資訊之前的操作。也就是說對于insert操作，會生成delete語句，反之delete操作，會生成insert語句。對于update操作，也會生成相反的update語句。這款工具隻能使用在binlog格式為row模式下。

下面進行實戰演練：

binlog2sql工具的下載下傳位址：

link 第一步：環境準備安裝各種依賴的工具包清單

python-pip 
PyMySQL 
python-mysql-replication
wheel argparse
           

第二步：解壓binlog2sql軟體，指令如下：

unzip  binlog2sql-master.zip
cd binlog2sql-master
pip install –r requirements.txt           

第三步：通過python binlog2sql.py --help指令，來檢視重要參數的使用

-B, --flashback 生成復原語句
--start-file 需要解析的binlog檔案
--start-position 解析binlog的起始位置
--stop-position解析binlog的結束位置
--start-datetime 從哪個時間點的binlog開始解析，格式必須為datetime
--stop-datetime 到哪個時間點的binlog停止解析，格式必須為datetime
-d, --databases 隻輸出目标db的sql
-t, --tables 隻輸出目标tables的sql           

第四步：開始模拟資料删除

首先删除掉zs庫下，t表中的資料

root@db 14:26:  [zs]> select * from t;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | aaa  | bj      |
|  2 | bbb  | sh      |
|  3 | ccc  | gz      |
|  4 | ddd  | sy      |
|  5 | eee  | fj      |
+----+------+---------+
5 rows in set (0.00 sec)

root@db 14:26:  [zs]> delete from t;
Query OK, 5 rows affected (0.04 sec)

root@db 14:27:  [zs]> select * from t;
Empty set (0.00 sec)           

第五步：需要建立一個閃回使用者

create user 'zs_test'@'%' identified by '123456';
grant select,replication slave,replication client on *.* to 'zs_test'@'%' ;
flush privileges;           

第六步：确定目前binlog檔案和position位置

root@db 14:41:  [zs]> show master status;
+---------------------+----------+--------------+------------------+------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------------+----------+--------------+------------------+------------------------------------------+
| mysql-binlog.000002 |     2091 |              |                  | e10f0ead-d595-11e7-82cb-080027cd683a:1-8 |
+---------------------+----------+--------------+------------------+------------------------------------------+           

可以看到目前binlog是：mysql-binlog.000002

位置偏移量：2091

第七步：需要預估下時間，誤操作的時間範圍應該在下午2點20分到2點30分之間。指令如下：

python binlog2sql.py -h192.168.56.102 -P3306 -uzs_test -p123456 -dzs -tt --start-file='mysql-binlog.000002' --start-datetime='2017-11-30 14:20:00' --stop-datetime='2017-11-30 14:30:00'           

輸出結果：

DELETE FROM zs.t WHERE address='bj' AND id=1 AND name='aaa' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='sh' AND id=2 AND name='bbb' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='gz' AND id=3 AND name='ccc' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='sy' AND id=4 AND name='ddd' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46

DELETE FROM zs.t WHERE address='fj' AND id=5 AND name='eee' LIMIT 1; #start 1214 end 1427 time 2017-11-30 14:27:46

從解析結果中我們了解到，誤操作sql的位置是在1214~1427之間;這樣就可以進一步過濾，使用flashback模式生成復原sql；

指令如下：

python binlog2sql.py -h192.168.56.102 -P3306 -uzs_test -p123456 -dzs -tt --start-file='mysql-binlog.000002' --start-position=1214 --stop-position=1427 -B >t_rollback.sql           

檢視閃回導出檔案：

[root@node3 binlog2sql]# cat t_rollback.sql

INSERT INTO

zs

.

t

(

address

,

id

name

) VALUES ('fj', 5, 'eee'); #start 1214 end 1427 time 2017-11-30 14:27:46

zs

t

address

id

name

) VALUES ('sy', 4, 'ddd'); #start 1214 end 1427 time 2017-11-30 14:27:46

zs

t

address

id

name

) VALUES ('gz', 3, 'ccc'); #start 1214 end 1427 time 2017-11-30 14:27:46

zs

t

address

id

name

) VALUES ('sh', 2, 'bbb'); #start 1214 end 1427 time 2017-11-30 14:27:46

zs

t

address

id

name

) VALUES ('bj', 1, 'aaa'); #start 1214 end 1427 time 2017-11-30 14:27:46

第八步：應用復原檔案，恢複資料。指令如下：

/usr/local/mysql/bin/mysql -uroot -proot123 zs < t_rollback.sql           

第九步：檢驗恢複資料是否成功

root@db 15:09:  [zs]> select * from t;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | aaa  | bj      |
|  2 | bbb  | sh      |
|  3 | ccc  | gz      |
|  4 | ddd  | sy      |
|  5 | eee  | fj      |
+----+------+---------+
5 rows in set (0.00 sec)           

驗證恢複資料成功！

工具雖小，但功能很強大，我們要善于發現周圍的資源，利用這些武器，來幫助我們學習MySQL資料庫！希望甦哥可以幫助到大家！