前幾天有個同事碰到了一個MySQL資料恢復的問題，他執行了一條update語句，結果忘記了加where條件，結果等反應過來已經晚了。我簡單確認了下，是否存在備份，沒有，是否開啟了日誌，沒有。所以這個恢復無從談起。

當然後來他也花了些功夫逐條資料修復，事情過去了，資料恢復的重要性，人為操作的重要性就不言而喻了，但是有些時間工作職責還是需要下移。我覺得還是需要好好總結下資料恢復的問題。我會從以下幾個方面來談。

⊙ 手工恢復資料的簡單示例

⊙使用開源工具恢復資料的配置

⊙ 使用開源工具恢復資料的實踐

⊙小結

首先手工恢復資料，其實有一些思路，一種就是透過全備+binlog的時間、偏移量來恢復。另外一類是透過解析binlog來恢復，前提條件是日誌格式為row。我們來簡單模擬解析binlog的恢復方式。

手工恢復資料的簡單示例

先看一看binlog的情況，可以看到當前的binlog是序號為15的日誌檔案。

create table test (id int not null primary key,name varchar(20),memo varchar(50)) ENGINE=InnoDB auto_increment=100 default charset=utf8;插入幾條資料

> insert into test values(1,'name1','memo1'),(2,'name2','memo2'),(3,'name3','memo3'),(4,'name4','memo4'),(5,'name5','memo5');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0檢視一下資料的基本情況：

> delete from test where id in (1,3);
Query OK, 2 rows affected (0.01 sec)

> update test set memo='new' where id in(2,4);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

> insert into test values(6,'name6','memo6');
Query OK, 1 row affected (0.00 sec)做完上面三個DML操作之後，我們標記一下時間。

> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2017-02-06 04:15:44 |
+---------------------+下面我們來解讀一下binlog，根據時間戳得到一個基本可讀的日誌，裡面還有這些資料變更，但是語句和執行的還是有一些出入，我們直接複製一份binlog到/tmp目錄下解析。

mysqlbinlog --no-defaults -v --start-datetime="2017-02-06 04:14:33" --stop-datetime="2017-02-06 04:15:44" /tmp/mysql-bin.000016 --result-file=/tmp/result.sql生成的檔案result.sql內容如下，可以看到這些操作在binlog中都有了很詳細的標記，資料的情況基本都是一目瞭然，update的部分變化前變化後的資料都一覽無餘。其實DML中難度較大的就是update,而insert,delete就是一個加減法。

delete操作對應binlog日誌中的SQL

### DELETE FROM `test`.`test`
### WHERE
###   @1=1
###   @2='name1'
###   @3='memo1'
### DELETE FROM `test`.`test`
### WHERE
###   @1=3
###   @2='name3'
###   @3='memo3'
# at 998969666update操作對應binlog日誌中的SQL

### UPDATE `test`.`test`
### WHERE
###   @1=2
###   @2='name2'
###   @3='memo2'
### SET
###   @1=2
###   @2='name2'
###   @3='new'
### UPDATE `test`.`test`
### WHERE
###   @1=4
###   @2='name4'
###   @3='memo4'
### SET
###   @1=4
###   @2='name4'
###   @3='new'
# at 998971422

insert操作對應binlog日誌中的SQL

### INSERT INTO `test`.`test`
### SET
###   @1=6
###   @2='name6'
###   @3='memo6'
# at 998973859值得一提的是-v（--verbose）選項會將行事件重構成被註釋掉的偽SQL語句，如果想看到更詳細的資訊可以使用-vv選項，這樣可以包含一些資料型別和元資訊的註釋內容。

比如：

-vv的結果：

### DELETE FROM `test`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @3='memo1' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */回到資料恢復的問題，如果需要手工恢復就需要做幾件事情，一個就是根據欄位標示拼接出可執行的SQL語句，然後按照逆向的順序執行即可。

使用開源工具恢復資料的配置

從上面的步驟可以看到，如果手工修復其實還是可以實現的，不過手工的操作不少，這個時候能夠簡化你的工作就是好工具，我試用了下binglog2sql這個開源工具，也是大眾點評的DBA團隊推出的，總體還不錯。

