MySQL根據離線binlog快速“閃回”

NO IMAGE
1 Star2 Stars3 Stars4 Stars5 Stars 給文章打分!
Loading...

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

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

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

正文開始


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

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

  1. 對於 delete 操作,生成insert (DELETE_ROWS_EVENT)

  2. 對於 update 操作,交換binlog裡面值的順序 (UPDATE_ROWS_EVENT)

  3. 對於 insert 操作,反向生成delete (WRITE_ROWS_EVENT)

  4. 對於多個event,要逆向生成sql

開源實現:

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

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

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

  1. 藉助一個自建的 mysqld 例項,將已purge掉的binlog拷貝到該例項的目錄下

  2. 在自建例項裡,提前建立好需要恢復的表(結構),因為工具需要連線上來從 information_schema.columns 獲取後設資料資訊

  3. 拷貝的時候,可以替換掉mysql例項自己的binlog檔名,保持連續

  4. 可能要修改 mysql-bin.index,確保檔名還能被mysqld識別到

  5. 重啟mysql例項,show binary logs 看一下是否在列表裡面

  6. 接下來就可以使用上面任何一種工具,模擬從庫,指定一個binlog檔案,開始時間,結束時間,得到回滾SQL

  7. 再根據業務邏輯,篩選出需要的sql

<!–more–>
總之就是藉助另外一個mysql,把binlog event傳輸過來。溫馨提示:

  1. 兩個例項間版本不要跨度太大

  2. 注意檔案許可權

  3. 如果原庫開啟了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={'start_binlog_file': 'mysql-bin.000019', 'stream': None, 'keep_data': True,
'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': False, 'start_time': None, 'keep_current_data': False, 'start_to_timestamp': 1488430800,
'mysql_setting': {'passwd': 'ecuser', 'host': 'localhost', 'charset': 'utf8', 'port': 3306, 'user': 'ecuser'},
'table_name': 't_xx1,t_xx2,t_xx3', 'skip_delete': False, 'schema': 'dbname', 'stat': {'flash_sql': {}},
'table_name_array': ['t_xx1', 't_xx2', 't_xx3'],
'one_binlog_file': False, 'output_file_path': './log', 'start_position': 4, 'skip_update': True,
'dump_event': False, 'end_to_timestamp': 1488434940, 'skip_insert': True, 'schema_array': ['dbname']
}
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。

參考

  1. http://dinglin.iteye.com/blog…

  2. http://www.penglixun.com/tech…

  3. http://www.cnblogs.com/yuyue2…


本文連結地址:http://seanlook.com/2017/03/0…


相關文章

資料庫 最新文章