---

前言

隨著辦公自動化後電子商務的飛速發展，企業對資訊系統的依賴性越來越高，資料庫作為資訊系統的核心擔當著相當重要的角色。資料庫備份，是在資料丟失的情況下，能及時恢復重要資料，防止資料丟失的一種重要手段。一個合理的資料庫備份方案，應該能夠在資料丟失時，及時的恢復資料，同時需要考慮技術實現難度和有效利用資源。

---

一 資料備份介紹

1.1 資料備份的重要性

1.在生產環境中，資料的安全性至關重要
2.任何資料的丟失都可能產生嚴重的後果
3.造成資料丟失的原因
   程式錯誤
   人為操作錯誤
   運算錯誤
   磁碟故障
   災難（如火災，地震）和盜竊

1.2 資料庫備份的分類

1.2.1 從物理與邏輯的角度，備份可分為

1.物理備份：對資料庫作業系統的物理檔案（如資料檔案，日誌檔案等）的備份
   物理備份方法：
       冷備份(離線備份)：是在關閉資料庫的時候進行的
       熱備份（聯機備份）：資料庫處於執行狀態，依賴於資料庫的日誌檔案
       溫備份：資料庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作
       
2.邏輯備份：對資料庫邏輯元件（如：表等資料庫物件）的備份

1.2.2 從資料庫的備份策略角度，備份可分為

完全備份：每次對資料庫進行完整的備份

差異備份；備份自從上次完全備份之後被修改過的檔案

增量備份：只有在上次完全備份或者增量備份後被修改的檔案才會被備份（無重複備份）

1.3 常見的備份方法

1.物理冷備

  備份時資料庫處於關閉狀態，直接打包資料庫檔案
  備份速度快，恢復時也是最簡單的

2.專用備份工具mydump或mysqlhotcopy

  mysqldump常用的邏輯備份工具
  mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表

3.啟用二進位制日誌進行增量備份

  進行增量備份，需要重新整理二進位制日誌
  日誌生成條件：1.服務重啟，2.重新整理日誌，3.超過儲存容量

4.第三方工具備份

  免費的MySQL熱備份軟體Percona XtraBackup

二 MySQL完全備份與恢復

2.1 MySQL完全備份

1.是對整個資料庫，資料庫結構和檔案結構的備份

2.儲存的是備份完成時刻的資料庫

3.是差異備份與增量備份的基礎

4.優點
  備份與恢復操作簡單方便
  
5.缺點
  資料存在大量的重複
  佔用大量的備份空間
  備份與恢復時間長

2.2 資料庫完全備份分類

1.物理冷備份與恢復

  關閉MySQL資料庫
  使用tar命令直接打包資料庫資料夾
  直接替換現有MySQL目錄即可

2.mysqldump備份與恢復

  MySQL自帶的備份工具，可方便實現對MySQL的備份
  可以將指定的庫，表匯出為SQL指令碼（字尾為.sql）
  使用命令mysql匯入備份的資料

2.2.1 MySQL物理冷備份及恢復

2.2.1.1 流程解析

先備份資料庫中的資料打包，模擬資料丟失，損壞，利用備份打包恢復資料

物理冷備份
先關閉資料庫，之後打包備份
恢復資料庫
恢復資料庫，採用將備份資料mv成線上庫資料夾的方式

2.2.1.1 配置流程

1.首先建立資料庫資料

[root@server1 ~]# mysql -uroot -pabc123   登入資料庫
mysql> create database lbf;  建立資料庫lbf
mysql> use lbf;  使用資料庫
mysql> create table a(id int(10)); 建立資料庫並設定資料
mysql> desc a; 描述表a的結構
mysql> exit 退出

2.進行資料備份打包

[root@server1 ~]# systemctl stop mysqld    關閉資料庫
[root@server1 ~]# tar zcvf /opt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/  原始檔來源路徑
備份打包所有資料庫，當天日期
[root@server1 ~]# cd /opt/ 
[root@server1 opt]# ls -lh   檢視檔案

3.模擬資料丟失，損壞

