利用binlog2sql快速閃回誤刪除數據 - 別拿豆包不當干糧
今兒抽出時間，給大家分享一篇利用binlog2sql閃回工具，來恢復誤刪除的數據。我們都知道binlog的作用是備份恢復和完成MySQL的主從復制功能。利用mysqlbinlog工具可以進行基于時間點或者位置偏移量的數據恢復工作，在生產環境中遇到誤刪除，改錯數據的情況，那是常有的事兒。我們都知道Oracle數據庫有閃回功能，而MySQL本身沒有自帶閃回，但我們可以使用binlog2sql來完成這項工作。
我們都知道binlog是以event作為單位，來記錄數據庫變更的數據信息，閃回就是可以幫助我們重現這些變化數據信息之前的操作。也就是說對于insert操作，會生成delete語句，反之delete操作，會生成insert語句。對于update操作，也會生成相反的update語句。這款工具只能使用在binlog格式為row模式下。
下面進行實戰演練：
binlog2sql工具的下載地址： https://github.com/danfengcao/binlog2sql
***步：環境準備安裝各種依賴的工具包列表
- 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
- INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('sy', 4, 'ddd'); #start 1214 end 1427 time 2017-11-30 14:27:46
- INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('gz', 3, 'ccc'); #start 1214 end 1427 time 2017-11-30 14:27:46
- INSERT INTO `zs`.`t`(`address`, `id`, `name`) VALUES ('sh', 2, 'bbb'); #start 1214 end 1427 time 2017-11-30 14:27:46
- INSERT INTO `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數據庫！今后老張會繼續分享新的干貨，供大家學習參考！
