MySQL根據離線binlog快速「閃回」的詳情介紹-mysql教程-PHP中文網

MySQL根據離線binlog快速「閃回」的詳情介紹

黄舟

發布： 2017-03-18 14:37:12

原創

1643 人瀏覽過

昨天突然有個客戶說誤操作，自己刪除了大量數據，CTO直接將我拉到一個討論組裡，說要幫他們恢復數據。他們自己挖的坑，打算讓開發那邊根據業務日誌去恢復，被告知只記錄的刪除主鍵這樣的信息，物理刪除，無能為力。

上伺服器看了下記錄的日誌，發現好幾台上面都有被誤刪的記錄輸出。阿里RDS雖然可以複製一個恢復到刪除時間點前的實例，但這散落的幾萬個id找起來費力，還有就是幾個表之間關聯的資料也要恢復，覺得麻煩。

想到 MySQL 的閃回方案。以前看過好幾篇相關文章，甚至差點自己用python擼一個來解析binlog，反轉得到回滾sql，實在沒空，這下要急用了。趕緊找了下網路「現成的方案」。

正文開始

MySQL（含阿里RDS）快速閃回可以說是對資料庫誤操作的後悔藥，flashback功能可以將資料庫回到誤操作之前。但是即使oracle資料庫也只支援短時間內的閃回。

網路上現有開源的MySQL閃回實現，原理都是解析binlog，生成反向sql: (必須為row模式)

對於delete 操作，生成insert （DELETE_ROWS_EVENT）
對於update 操作，交換binlog裡面值的順序（UPDATE_ROWS_EVENT）
#對於insert 操作，反向產生delete （ WRITE_ROWS_EVENT）
對於多個event，要逆向產生sql

上面兩種實作方式，都是透過python-mysql-replication 套件，模擬出原庫的一個從庫，然後show binary logs 來取得binlog，發起同步binlog的請求，再解析EVENT。但阿里雲 RDS 的binlog在同步給從函式庫之後， 很快就被 purge 掉了 。如果要恢復昨天的 部分資料 ，兩個方案都是拿不到binlog的。也就是閃回的時間有限。

還有一些比較簡單的實現，就是解析 binlog 物理文件，實現回滾，如 binlog-rollback.pl ，試過，但是速度太慢。

為了不影響速度，又想使用比較成熟的閃回方案，我們可以這樣做：

借助一個自建的mysqld 實例，將已purge掉的binlog拷貝到該實例的目錄下
在自建實例裡，提前創建好需要恢復的表（結構），因為工具需要連接上來從information_schema.columns 取得元資料資訊
拷貝的時候，可以替換掉mysql實例自己的binlog檔名，保持連續
##可能要修改
mysql-bin.index，確保檔名還能被mysqld辨識到
重啟mysql實例，
show binary logs 看一下是否在清單裡面
接下來就可以使用上面任何一種工具，模擬從庫，指定一個binlog文件，開始時間，結束時間，得到回滾SQL
#再根據業務邏輯，篩選出需要的sql

總之就是藉助另外一個mysql，把binlog event傳輸過來。溫馨提示：

兩個實例間版本不要跨度太大
#注意檔案權限
如果原庫開啟了gtid，這個自建實例也要開啟gtid

範例：

python mysqlbinlog_back.py --host="localhost" --username="ecuser" --password="ecuser" --port=3306 \
--schema=dbname --tables="t_xx1,t_xx2,t_xx3" -S "mysql-bin.000019" -E "2017-03-02 13:00:00" -N "2017-03-02 14:09:00" -I -U

===log will also  write to .//mysqlbinlog_flashback.log===
parameter={&#39;start_binlog_file&#39;: &#39;mysql-bin.000019&#39;, &#39;stream&#39;: None, &#39;keep_data&#39;: True,
 &#39;file&#39;: {&#39;data_create&#39;: None, &#39;flashback&#39;: None, &#39;data&#39;: None}, &#39;add_schema_name&#39;: False, &#39;start_time&#39;: None, 
 &#39;keep_current_data&#39;: False, &#39;start_to_timestamp&#39;: 1488430800,
 &#39;mysql_setting&#39;: {&#39;passwd&#39;: &#39;ecuser&#39;, &#39;host&#39;: &#39;localhost&#39;, &#39;charset&#39;: &#39;utf8&#39;, &#39;port&#39;: 3306, &#39;user&#39;: &#39;ecuser&#39;},
 &#39;table_name&#39;: &#39;t_xx1,t_xx2,t_xx3&#39;, &#39;skip_delete&#39;: False, &#39;schema&#39;: &#39;dbname&#39;, &#39;stat&#39;: {&#39;flash_sql&#39;: {}},
 &#39;table_name_array&#39;: [&#39;t_xx1&#39;, &#39;t_xx2&#39;, &#39;t_xx3&#39;],
 &#39;one_binlog_file&#39;: False, &#39;output_file_path&#39;: &#39;./log&#39;, &#39;start_position&#39;: 4, &#39;skip_update&#39;: True,
 &#39;dump_event&#39;: False, &#39;end_to_timestamp&#39;: 1488434940, &#39;skip_insert&#39;: True, &#39;schema_array&#39;: [&#39;dbname&#39;]
}
scan 10000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:14
scan 20000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:29
...

登入後複製

提示：

binlog為ROW格式，dml影響的每一行都會記錄兩個event：Table_map和Row_log。而table_map裡面的table_id並不會影響它在哪個實例上應用，這個id可以認為是邏輯上，記錄表結構版本的機制－當它在table_definition_cache 沒有找到表定義時，id自增1，分配給要記錄到binlog的表。

mysqlbinlog_back.py 使用經驗：

#務必指定庫名、表明，開始的binlog檔名，起始時間，結束時間。可以加快scan的速度。
根據恢復的需要，選擇 -I, -U, -D，指定回滾哪些類型的操作。
如果只是恢復部分錶資料（非完全閃回），做不到關聯表的正確恢復。例如需要恢復delete數據，但無法恢復業務裡因為delete引起其它表
更新的數據，除非完全閃回。
不支援表格欄位是 enum 類型的，例如 t_xx3 的f_do_type欄位。可以把自建實例上的enum定義改成int。

以上是MySQL根據離線binlog快速「閃回」的詳情介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！