[root@server1 opt]# systemctl start mysqld  開啟資料庫
[root@server1 opt]# mysql -uroot -pabc123 登入資料庫
mysql> show databases;  檢視資料庫資訊
mysql> drop database lbf;  刪除資料庫lbf
mysql> show databases;   檢視資料庫
mysql> exit

4.恢復資料

[root@server1 opt]# systemctl stop mysqld  關閉資料庫
[root@server1 opt]# cd /opt/ 
[root@server1 opt]# tar zxvf mysql_all-2020-12-24.tar.gz  解壓縮
[root@server1 opt]# ls -lh
[root@server1 opt]# mkdir mysql_old       建立目錄
[root@server1 opt]# mv /usr/local/mysql/data/ mysql_old/  
剪下在備份完成之前產生的資料
[root@server1 opt]# mv usr/local/mysql/data/ /usr/local/mysql/
[root@server1 opt]# cd /usr/local/mysql/
[root@server1 mysql]# ls -lh
[root@server1 data]# cd
[root@server1 ~]# systemctl start mysqld  開啟資料庫
[root@server1 ~]# mysql -uroot -pabc123   登入資料庫
mysql> show databases;       檢視資料庫資訊 ，資料恢復
mysql> use lbf;     使用資料庫
mysql> show tables; 檢視錶
mysql> desc a;      模式表結構
mysql> exit         退出

2.2.2 mysqldump（熱備份）備份資料庫

2.2.2.1 mysqldump命令對單個庫進行完全備份

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

用法示例：

mysqldump -u root -p auth > /backup/auth.sql 
mysqldump -u root -p mysql > /bakcup/mysql.sql

2.2.2.2 mysqldump命令對多個庫進行完全備份

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

用法示例：

mysqldump -u root -p --databases auth mysql > /backup/databases-
auth-mysql.sql 

2.2.2.3 對所有庫進行完全備份

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

用法示例：

mysqldump -u root -p  --all-databases > /backup/all-data.sql

2.2.2.4 應用示例：

1.建立資料庫資料

mysql> show databases;  檢視資料庫資訊
mysql> use lbf;  使用資料庫
mysql> insert into a values(1); 插入資料記錄
mysql> insert into a values(2);
mysql> select * from a;   查詢所有欄位
mysql> show databases;  

mysql> use tree;使用資料庫
mysql> show tables; 檢視錶資訊
mysql> select * from fruits; 查詢所有欄位

2.進行資料備份

[root@server1 ~]# mkdir /backup  建立目錄
[root@server1 ~]# mysqldump -uroot -pabc123 tree > /backup/tree-$(date +%F).sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.（不安全）
[root@server1 ~]# mysqldump -uroot -p --all-databases > /backup/all-databases-$(date +%F).sql
[root@server1 ~]# cd /backup  檢視備份檔案
[root@server1 backup]# ls -lh
[root@server1 backup]# vi tree-2020-12-24.sql 
[root@server1 backup]# vi all-databases-2020-12-24.sql

2.3 恢復資料庫

1.使用mysqldump匯出的指令碼，可使用匯入的方法

source命令
mysql命令

2.使用source恢復資料庫的步驟

登入到MySQL資料庫
執行source備份sql指令碼的路徑

3.source恢復的示例

MySQL [(none)]> source /backup/all-data.sql

2.3.1 使用source命令恢復資料

1.模擬資料庫出現問題

[root@server1 backup]# mysql -uroot -pabc123 登入資料庫
mysql> show databases;  檢視資料庫資訊
mysql> drop database tree;  刪除資料庫tree
mysql> show databases; 

mysql> create database tree;    先建立同名資料庫
mysql> use tree;       後使用資料庫
mysql> source /backup/tree-2020-12-24.sql   恢復資料庫
mysql> show databases; 檢視資料庫資訊
mysql> use tree;       使用資料庫
mysql> show tables;    檢視錶
mysql> select * from fruits;    程式使用欄位

2.3.3 使用mysql命令恢復資料

mysql -u 使用者名稱 -p [密碼] < 庫備份檔案指令碼的路徑

