媽媽再也不用擔(dān)心我把數(shù)據(jù)弄丟了

數(shù)據(jù)是現(xiàn)代大小廠的重要資產(chǎn)，保護(hù)和恢復(fù)數(shù)據(jù)成為了重要的技能，

最近幾年，常有一些無(wú)良程序員刪庫(kù)跑路的情況，不僅給所在企業(yè)更是給自己造成重大的損失。

另外，即使不是故意的情況下，也會(huì)因?yàn)槭韬鲈斐蓴?shù)據(jù)誤操作，是一件及麻煩又頭疼的事情……

神器出場(chǎng)

最近的一個(gè)項(xiàng)目里，客戶數(shù)據(jù)因?yàn)榫S護(hù)不當(dāng)，導(dǎo)致數(shù)據(jù)丟失，為了挽回?cái)?shù)據(jù)，并建立一個(gè)跨網(wǎng)閘（內(nèi)部組網(wǎng)之間不通，無(wú)法使用 MySql 主從同步）的數(shù)據(jù)備份機(jī)制，發(fā)現(xiàn)了一個(gè)神器 binlog2sql^[1]。

研究了一番之后，不僅恢復(fù)了誤操作丟失的數(shù)據(jù)，還通過(guò) binlog2sql 將主服務(wù)器上的 binlog^[2] 轉(zhuǎn)化為 SQL 語(yǔ)句，存入文件，實(shí)現(xiàn)了數(shù)據(jù)同步！

安裝

binlog2sql 使用 Python 開發(fā)，所以需要 Python 環(huán)境，可參考 Python 環(huán)境搭建

將 binlog2sql 用 git 克隆的本地，GitHub 上的地址是: https://github.com/danfengcao/binlog2sql.git

git clone https://github.com/danfengcao/binlog2sql.git

通過(guò) binlog2sql 目標(biāo)下的 requirements.txt 安裝依賴包

提示：推薦在 Python 虛擬環(huán)境中安裝，創(chuàng)建虛擬環(huán)境可參考 Python 虛擬環(huán)境 看這一篇就夠了

pip install -r requirements.txt

一切順利的話，很快就可完成安裝。

命令行進(jìn)入 binlog2sql 代碼目錄下測(cè)試一下

> python binlog2sql.py

usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD ...]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS]
                     [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--save-as SAVE_AS] [--stop-never] [--help] [-d [DATABASES ...]] [-t [TABLES ...]] [--only-dml]
                     [--sql-type [SQL_TYPE ...]] [-K] [-B] [--back-interval BACK_INTERVAL]

Parse MySQL binlog to SQL you want

...<省略>...

由于沒加任何參數(shù)，所以打印出使用說(shuō)明，那說(shuō)明安裝正常了。

簡(jiǎn)介

binlog2sql 是通過(guò)分析 MySql 數(shù)據(jù)庫(kù)的 binlog 文件，從中解析出需要執(zhí)行的 sql 語(yǔ)句的。

那么使用時(shí)需要提供一些必要的參數(shù)，其中重要的有數(shù)據(jù)庫(kù)服務(wù)器鏈接信息，需要分析的 binlog 文件名等，

還可以指定解析的起始和結(jié)束位置，以及開始和結(jié)束時(shí)間。

身手不凡

是騾子是馬拉出來(lái)溜溜。

恢復(fù)被刪數(shù)據(jù)

假如庫(kù)表 tb_user 中的數(shù)據(jù)如下：

+----+--------+---------------------+
| id | name   | createtime          |
+----+--------+---------------------+
|  1 | 張三   | 2021-01-10 00:04:33 |
|  2 | 李四   | 2021-01-10 00:04:48 |
|  3 | 王五   | 2021-04-23 20:25:00 |
|  4 | 趙六   | 2021-06-04 11:21:23 |
+----+--------+---------------------+

這時(shí)不小心執(zhí)行了一個(gè)刪操作，將數(shù)據(jù)誤刪了

delete from tb_user

如何恢復(fù)呢？

我們看一下數(shù)據(jù)庫(kù)的日志情況

show master status;

會(huì)看到類似這樣的結(jié)果

+------------------+-----------+
| File             | Position  |
+------------------+-----------+
| mysql-bin.000002 |     13136 |
+------------------+-----------+

注意：只有 MySql 數(shù)據(jù)庫(kù)打開了日志記錄功能，才能查詢到，打開日志功能請(qǐng)參考 binlog日志開啟和使用^[3]

可以看出，目前日志記錄在文件 mysql-bin.000002 中，當(dāng)前最新的記錄位置是 12546 行

假如當(dāng)時(shí)誤操作的時(shí)間是上午 11點(diǎn)半左右（可能著急吃飯，沒注意），那么預(yù)估一個(gè)時(shí)間范圍，比如 11點(diǎn)25 到 11點(diǎn)35，看看一下當(dāng)時(shí)的操作：

