Mysql 之 完全備份+增量備份+備份恢複

文章目錄

• • 一、資料備份的重要性
  • 二、資料庫備份的分類
  • • 2.1、從實體與邏輯的角度
    • 2.2、從資料庫的備份政策角度
  • 三、完全備份
  • • 3.1、完全備份的優點
    • 3.2、完全備份的缺點
  • 四、mysqldump備份庫
  • • 4.1、備份單個庫
    • 4.2、備份多個庫
    • 4.3、備份所有庫
    • 4.4、mysqldump備份表
  • 五、資料庫的恢複
  • • 5.1、source指令恢複
    • 5.2、mysql指令恢複
  • 六、增量備份
  • • 6.1、增量備份的優點
    • 6.2、增量備份的缺點
    • 6.3、增量備份的方法
    • 6.4、增量備份的恢複

一、資料備份的重要性

• 在生産環境中，資料的安全性是至關重要的，任何資料的丢失都可能産生嚴重的後果
• 造成資料丢失的原因：
  • 程式錯誤
  • 人為錯誤
  • 計算機失敗
  • 磁盤失敗
  • 災難(如火災、地震)和偷竊

二、資料庫備份的分類

2.1、從實體與邏輯的角度

備份可分為：

• 實體備份：對資料庫作業系統的實體檔案(如資料檔案、日志檔案等)的備份。
  • 實體備份又可以分為脫機備份(冷備份)和聯機備份(熱備份)。
    • 冷備份:是在關閉資料庫的時候進行的。
    • 熱備份:資料庫處于運作狀态，這種備份方法依賴于資料庫的日志檔案。
• 邏輯備份：對資料庫邏輯元件(如表等資料庫對象)的備份。

2.2、從資料庫的備份政策角度

備份可分為：

• 完全備份：每次對資料進行完整的備份
• 差異備份：備份那些自從上次完全備份之後被修改過的檔案
• 增量備份：隻有那些在上次完全備份或者增量備份後被修改的檔案才會被備份

三、完全備份

• 完全備份是對整個資料庫的備份、資料庫結構和檔案結構的備份
• 完全備份儲存的是備份完成時刻的資料庫
• 完全備份是增量備份的基礎

3.1、完全備份的優點

• 備份與恢複操作簡單友善。

3.2、完全備份的缺點

• 資料存在大量的重複。
• 占用大量的備份空間。
• 備份與恢複時間長。

四、mysqldump備份庫

• MySQL資料庫的備份可以采用用多種方式
  • 直接打包資料庫檔案夾，如/usr/ocal/mysql/data
  • 使用專用備份工具mysqldump
• mysqldump指令
  • MySQL自帶的備份工具，相當友善對MySQl進行備份
  • 通過該指令工具可以将指定的庫、表或全部的庫導出為SQL腳本，在需要恢複時可進行資料恢複

4.1、備份單個庫

• mysqldump指令對單個庫進行完全備份

mysqldump -u 使用者名 -p[密碼] [選項] [資料庫名] > /備份路徑/備份檔案名
           

• 單庫備份例子

mysqldump -u root -pabc123 student > /opt/student.sql
           

4.2、備份多個庫

• mysqldump指令對多個庫進行完全備份

mysqldump -U使用者名-P [密碼] [選項] --databases 庫名1 [庫名2]... >
/備份路徑/備份檔案名
           

• 多庫備份例子

mysqldump -u root -pabc123 --databases student mysql > /opt/db_student_mysql.sql
           

4.3、備份所有庫

• 對所有庫進行完全備份

mysqldump -u 使用者名 -p[密碼] [選項] -all-atabases > /備份路徑/備份檔案名
           

• 所有庫備份例子

mysqldump -uroot -pabc123 --opt --all-databases > /opt/all.sql
           

4.4、mysqldump備份表

• 在實際生産環境中，存在對某個特定表的維護操作，此時 mysqldump 同樣發揮重大作用
• 使用mysqldump備份表的操作

mysqldump -u使用者名 -p[密碼] [選項]資料庫名 表名 > /備份路徑/備份檔案名
           

• 備份表的例子

mysqldump -uroot -pabc123 student user > /backup/mysql-user.sql
           

隻備份表的結構

mysqldump -u使用者名 -p[密碼] -d 資料庫名 表名 > /備份路徑/備份檔案名   
例：
mysqldump -uroot -pabc123 -d student stu01 > /opt/student_stu01.sql
           

五、資料庫的恢複