mysql命令恢復的示例

mysql -u root -p < /backup/all-data.sql

1.模擬資料庫出現問題

mysql> show databases;     檢視資料庫資訊
mysql> drop database tree; 刪除資料庫tree
mysql> show databases;     檢視資料庫資訊
mysql> create database tree;建立同名空的資料庫
mysql> exit  退出

2.恢復資料庫

[root@server1 ~]# mysql -uroot -pabc123 < /backup/tree-2020-12-24.sql 
[root@server1 ~]# mysql -uroot -pabc123 < /backup/all-databases-2020-12-24.sql
[root@server1 ~]# mysql -uroot -pabc123  登入資料庫
mysql> show databases;
mysql> use tree;
mysql> show tables;
mysql> select * from fruits;

2.4 恢復表的操作

1.恢復表時同樣可以使用source或者mysql命令

2.source恢復表的操作與恢復庫的操作相同

3.當備份檔案中只包含表的備份，而不包括建立庫的語句時，必須指定庫名，且目標庫必須存在

   mysql -u 使用者名稱 -p [密碼] < 表備份指令碼的路徑
   mysql -u root -p mysql <  /backup/mysql-user.sql

4.在生產環境中，可以使用Shell指令碼自動實現定時備份

2.4.1 應用示例：

mysql> use tree;    使用資料庫
mysql> create table a(id int(1)); 建立新的表a設定資料
mysql> insert into a values(1),(2);  插入資料記錄
mysql> select * from a;  查詢所有欄位
mysql> show tables; 檢視錶資訊
mysql> exit 

2.建立備份

[root@server1 ~]# mysqldump -uroot -pabc123 tree a > /opt/tree-a.sql  
[root@server1 ~]# mysql -uroot -pabc123 登入資料庫
mysql> use tree;使用資料庫
mysql> show tables;  檢視錶資訊
mysql> drop table a;  刪除表a
mysql> show tables; 

3.恢復資料表

mysql> source /opt/tree-a.sql   
mysql> select * from a;  查詢所有欄位
mysql> show tables; 檢視錶資訊

注:備份庫裡的某張表時，應該針對這張壞的表刪除，使用原來資料庫，重新source備份的sq1可以實現，不需要刪除整個庫，只需刪除相應的表就行了，進行備份。

三 MySQL增量備份與恢復

3.1 MySQL增量備份

1.使用mysqldump進行完全備份存在的問題

  備份資料中有著重複資料
  備份時間與恢復時間過長

2.是自上一次備份後增加/變化的檔案或者內容

3.特點

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

4.MySQL沒有提供直接的增量備份方法

5.可通過MySQL提供的二進位制日誌間接實現增量備份

6.MySQL二進位制日誌對備份的意義

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

3.2 MySQL資料庫增量恢復

1.一般恢復

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

2.基於位置恢復

資料庫在某一時間點可能既有錯誤的操作也有正確的操作
可以基於精準的位置跳過錯誤的操作
發生錯誤節點之前的一個節點，上一次正確操作的位置點停止

3.基於時間點恢復

跳過某個發生錯誤的時間點實現資料恢復
在錯誤時間點停止，在下一個正確時間點開始

3.2.1 基於時間點恢復

3.2.1.1 流程解析：

還原時間點的步驟

刪除原先壞掉的那張表
還原完全備份的那個資料庫
停止在錯誤的時間點
開始在正確的時間點

3.2.1.2 配置流程

1.開啟二進位制日誌檔案

[root@server1 ~]# vi /etc/my.cnf   編輯配置檔案
[root@server1 ~]# systemctl restart mysqld  服務重啟，日誌生成
[root@server1 ~]# cd /usr/local/mysql/data/  檢視日誌
[root@server1 data]# ls -lh
新增
log_bin=/usr/local/mysql/data/mysql_bin
選項                          日誌名稱

2.檢視二進位制日誌內容

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000001 
--no-defaults：解決utf-8報錯
--base64-output=decode-rows：解決亂碼問題
-v：輸出檔案指向
無新操作，無新增的資訊

3.建立資料庫資料