python binlog2sql -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t tb_user --start-file='mysql-bin.000002' --start-datetime='2021-06-04 11:25:00' --stop-datetime='2021-06-04 11:35:00'

輸出為：

INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-06-04 11:21:23', 4, '李四'); #start 12317 end 12487 time 2021-06-04 11:21:23
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-01-10 00:04:33' AND `id`=1 AND `name`='張三' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-01-10 00:04:48' AND `id`=2 AND `name`='李四' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-04-23 20:25:00' AND `id`=3 AND `name`='王五' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32
DELETE FROM `test`.`tb_user` WHERE `createtime`='2021-06-04 11:21:23' AND `id`=4 AND `name`='趙六' LIMIT 1; #start 12728 end 12829 time 2021-06-04 11:27:32

可以看出，第二行開始到第五行為刪除語(yǔ)句，查看語(yǔ)句最后的起始和結(jié)束位置 start 12728 end 12829

即 binlog 中，刪除執(zhí)行的位置在 12728-12829 之間，于是鎖定精確位置，生成回滾語(yǔ)句：

python binlog2sql -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t tb_user --start-file='mysql-bin.000002' --start-position=12728 --stop-position=12829 -B

注意參數(shù) -B，意思是生成回滾 SQL，即生成的是撤銷之前操作的語(yǔ)句

輸出為：

INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-06-04 11:21:23', 4, '趙六'); #start 12728 end 12829 time 2016-12-13 20:28:05
INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-04-23 20:25:00', 3, '王五'); #start 12728 end 12829 time 2016-12-13 20:28:05
INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-01-10 00:04:48', 2, '李四'); #start 12728 end 12829 time 2016-12-13 20:28:05
INSERT INTO `test`.`tb_user`(`createtime`, `id`, `name`) VALUES ('2021-01-10 00:04:33', 1, '張三'); #start 12728 end 12829 time 2016-12-13 20:28:05

從輸出的語(yǔ)句來(lái)看，順序是刪除的倒序，而且已經(jīng)將原來(lái)的 delete 語(yǔ)句改為了 insert 語(yǔ)句，也就是原來(lái)操作的逆操作

如果確認(rèn)語(yǔ)句沒問題，執(zhí)行生成的語(yǔ)句就可以了

是不是既方便又高效呢？

解析 SQL

binlog2sql 功能強(qiáng)大，使用起來(lái)也很方便，看看其他功能吧。

作為一個(gè)命令行工具，功能都體現(xiàn)在參數(shù)里，可分為 解析模式、解析目標(biāo)、解析范圍三部分。

解析模式

binlog2sql 支持兩個(gè)解析模式，默認(rèn)的是單次解析，即運(yùn)行一次解析一次，

還可以支持持續(xù)解析，即不間斷地從目標(biāo)數(shù)據(jù)庫(kù)地 binlog 中解析出 sql 來(lái)，持續(xù)解析通過(guò)參數(shù) --never-stop 開啟，

開啟之后，線程不會(huì)退出，一直處于運(yùn)行狀態(tài)，會(huì)自動(dòng)判斷 binlog 的變化，對(duì)變化部分增量式解析。

這種模式可以用于數(shù)據(jù)庫(kù)同步，不過(guò)生產(chǎn)上使用前，最好考慮各種異常情況，比如重啟，網(wǎng)絡(luò)中斷等情況。

參數(shù) -K 或 --no-primany-key 表示的去除 INSERT 語(yǔ)句中的主鍵，這個(gè)在數(shù)據(jù)匯總的場(chǎng)景下很方便，可以避免多個(gè)數(shù)據(jù)源中主鍵沖突的問題。

參數(shù) -B 或 --flashback，表示回滾模式，在上面的例子中展示過(guò)，即會(huì)解析成逆操作的 sql 語(yǔ)句。

在回滾模式下，每生成一千條 SQL 語(yǔ)句會(huì)加一個(gè) SLEEP 語(yǔ)句，是為以免數(shù)據(jù)執(zhí)行時(shí)產(chǎn)生擁堵，默認(rèn)為 1 秒，可以通過(guò) --back-interval 參數(shù)來(lái)設(shè)置，

例如 --back-interval 2 表示暫停 2 秒。

解析目標(biāo)

MySql 設(shè)置 binlog 時(shí)可以指定記錄哪個(gè)庫(kù)，以及哪些表，即目標(biāo)。

那么用 binlog2sql 也可以指定解析目標(biāo)。

參數(shù) -d 或 --databases 用于指定數(shù)據(jù)庫(kù)，如果多個(gè)庫(kù)，用空格分隔，例如 -d db1 db2；

參數(shù) -t 或 --tables 用于指定庫(kù)表，多個(gè)庫(kù)表用空格分隔，例如 -t tb1 tb2。

如果指定解析目標(biāo)不僅效率更高，而且分析和執(zhí)行解析的結(jié)果也更方便。

解析范圍