這個工具是Python開發，當然有一些依賴的庫和環境需要配置。如果你的伺服器是聯網環境，那就省事多了。

兩個步驟即可完成。

git clone && cd binlog2sql
pip install -r requirements.txt --會安裝額外需要的外掛而如果不是，那麼就有一些額外的工作需要你去做。比如我這個環境連pip都沒有。可以先在其他伺服器上下載到對應的指令碼，部署即可。

# wget "" --no-check-certificate件有些環境在pip部署的時候可能有下面的錯誤。

# python setup.py install
Traceback (most recent call last):
File "setup.py", line 6, in <module>
from setuptools import setup, find_packages
ImportError: No module named setuptools看來還和一個setuptools的庫有關，我們繼續安裝。

# wget --no-check-certificate然後使用 python ez_setup.py install 即可編譯成功setuptools

再次嘗試python setup.py install即可完成pip的安裝。

對於外掛PyMySQL可以使用如下的方式來安裝：

git clone 對於外掛mysql replication可以使用如下的方式：

git clone 就這樣前期的工作就做好了。

使用開源工具恢復資料的實踐

完成了環境的配置，工具使用起來和mysqlbinlog還是有一些相似之處，好的地方就是多了一些輔助功能。

我們建立一個使用者admin來解析。

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY 'admin';比如我們使用如下的命令來解析binlog得到指定時間戳範圍內的SQL情況，在此我們限定資料為test

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -padmin -dtest --start-file='mysql-bin.000016' --start-datetime='2017-02-06 04:14:33' --stop-datetime='2017-02-06 04:15:44' > /tmp/tmp.log得到的檔案內容如下：

#cat /tmp/tmp.log
DELETE FROM `test`.`test` WHERE `memo`='memo1' AND `id`=1 AND `name`='name1' LIMIT 1; #start 11127 end 11321 time 2017-02-06 04:15:23
DELETE FROM `test`.`test` WHERE `memo`='memo3' AND `id`=3 AND `name`='name3' LIMIT 1; #start 11127 end 11321 time 2017-02-06 04:15:23
UPDATE `test`.`test` SET `memo`='new', `id`=2, `name`='name2' WHERE `memo`='memo2' AND `id`=2 AND `name`='name2' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
UPDATE `test`.`test` SET `memo`='new', `id`=4, `name`='name4' WHERE `memo`='memo4' AND `id`=4 AND `name`='name4' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES ('memo6', 6, 'name6'); #start 12062 end 12239 time 2017-02-06 04:15:37其實看起來還是很省事了。

如果希望得到閃回的語句，有一個flashback的選項，其實就是在原來的基礎上進行了解析和順序調整。

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -padmin -dtest --flashback --start-file='mysql-bin.000016' --start-datetime='2017-02-06 04:14:33' --stop-datetime='2017-02-06 04:15:44' > /tmp/tmp.log得到的內容如下：

#cat /tmp/tmp.log
DELETE FROM `test`.`test` WHERE `memo`='memo6' AND `id`=6 AND `name`='name6' LIMIT 1; #start 12062 end 12239 time 2017-02-06 04:15:37
UPDATE `test`.`test` SET `memo`='memo4', `id`=4, `name`='name4' WHERE `memo`='new' AND `id`=4 AND `name`='name4' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
UPDATE `test`.`test` SET `memo`='memo2', `id`=2, `name`='name2' WHERE `memo`='new' AND `id`=2 AND `name`='name2' LIMIT 1; #start 11400 end 11625 time 2017-02-06 04:15:29
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES ('memo3', 3, 'name3'); #start 11127 end 11321 time 2017-02-06 04:15:23
INSERT INTO `test`.`test`(`memo`, `id`, `name`) VALUES ('memo1', 1, 'name1'); #start 11127 end 11321 time 2017-02-06 04:15:23執行了如上的語句之後，再次檢視資料，資料就恢復了正常。

小結

對於DML的閃回其實還是有一些技巧可以參考，對於DDL的閃回相對來說就麻煩的多了。我們後續也會跟進這方面的工作。但是DML的閃回場景相對要多一些，我們尤其需要注意。

MySQL誤運算元據恢復的簡單實踐（r11筆記第67天）

相關文章