[root@server1 ~]# mysql -uroot -pabc123  登入資料庫
mysql> show databases;    檢視資料庫資訊
mysql> use tree;          使用資料庫
mysql> create table bb(name varchar(128), score int(10));  建立設定表bb資料
mysql> insert into bb values('wang',60);   插入資料記錄
mysql> insert into bb values('zhu',80);
mysql> select * from bb;  查詢所有欄位
mysql> exit 退出

4.完全備份

[root@server1 ~]# mysqldump -uroot -pabc123 tree  > /backup/tree-bb.sql
[root@server1 ~]# cd /backup/ 檢視備份
[root@server1 backup]# ls -lh
[root@server1 backup]#  mysqladmin -uroot -p flush-logs  重新整理日誌
[root@server1 backup]# cd /usr/local/mysql/data/
[root@server1 data]# ls -lh

5.再次操作，增量備份
資料配置

[root@server1 ~]# mysql -uroot -pabc123 登入資料庫
mysql> use tree; 使用資料庫
mysql> insert into bb values('da',75);  插入資料記錄 正確操作
mysql> delete from bb where name='zhu'; 刪除資料記錄，誤操作
mysql> insert into bb values('xiao',3); 插入資料記錄 正確操作
mysql> select * from bb;  查詢所有欄位
mysql> exit

6.重新整理並檢視新二進位制日誌檔案

[root@server1 ~]# mysqladmin -uroot -p flush-logs
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# ls -lh

7.檢視日誌

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002

查詢到錯誤時間點停止
201225 1:29:48

在下一個正確時間點開始
201225 1:29:56

8.模擬資料損壞

[root@server1 ~]# mysql -uroot -pabc123 登入資料庫
mysql> show databases;      檢視資料庫資訊
mysql> drop database tree;  刪除資料庫tree
mysql> show databases;  
mysql> create database tree; 建立同名為空的資料庫
mysql> show databases;    檢視資料庫
mysql> use tree;          使用資料庫
mysql> source /backup/tree-bb.sql  還原完全備份的那個資料庫
mysql> select * from bb;  查詢所有欄位
mysql> exit 

9.恢復資料

停止在錯誤的時間點201225  1:29:48
[root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-12-25   1:29:48' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p

開始在正確的時間點201225  1:29:56
[root@server1 ~]# mysqlbinlog --no-defaults --start-datetime='2020-12-25    1:29:56 ' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p

[root@server1 ~]# mysql -uroot -pabc123 登入資料庫
mysql> use tree;          使用資料庫
mysql> select * from bb;  查詢所有欄位

發現恢復的資料自動跳過發生錯誤的時間點恢復了，

3.2.2 基於位置點恢復

3.2.2.1 流程解析：

位置點恢復
刪除原先壞掉的那張表
任意刪除兩個資料欄位
利用日誌，備份恢復
查詢該二進位制日誌內容
上一次正確操作的位置點停止
下一次正確操作的位置點開始

3.2.2.2 配置流程

1.設定資料庫資料

[root@server1 ~]# mysql -uroot -pabc123 登入資料庫
mysql> use tree;使用資料庫
mysql>  select * from bb; 查詢所有欄位
mysql>  delete from bb where name='da';   刪除資料欄位
mysql>  delete from bb where name='xiao';  刪除資料欄位
[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003  檢視日誌

上一次正確操作的位置點停止
3475

下一次正確操作的位置點開始
3829

2.恢復資料

[root@server1 ~]# mysqlbinlog --no-defaults --stop-position='3475' /usr/local/mysql/data/mysql_bin.000003 | mysql -uroot -p
[root@server1 ~]#  mysqlbinlog --no-defaults --start-position='3829' /usr/local/mysql/data/mysql_bin.000003 | mysql -uroot -p
[root@server1 ~]# mysql -uroot -pabc123登入資料庫
mysql> use tree
mysql>  select * from bb; 查詢所有欄位

---

總結

通過以上知識的瞭解，學習，靈活掌握可以在在資料丟失的情況下，能及時恢復重要資料，防止資料丟失。