• 使用mysqldump指令導出的SQL備份腳本，在進行資料恢複時可使用以下方法導入
  • source指令
  • mysql指令

5.1、source指令恢複

• 使用source恢複資料庫的步驟
  • 登入到MySQL資料庫
  • 執行source 備份sql腳本的路徑

注意：當備份檔案中隻包含表的備份，而不包括建立庫的語句時，必須指定庫名，且目标庫必須存在。

模拟環境：把資料庫student的stu01表删除

#登入資料庫
use student;
drop table stu01;
           

source恢複

格式：source 表備份的絕對路徑;
mysql> source /opt/student_stu01.sql;   #注意：恢複的是絕對路徑
           

5.2、mysql指令恢複

格式：
mysql -u使用者名 -p[密碼] 庫名 < 表備份腳本的路徑
例：
mysql -uroot -pabc123 student < /opt/student_stu01.sql 
           

六、增量備份

• 使用mysqldump進行完全備份的存在的問題
  • 備份資料中有重複資料
  • 備份時間與恢複時間長
• 增量備份就是備份自上一次備份之後增加或變化的檔案或者内容

6.1、增量備份的優點

• 沒有重複的備份資料
• 備份的資料量不大
• 備份所需的時間也很短

6.2、增量備份的缺點

• 備份恢複比較麻煩，需要用上次完全備份及完全備份之後所有的增量備份才能恢複。
• 要對所有增量備份進行逐個反推恢複。

6.3、增量備份的方法

• MySQL沒有提供直接的增量備份方法
• 可以通過MySQL提供的二進制日志 (binary logs) 間接實作增量備份
• MySQL二進制日志對備份的意義
  • 二進制日志儲存了所有更新或者可能更新資料庫的操作
  • 二進制日志在啟動MySQL伺服器後開始記錄，并在檔案達到max_binlog_size所設定的大小或者接收到flush logs指令後重新建立新的日志檔案
  • 隻需定時執行flush logs方法重新建立新的日志，生成二進制檔案序列，并及時把這些舊的日志儲存到安全的地方就完成了一個時間段的增量備份

6.4、增量備份的恢複

1、增量備份依賴二進制日志檔案，開啟MySQL的二進制日志功能

vim /etc/my.cnf
在配置檔案裡插入一行代碼 log-bin=mysql-bin
systemctl restart mysqld #重新開機服務
           

2、重新開機服務後，建立新的資料庫内容，在student庫中建立info表，完全備份資料庫student。

在 /usr/local/mysql/data/目錄下生成一個日志檔案 mysql-bin.000001

3、重新整理所有日志後生成一個新日志檔案即 增量備份日志檔案 mysql-bin.000002

4、修改一些資料。

5、檢視日志檔案，記錄錯誤操作的位置和時間點

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt
           

6、模拟故障：登入資料庫，删除 student 庫中的資料表 info 。

7、恢複：先恢複完全備份（ 用 source、mysql指令），再用以下的三種方法之一恢複增量備份部分。

• 一般恢複

  

mysqlbinlog [--no-defaults] 增量備份檔案 | mysql -u 使用者名 -p
           

• 基于位置恢複

  就是将某個起始時間的二進制日志導入資料庫中，進而跳過某個發生錯誤的時間點實作資料的恢複

  

1、恢複資料到指定位置。

格式：mysqlbinlog --stop-position='操作id' 二進制日志 | mysql -u使用者名 -p密碼
mysqlbinlog --no-defaults --stop-position='1131' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
           

2、從指定的位置開始恢複資料。

格式：mysqlbinlog --start-position='操作id' 二進制日志 | mysql -u使用者名 -p密碼
mysqlbinlog --no-defaults --start-position='1236' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
           

• 基于時間點恢複

  使用基于時間點的恢複，可能會出現在一個時間點裡既同時存在正确的操作又存在錯誤的操作，是以我們需要一種更為精确的恢複方式。

**通過檢視日志檔案，得到恢複時間點**

200108 11:12:55 --stop datetime    #錯誤操作時間點
200108 11:13:27 --start-datetime   #下一條正确操作時間點
           

1、從日志開頭截止到某個時間點的恢複。

格式：mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小時:分鐘:秒’ 二進制日志 | mysql -u使用者名-p密碼
mysqlbinlog --no-defaults --stop-datetime='2020-01-08 11:12:55' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p
           

2、從某個時間點到日志結尾的恢複。

格式：mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒’ 二進制日志 | mysql -u使用者名-p密碼
mysqlbinlog --no-defaults --start-datetime='2020-01-08 11:13:27' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p