蜻蜓點水binlog

語言: CN / TW / HK

簡述

Binlog是記錄所有資料庫表結構變更以及表資料修改的二進位制日誌,不會記錄SELECT和SHOW這類操作。

Binlog日誌是以事件形式記錄,還包含語句所執行的消耗時間。

開啟Binlog日誌有以下兩個最重要的使用場景。

主從複製:在主庫中開啟Binlog功能,這樣主庫就可以把Binlog傳遞給從庫,從庫拿到Binlog後實現資料恢復達到主從資料一致性。
資料恢復:通過mysqlbinlog工具來恢復資料。

狀態

初始狀態

通過簡單的MySQL的資料,就可以看到初始狀態,預設mysql5.7是關閉的。如下圖所示。

開啟狀態

開啟的時候,需要在配置檔案中,設定log-bin變數的值。預設不改變路徑,只填寫一個名稱-mysql-bin。對此值賦值完,變開啟了bin-log日誌。查閱資料,開啟此類日誌,會佔用伺服器百分之一的開銷。僅供參考,未求證真偽。

預設路徑:/Library/Application\ Support/appsolute/MAMP\ PRO/db/mysql57/

相關引數

在binlog日誌中,有一個很重要的引數binlog_format,這個引數規定了日誌檔案的儲存方式,預設為row。其次快取的大小binlog_cache_size,還有有效期:expire_logs_days。這些均使用預設值,不涉及生產環境的參考值。

查閱日誌

小白初次嘗試,查閱binlog日誌的幾種方式,前兩種為錯誤操作,均為亂碼不可讀,以第三種為正確操作方式,使用專用的mysqlbinlog工具進行讀取該日誌檔案。

  • • 方式一【錯誤】:

  • • 方式二【錯誤】:

方式三【正確】:

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000007

儲存模式【binlog_format】

statement模式

表現形式,記錄sql語句,但如果是帶有時間引數的語句,比如now(),這樣的語句在恢復的時候,就是時間錯亂。優點是記錄資料較少,節省IO,提高效能。

row模式

表現形式,非一般的sql語句,記錄資料繁瑣,可讀性差,不會出現statement模式下的時間錯亂的情況,資料量比較龐大。

mixed模式

混合模式是前兩種的結合體,系統智慧判斷使用哪種形式記錄。三種方式的對比圖:

資料恢復實操

說明

在執行前使用flush logs,產生一個新的日誌檔案,便於我們實驗,方便結果的恢復和閱讀,此時產生了的是04日誌檔案。

實操

在本地資料庫blog中建立一張表xc_test,裡面新增一條資料。name為123的,製造資料丟失的場景,人工刪除這條資料,此時檢視日誌檔案如下:

以行號起始點恢復資料

尋找兩個標記,一個begin,一個end。中間為刪除操作產生的日誌記錄。在這兩個標記之間,尋找delete最上面的at數是343,最下面的at數為390。日誌檔案簡單分析到這裡。也可以在MySQL中執行:"show binlog events in 'mysql-bin.000004'"查閱更方便,閱讀更好。

再看日誌恢復的命令:mysqlbinlog  --database=要操作的資料庫 binlog的名稱 --start-position=開始的pos --stop-position=結束的pos| mysql -u登陸名 -p登陸密碼 -v 要操作的資料庫 分析到這裡,如果要回復這條資料,我們需要找到這個條數的插入過程,也就是insert。看下面這個圖:

也就是插入的開始位置是468,結束位置是688。按照上面的格式,進行拼接資料如下: mysqlbinlog --database=blog mysql-bin.000004 --start-position=486 --stop-position=688 | mysql -u root -proot -v blog

以時間起始點恢復資料

mysqlbinlog --database=blog mysql-bin.000004 --start-datetime="2022-08-02 10:45:05" --stop-datetime="2022-08-02 14:10:10" | mysql -u root -proot -v blog

bug[Ownership is released on COMMIT or ROLLBACK.]

原因: 設定的起始pos和結束pos點並不是一條完整的mysql事務開啟事務結束語

總結:

mysql下命令總結:

show variables like 'binlog%'; # 檢視資料庫變數
flush logs; # 重新生成一個日誌檔案
show binlog events in 'mysql-bin.000004' # 查詢指定日誌檔案
show master logs;  # 顯示所有日誌檔案
show master status; #當前的日誌檔案

Tide安全團隊正式成立於2019年1月,是新潮資訊旗下以網際網路攻防技術研究為目標的安全團隊,團隊致力於分享高質量原創文章、開源安全工具、交流安全技術,研究方向覆蓋網路攻防、系統安全、Web安全、移動終端、安全開發、物聯網/工控安全/AI安全等多個領域。

團隊作為“省級等保關鍵技術實驗室”先後與哈工大、齊魯銀行、聊城大學、交通學院等多個高校名企建立聯合技術實驗室,近三年來在網路安全技術方面開展研發專案60餘項,獲得各類自主智慧財產權30餘項,省市級科技專案立項20餘項,研究成果應用於產品核心技術研究、國家重點科技專案攻關、專業安全服務等。對安全感興趣的小夥伴可以加入或關注我們。