使用binlog2sql做數據恢復的簡單示例

知識 10-17

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

前提條件

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

[mysqld]
server_id = 1log_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。

使用binlog2sql做數據恢復的簡單示例

數據恢復

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

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

show master status;

使用binlog2sql做數據恢復的簡單示例

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

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

使用binlog2sql做數據恢復的簡單示例

從上圖可以看出二進位文件記錄了我們對每行數據修改的痕迹，其中@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做數據恢復的簡單示例

使用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做數據恢復的簡單示例