MySQL 日誌管理、備份與恢復

无敌小鲁班發表於2024-06-26
MySql

目錄
  • 1.資料庫備份的分類
    • （1）資料庫備份的分類
    • （2）備份策略
    • （3）備份工具
  • 2.MySQL完全備份與恢復
    • （1）MySQL完全備份
    • （2）物理冷備
    • （3）mysqldump 邏輯熱備
    • （4）完全恢復
  • 3.MySQL增量備份與恢復
    • （1）增量備份
      • （1）增量備份特點
      • （2）MySQL沒有提供直接的增量備份方法
    • （2）增量恢復
      • （1）一般恢復
      • （2）斷點恢復
      • （3）斷點恢復的原則
  • 4.你們公司資料庫的備份是如何做的?

1.資料庫備份的分類

資料備份的重要性：備份的主要目的是災難恢復；在生產環境中，資料的安全性至關重要；任何資料的丟失都可能產生嚴重的後果；造成資料丟失的原因（程式錯誤、人為操作錯誤、運算錯誤、磁碟故障、災難(如火災、地震)和盜竊）

（1）資料庫備份的分類

（1）物理備份
直接對資料庫的物理檔案（資料檔案、日誌檔案等）進行備份

物理備份方法：
冷備份(離線備份):是在關閉資料庫的時候進行的
熱備份(聯機備份):資料庫處於執行狀態，依賴於資料庫的日誌檔案
溫備份:資料庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作

（2）邏輯備份
對資料庫物件（庫、表）的資料以SQL語句的形式匯出進行備份

（2）備份策略

完全備份:每次備份都備份完整的庫或表資料
差異備份:只備份上一次完全備份後的更新資料
增量備份:只備份上一次完全備份或增量備份後的更新資料

（3）備份工具

tar壓縮打包（完全備份，物理冷備）
mysqldump（完全備份，邏輯熱備）
mysqlhotcopy（完全備份，邏輯熱備，僅支援MyISAM和ARCHIVE引擎表）
二進位制日誌（增量備份）
PXB XtraBackup 、innobackupex（完全備份、增量備份，物理熱備）

注：Xtrabackup中主要包含兩個工具：xtrabackup：是用於熱備份innodb，xtradb表中資料的工具，不能備份其他型別的表，也不能備份資料表結構。
innobackupex：是將xtrabackup進行封裝的perl指令碼，提供了備份myisam表的能力。

2.MySQL完全備份與恢復

（1）MySQL完全備份

是對整個資料庫、資料庫結構和檔案結構的備份
儲存的是備份完成時刻的資料庫
是差異備份與增量備份的基礎
優點：備份與恢復操作簡單方便
缺點：資料存在大量的重複、佔用大量的備份空間、備份與恢復時間長

（2）物理冷備

先關閉資料庫，使用 tar 命令壓縮打包備份資料庫的資料目錄和檔案 mysql/data/




物理冷備做的資料遷移


（3）mysqldump 邏輯熱備

mysqldump -u root -p密碼 --databases 庫1 [庫2 ....] > XXX.sql     #備份一個或多個指定的庫及庫中所有的表

mysqldump -u root -p密碼 --all-databases > XXX.sql       #備份所有庫
mysqldump -u root -p密碼 庫名 > XXX.sql    #只備份指定庫中的所有表（不包含庫物件本身）

mysqldump -u root -p密碼 庫名 表1 [表2 ....] > XXX.sql     #只備份指定庫中的一個或多個指定的表（不包含庫物件本身）




（4）完全恢復

1）先登入資料庫，再執行 source sql檔案路徑  
（如果sql檔案裡只備份了表，需要先 use 切換庫再執行 source）

2）mysql -u root -p密碼 < sql檔案路徑           
cat sql檔案路徑 | mysql -u root -p密碼         #恢復庫

mysql -u root -p密碼 庫名 < sql檔案路徑      
cat sql檔案路徑 | mysql -u root -p密碼 庫名    #恢復表






3.MySQL增量備份與恢復

（1）增量備份

使用mysqldump進行完全備份存在的問題：備份資料中有重複資料；備份時間與恢復時間過長
是自上一次備份後增加/變化的檔案或者內容

（1）增量備份特點

沒有重複資料，備份量不大，時間短
恢復需要上次完全備份及完全備份之後所有的增量備份才.能恢復，而且要對所有增量備份進行逐個反推恢復

（2）MySQL沒有提供直接的增量備份方法

可透過MySQL提供的二進位制日誌間接實現增量備份
MySQL二進位制日誌對備份的意義：二進位制日誌儲存了所有更新或者可能更新資料庫的操作；二進位制日誌在啟動MySQL伺服器後開始記錄，並在檔案達到max_binlog_size所設定的大小或者接收到flush logs命令後重新建立新的日誌檔案；只需定時執行flush logs方法重新建立新的日誌，生成二進位制檔案序列，並及時把這些日誌儲存到安全的地方就完成了一個時間段的增量備份。

透過重新整理二進位制日誌實現增量備份
mysqladmin -u root -p密碼 flush-logs

檢視二進位制日誌內容
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二進位制檔案路徑

使用二進位制日誌增量恢復
mysqlbinlog --no-defaults 二進位制檔案路徑 | mysql -u root -p密碼

二進位制日誌(binlog)有3種不同的記錄格式：STATEMENT(基於SQL語句)、ROW(基於行)、MIXED(混合模式)





（2）增量恢復

（1）一般恢復

將所有備份的二進位制日誌內容全部恢復

（2）斷點恢復

（1）基於位置恢復
資料庫在某一時間點可能既有錯誤的操作也有正確的操作
可以基於精準的位置跳過錯誤的操作

mysqlbinlog --no-defaults --start-position='開始位置點' --stop-position='結束位置點'  二進位制檔案路徑 | mysql -u root -p密碼



（2）基於時間點恢復
跳過某個發生錯誤的時間點實現資料恢復

mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS'  二進位制檔案路徑 | mysql -u root -p密碼

（3）斷點恢復的原則

如果要恢復到某條sql語句之前的所有資料，就stop在這個語句的位置點或時間點之前
如果要恢復某條sql語句及其之後的所有資料，就從這個語句的位置點或時間點開始start

4.你們公司資料庫的備份是如何做的?

透過crontab 結合 資料庫備份指令碼 實現每週N 做一次全量/完全備份，其餘每天做增量備份
全量/完全備份 mysqldump(邏輯備份) ；xtrabackup(PXB 物理備份)
增量備份 重新整理二進位制日誌 xtrabackup(PXB)


相關文章