[[404372]]

本文轉(zhuǎn)載自微信公眾號(hào)「Python技術(shù)」，作者派森醬。轉(zhuǎn)載本文請(qǐng)聯(lián)系Python技術(shù)公眾號(hào)。

數(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 開(kāi)發(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 
 
...<省略>... 

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

簡(jiǎn)介

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

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

還可以指定解析的起始和結(jié)束位置，以及開(kāi)始和結(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ù)打開(kāi)了日志記錄功能，才能查詢到，打開(kāi)日志功能請(qǐng)參考 binlog日志開(kāi)啟和使用[3]

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

假如當(dāng)時(shí)誤操作的時(shí)間是上午 11點(diǎn)半左右(可能著急吃飯，沒(méi)注意)，那么預(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 

可以看出，第二行開(kāi)始到第五行為刪除語(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ǔ)句沒(méi)問(wèn)題，執(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 開(kāi)啟，

開(kāi)啟之后，線程不會(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ù)源中主鍵沖突的問(wèn)題。

參數(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)椋琤inlog2sql 會(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ā)揮作用，比如得開(kāi)啟 MySql 的 binlog 日志，并由記錄工作的習(xí)慣。

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

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

比心

參考資料

[1]binlog2sql: https://github.com/danfengcao/binlog2sql

[2]binlog: https://laijianfeng.org/2019/03/MySQL-Binlog-%E4%BB%8B%E7%BB%8D/

[3]binlog日志開(kāi)啟和使用: https://juejin.cn/post/6854573218485944333

[4]argparse 模塊: https://docs.python.org/zh-cn/3/library/argparse.html

[5]@contextmanager: https://www.liaoxuefeng.com/wiki/1016959663602400/1115615597164000

[6]生成器: https://www.programiz.com/python-programming/generator

[7]上下文管理器: https://www.geeksforgeeks.org/context-manager-in-python/