記一次mysql生產誤刪表搶救操作

海yo發表於2024-07-25
MySql

背景

資料庫的操作必須要採取一系列的措施來規範流程和管理。然而，不同客戶群體管理水平不一，刪庫跑路也並不是茶餘飯後的笑話，它真真實實的，突如其來的就發生在你身邊。
接專案經理前線打來電話，某醫院突發生產故障，透過業務日誌得知，資料庫缺表，需要緊急恢復資料，恢復生產。

思路

確認環境和條件

0.已確認生產受影響，關閉應用防止報錯業務日誌堆積。
1.好端端的生產庫怎麼可能缺一張至關重要的表，除了人為刪除找不到其他合理的解釋。
2.恢復資料，肯定是先找最近的備份記錄及檔案。
3.確認備份策略（mysqldump備份，一天一全備，凌晨1點執行，保留7天備份資料）
4.確認是否有開啟binlog（正常生產上線，必須是開著的）

梳理恢復流程

大致分兩個步驟：
1.透過全備，建表並恢復表資料到今天凌晨1點。
2.讀取binlog日誌，恢復從凌晨1點到最後生產故障時間點的資料。（保證這一天下來的增量資料）

具體恢復流程開始

1.透過binlog定位誤刪操作的時間，命令等資訊。

mysqlbinlog -d 資料庫名 -v --skip-gtids binlog檔名  |grep 'DROP TABLE' -C 10


透過mysqlbinlog命令，可得：
找到誤刪操作的時間點：20240724 17:42:52
對應的binlog檔名為：bin.000399
最後故障前的資料庫恢復點pos：444959948
操作程序id:thread_id=159424
對方可能是跑了指令碼，DROP TABLE IF EXISTS，可能是想在測試庫上跑指令碼建表，結果執行到了生產，把生產庫表刪了。

2.確認全備時的點位



凌晨一點備份，對應的binlog檔名為：bin.000396（可以從圖片看到396的binlog是從23號的20:00多一直寫到24號01:07）
後續撈全備後一天的增量資料，就從bin.000396這個日誌開始撈最全，不怕資料重複，頂多插不進去，最怕資料丟失。

3.抽取全備裡面的建表語句+表資料，恢復到生產庫。（資料最新到凌晨1點）

抽取建表語句

cat {全備sql檔案} |grep 'CREATE TABLE `XXXXXXXX`' -A 50 |more

抽取表資料

cat {全備sql檔案} |grep 'INSERT INTO `XXXXXXXX`' >> data_insert.sql

建表

透過上面抽取的建表語句，要麼直接執行，要麼放到create_table.sql檔案裡，use {db}；source /path/to/create_table.sql

鎖表

lock tables XXXXXXXX write;

匯入全量表資料

use {db}；source  /path/to/data_insert.sql

或者
/path/to/bin/mysql -h {ip} -P {埠} -u root -p{密碼} -S /path/to/tmp/mysql.sock {資料庫} < /path/to/data_insert.sql
亦可

透過業務資料確認最新表資料的時間戳

確認最新資料為故障前20240724 01:00:00之前的最新資料

4.利用my2sql，讀取binlog，撈取從凌晨1點的資料追到最後生產故障時間點的最新資料。（補充這一天下來的增量資料）

./my2sql -host {伺服器ip} -port {埠} -user root -password 'XXXXX' -mode file -work-type 2sql -databases ehc  -tables {要恢復的表名} -local-binlog-file ./bin.000396 -start-file bin.000396  -stop-file bin.000399 -start-datetime "2024-07-24 00:40:00" -stop-datetime "2024-07-24 18:00:00"  -output-dir ./tmpdir

前提工作

1.my2sql工具傳進去（我是直接放到binlog的目錄下）
2.確保my2sql可以執行
3.確保開始撈增量的開始和結束的時間點和binlog檔案（前面已經確認過了）
3.建立tmpdir資料夾

執行成功後，在tmpdir目錄下生成forward.396.sql/forward.397.sql（中間有多少binlog，就會生成多少個sql）

透過head -n 進行sql確認

匯入增量資料

/path/to/bin/mysql -h {ip} -P {埠} -u root -p{密碼} -S /path/to/tmp/mysql.sock  {資料庫}  <  /path/to/forward.396.sql
/path/to/bin/mysql -h {ip} -P {埠} -u root -p{密碼} -S /path/to/tmp/mysql.sock  {資料庫}  <  /path/to/forward.397.sql

透過業務資料確認最新表資料的時間戳

確認最新資料為故障前20240724 17:42:52之前的最新資料

解鎖表

UNLOCK TABLES;

資料恢復結束，確認業務是否恢復。

業務資料再次確認，啟動應用，觀察應用是否正常，業務撥測，觀察資料庫資料是否正常。

故障溯源

1.從操作程序id:thread_id=159424出發，SHOW PROCESSLIST或者透過查詢information_schema資料庫中的PROCESSLIST表來獲取類似的資訊。
2.結合審計日誌。

相關文章