[[404372]]

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

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

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

另外，即使不是故意的情況下，也會因為疏忽造成數(shù)據(jù)誤操作，是一件及麻煩又頭疼的事情……

神器出場

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

研究了一番之后，不僅恢復(fù)了誤操作丟失的數(shù)據(jù)，還通過 binlog2sql 將主服務(wù)器上的 binlog[2] 轉(zhuǎn)化為 SQL 語句，存入文件，實現(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 

通過 binlog2sql 目標下的 requirements.txt 安裝依賴包

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

pip install -r requirements.txt 

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

命令行進入 binlog2sql 代碼目錄下測試一下

> 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ù)，所以打印出使用說明，那說明安裝正常了。

簡介

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

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

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

身手不凡

是騾子是馬拉出來溜溜。

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

假如庫表 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 | 
+----+--------+---------------------+ 

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

delete from tb_user 

如何恢復(fù)呢?

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

show master status; 

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

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

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

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

假如當時誤操作的時間是上午 11點半左右(可能著急吃飯，沒注意)，那么預(yù)估一個時間范圍，比如 11點25 到 11點35，看看一下當時的操作：

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 

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

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

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，即生成的是撤銷之前操作的語句

輸出為：

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 

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

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

是不是既方便又高效呢?

解析 SQL

binlog2sql 功能強大，使用起來也很方便，看看其他功能吧。

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

解析模式

binlog2sql 支持兩個解析模式，默認的是單次解析，即運行一次解析一次，

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

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

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

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

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

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

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

解析目標

MySql 設(shè)置 binlog 時可以指定記錄哪個庫，以及哪些表，即目標。

那么用 binlog2sql 也可以指定解析目標。

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

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

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

解析范圍

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

文件范圍 用 --start-file 和 --stop-file 參數(shù)來指定，只需要提供 binlog 文件名即可，不需要寫全路徑，這是因為，binlog2sql 會自動根據(jù)目標服務(wù)器配置讀取 binlog 文件;

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

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

深入了解

binlog2sql 不僅是一個實用的工具，而且也是個研究和學習的好例子。

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

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

實現(xiàn)原理

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

在這基礎(chǔ)上，做了一些功能性擴展，比如解析范圍，解析模式等，相對來說比較簡單，很容易看懂。

命令行參數(shù)

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

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

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

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

文件處理上下文(context)

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

一般來說，在完成后主動刪除文件即可，不過如果能利用 with 塊的資源回收功能就更好了。

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

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

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

那么：

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

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

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

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

使用方式為：

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

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

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

總結(jié)

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

同時，任何工具方法都有它的特點，可以在了解功能的同時，研究一下其使用原理，是一個很好的技能提升機會。

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

比心

參考資料

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

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

[3]binlog日志開啟和使用: 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/