媽媽再也不用擔心我把數據弄丟了

作者：派森醬 2021-06-08 07:48:26

數據庫 MySQL

數據是現代大小廠的重要資產，保護和恢復數據成為了重要的技能，最近幾年，常有一些無良程序員刪庫跑路的情況，不僅給所在企業更是給自己造成重大的損失。

[[404372]]

本文轉載自微信公眾號「Python技術」，作者派森醬。轉載本文請聯系Python技術公眾號。

數據是現代大小廠的重要資產，保護和恢復數據成為了重要的技能，

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

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

神器出場

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

研究了一番之后，不僅恢復了誤操作丟失的數據，還通過 binlog2sql 將主服務器上的 binlog[2] 轉化為 SQL 語句，存入文件，實現了數據同步!

安裝

binlog2sql 使用 Python 開發，所以需要 Python 環境，可參考 Python 環境搭建

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

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

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

提示：推薦在 Python 虛擬環境中安裝，創建虛擬環境可參考 Python 虛擬環境看這一篇就夠了

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 
 
...<省略>...

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

簡介

binlog2sql 是通過分析 MySql 數據庫的 binlog 文件，從中解析出需要執行的 sql 語句的。

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

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

身手不凡

是騾子是馬拉出來溜溜。

恢復被刪數據

假如庫表 tb_user 中的數據如下：

+----+--------+---------------------+ 
| 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 | 
+----+--------+---------------------+

這時不小心執行了一個刪操作，將數據誤刪了

delete from tb_user

如何恢復呢?

我們看一下數據庫的日志情況

show master status;

會看到類似這樣的結果

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

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

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

假如當時誤操作的時間是上午 11點半左右(可能著急吃飯，沒注意)，那么預估一個時間范圍，比如 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

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

即 binlog 中，刪除執行的位置在 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

注意參數 -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

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

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

是不是既方便又高效呢?

解析 SQL

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

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

解析模式

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

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

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

這種模式可以用于數據庫同步，不過生產上使用前，最好考慮各種異常情況，比如重啟，網絡中斷等情況。

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

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

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

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

解析目標

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

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

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

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

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

解析范圍

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

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

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

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

深入了解

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

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

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

實現原理

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

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

命令行參數

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

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

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

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

文件處理上下文(context)

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

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

查看源碼，會看到一個創建文件寫法：

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

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

那么：

with 聲明部分，會執行前會執行 yield 語句之前的部分

with 范圍內，會執行 yield 語句，即返回一個需要后續處理的對象，比如文件，后續處理是關閉

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

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

使用方式為：

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

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

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

總結

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

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

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

比心

參考資料

[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/

責任編輯：武曉燕來源： Python技術

數據 Python 開發