范圍包括 binlog 文件范圍，時(shí)間范圍 以及 行范圍，例如前面例子中用到了 時(shí)間范圍 和 行范圍。

文件范圍 用 --start-file 和 --stop-file 參數(shù)來(lái)指定，只需要提供 binlog 文件名即可，不需要寫全路徑，這是因?yàn)椋?code style="font-size: 14px;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;color: rgb(30, 107, 184);background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;">binlog2sql 會(huì)自動(dòng)根據(jù)目標(biāo)服務(wù)器配置讀取 binlog 文件；

時(shí)間范圍 用  --start-datetime 和 --stop-datetime 參數(shù)來(lái)指定，時(shí)間格式為 %Y-%m-%d %H:%M:%S；

行范圍 用 --start-position 和 --stop-position 參數(shù)來(lái)指定，也可以簡(jiǎn)寫為 --start-pos 和 --end-pos。

深入了解

binlog2sql 不僅是一個(gè)實(shí)用的工具，而且也是個(gè)研究和學(xué)習(xí)的好例子。

只有不到 500 行代碼，很容易閱讀；

閱讀源碼，不僅能深入了解其實(shí)現(xiàn)原理，而且還可以學(xué)習(xí)到很多好用法。

實(shí)現(xiàn)原理

binlog2sql 的原理是，利用 pymysql 從目標(biāo)服務(wù)器上獲取 binlog 信息，然后鎖定范圍，使用 pymysqlreplication 解析 binlog 文件，最后，得到需要解析出的 sql 語(yǔ)句。

在這基礎(chǔ)上，做了一些功能性擴(kuò)展，比如解析范圍，解析模式等，相對(duì)來(lái)說(shuō)比較簡(jiǎn)單，很容易看懂。

命令行參數(shù)

編程時(shí)處理命令行參數(shù)是機(jī)械而繁瑣的，特別是有不同性質(zhì)的性質(zhì)和別名的參數(shù)

binlog2sql 中利用了 argparse 模塊^[4]，

argparse 模塊可以讓人輕松編寫用戶友好的命令行接口。程序定義它需要的參數(shù)，argparse 可以從 sys.argv 解析出提供的命令行參數(shù)。而且 argparse 模塊還會(huì)自動(dòng)生成幫助和使用手冊(cè)，并在用戶給程序傳入無(wú)效參數(shù)時(shí)報(bào)出錯(cuò)誤信息。

很容易就能編程高大上的命令行程序接口，再也不用為很 low 的程序接口發(fā)愁了。

文件處理上下文（context）

binlog2sql 在回滾模式（即提供了參數(shù) -B）中，使用了一個(gè)臨時(shí)文件記錄解析出來(lái)的 SQL 語(yǔ)句，并且在完成之后刪除。

一般來(lái)說(shuō)，在完成后主動(dòng)刪除文件即可，不過(guò)如果能利用 with 塊的資源回收功能就更好了。

查看源碼，會(huì)看到一個(gè)創(chuàng)建文件寫法：

@contextmanager
def temp_open(filename, mode):
    f = open(filename, mode)
    try:
        yield f
    finally:
        f.close()
        os.remove(filename)

@contextmanager^[5] 指示器可以將一個(gè)生成器^[6]，作為一個(gè)上下文管理器^[7]，

那么：

with 聲明部分，會(huì)執(zhí)行前會(huì)執(zhí)行 yield 語(yǔ)句之前的部分

with 范圍內(nèi)，會(huì)執(zhí)行 yield 語(yǔ)句，即返回一個(gè)需要后續(xù)處理的對(duì)象，比如文件，后續(xù)處理是關(guān)閉

with 執(zhí)行完成前，會(huì)執(zhí)行 yield 語(yǔ)句之后的代碼

那么這段代碼的意義就是，當(dāng)文件使用完成后，關(guān)閉文件，并且刪除掉。

使用方式為：

with temp_open(tmp_file, "w") as f_tmp
    ...
    f_tmp.write(sql + '\n')
    ...

這樣無(wú)論如何只要 with 塊執(zhí)行完，文件就會(huì)被刪除，不用擔(dān)心忘記，是不是很優(yōu)雅？

除了這兩點(diǎn)，還有很多值得把玩的地方，有興趣的話可以讀讀源碼。

總結(jié)

無(wú)論是什么工具，都需要有一定的基礎(chǔ)和良好的習(xí)慣上才會(huì)發(fā)揮作用，比如得開啟 MySql 的 binlog 日志，并由記錄工作的習(xí)慣。

同時(shí)，任何工具方法都有它的特點(diǎn)，可以在了解功能的同時(shí)，研究一下其使用原理，是一個(gè)很好的技能提升機(jī)會(huì)。

很多人在抱怨，沒有應(yīng)用場(chǎng)景，沒有實(shí)際項(xiàng)目，其實(shí)研究這些工具，就會(huì)有事半功倍的效果。

比心

參考資料