記一次資料庫刪表事件

陳大剩發表於2020-07-02
資料庫事件

前言

目前我司正處於一種混亂的開發環境中；
對於資料庫欄位增修都是開發人員直上伺服器資料庫修改（我多次強調）；
我未入職前，是直接透過SFTP連線伺服器，對專案進行開發和修改（話說造成程式碼覆蓋都不知道是誰幹的，多次建議開發使用Git）；
入職後我寫一個開發規範文件：軟體開發手冊（歡迎各位大佬提出意見）
雖然對開發規範有所改變，但還是因為不可抗力因素無法執行，也是造成我最近想跳槽的原因；

原因

某天晚上，開發經理突然打電話：大剩，我不小心刪表了，能幫我恢復嗎（線上環境）？
我：好的，我看下，我開啟了二進位制日誌，應該恢復資料沒有問題；

開始操作

1.輸出檔案

將日誌檔案輸出到mysql_5.log（方便做其他操作）

#此處可能有多個日誌檔案，檢視日誌日期，可依個輸出到不同檔案中;
mysqlbinlog mysql-bin.000005  >> /home/mysql_5.log

2.查詢刪除的語句

#查詢帶有刪除語句的行數
grep -n 'DROP' /home/mysql_5.log
#output：878441:DROP TABLE `wtzx`

#擴大一點區間 檢視刪除語句在多少行 下文at1970819是刪除行語句的行號
sed -n '878430,878500p' /home/mysql_5.log
# output：
# at 1970819 
#200629 19:47:28 server id 1  end_log_pos 1970950 CRC32 0xc6b08770     Query    thread_id=4276    exec_time=0    error_code=0
# use `htrans_demo`/*!*/;
# SET TIMESTAMP=1593431248/*!*/;
# SET @@session.pseudo_thread_id=4276/*!*/;
# DROP TABLE `wtzx`

3.進行恢復

mysqlbinlog  mysql-bin.000005  --stop-position=1970819 | mysql -u root -p

天真我以為這樣就可以恢復 ；
其實是對Mysql二進位制日誌不瞭解；
Mysql二進位制日誌有點類似於Redis的AOF；
開啟二進位制日誌後記錄操作日誌，沒有記錄之前的資料則無法恢復（如創表語句等..）；

結合全量備份進行恢復

辛虧我往伺服器上，設定每天凌晨一點的定時備份，對重要的庫進行全量備份（資料量少，可以使用全量；資料量多的可以考慮全量+增量結合備份）；

1.將備份指令碼恢復

# 恢復備份
 cat db_test_db1.sql |mysql -uroot -p

2.恢復誤刪資料或者表（結合上面步驟）

# 恢復誤刪的資料
 mysqlbinlog  mysql-bin.000005  --stop-position=1970819 | mysql -u root -p

至此也就大功告成~~

開啟定時（全量\增量）備份+ Mysql二進位制日誌 防止資料誤操作方法

1.編寫Shell指令碼

#!/bin/sh

# 查詢三十天前的備份的檔案進行刪除
find /home/mysql-backups -mtime +30 -name "*.*" -exec rm -Rf {} \;

# 資料庫配置資訊
DB_USER="root"
DB_PASS="123"
DB_HOST="127.0.0.1"

# 需要備份資料庫陣列
DB_NAME=("test_db1","test_db2")

# 其他配置
BIN_DIR="/usr/bin/"      # Mysql bin
BCK_DIR="/home/mysql-backups"  #備份檔案目錄
DATE=`date +%F`

# 建立檔案
mkdir -p $BCK_DIR/$DATE

# 執行程式碼
for var in ${DB_NAME[@]};
do
  $BIN_DIR/mysqldump --opt --single-transaction --master-data=2 -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/$DATE/db_$var.sql
done
# TODO

我目前是做的全量備份，因為專案小。可以考慮全量+增量結合備份；
可加上郵件傳送備份的檔案到自己的郵箱，也可用上傳第三方Oss；

2.設定定時任務

# crontab -e
# 設定每天凌晨一點備份
0 1 * * * /home/sh/dump.sh

總結

利用 定時備份+開啟Mysql二進位制日誌 能更好的保證誤刪恢復（建議加上傳送郵件，或者上傳OSS）。這個是小公司做法，當然還有更多更好的方法；

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章