使用binlog2sql做資料恢復的簡單示例

    有時我們會遇到操作人員誤刪或者誤更新資料的情況，這時我們迫切希望把原來的資料還原回來，今天我們介紹一個簡單的工具來方便的實現此功能。

前提條件

在實現資料恢復之前，需要我們的MySQL滿足以下配置條件：

[mysqld]
server_id = 1
log_bin = bin
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

以上配置都是MySQL的常規配置，不存在特殊配置的情況，所以你其實不用太擔心。

構造實驗資料

接下來我們建一個簡單的user表，並插入示例資料。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'liping', 'ADFASDFASDF', '2017-10-17 10:57:42');
INSERT INTO `user` VALUES ('2', 'xiaoming', 'SDDSFGSDFGSDG', '2017-10-17 10:58:06');
INSERT INTO `user` VALUES ('3', 'xiaohong', 'QEWFGFGDSFGSDG', '2017-10-17 10:58:29');

執行誤操作

接下來如果操作人員執行了一個誤操作，試圖修改liping的密碼為123456,但忘記了加where條件，即執行了SQL：

update user set `password`='123456';

導致所有的使用者密碼都被設定為了123456。

資料恢復

接下來我們將嘗試恢復原來的資料，首先因為我們開啟了二進位制日誌，所以其實我們的每次操作都被記錄到了二進位制日誌當中，我們可以使用二進位制檢視命令，查詢到我們剛才所做的操作痕跡。

首先定位到當前MySQL寫到了哪個二進位制檔案中，進入到MySQL執行命令：

show master status;

可以看到當前的二進位制檔案寫到了bin.000002。於是我們用二進位制檔案檢視命令，找出剛才操作的痕跡。（請注意二進位制檔案的路徑，修改為你的二進位制檔案路徑）

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /mdata/mysql_data/bin.000002 | grep -B 15 '123456'

從上圖可以看出二進位制檔案記錄了我們對每行資料修改的痕跡，其中@1 @2 @3 @4表示我們user表中的4個欄位，看到這些記錄你應該可以想到，如果我們把這些記錄逆向執行，這資料就可以恢復。所以這裡提供了一個思路，就是可以通過文字操作，把update set前後的字串調換，儲存成SQL，即可實現資料還原，但真正實現起來還是挺麻煩的。這裡我們介紹binlog2sql工具，你不用那麼麻煩的去實現此功能。

binlog2sql工具

binlog2sql是大眾點評開源的一款用於解析binlog的工具，詳見：https://github.com/danfengcao/binlog2sql

使用binlog2sql將二進位制檔案轉換為SQL：

python binlog2sql.py -h10.89.1.143 -P3306 -udev -p'123456' -dtest -t user --start-file='bin.000002'

使用binlog2sql將二進位制檔案解析為了SQL格式，這個檔案當中包括我們之前做的建表SQL以及插入示例資料的SQL，當然也包括我們誤操作的UPDATE語句。

這裡我們重點看黃色框包含的內容，它是我們update語句記錄在二進位制檔案當中的起止位置，我們可以看到三條UPDATE語句都記錄到：#start 2616 end 3028，所以我們待會恢復時，只需要恢復這個位置的資料就可以了。

使用binlog2sql生成回滾SQL

python binlog2sql.py --flashback -h10.89.1.143 -P3306 -udev -p'123456' -dtest -tuser --start-file='bin.000002' --start-position=2616 --stop-position=3028

可以看到binlog2sql生成了：#start 2616 end 3028位置的回滾SQL。我們把這個SQL複製下來到MySQL中執行，即可實現資料恢復。